SQL to Interactive Excel Dashboard Masterclass | Complete Business Analytics Project
SQL to Interactive Excel Dashboard: Live Project Build
Stop struggling with slow workbooks and crashing spreadsheets. In this continuous live marathon, we take real-world raw transactional data, query business logic directly using SQLite, and architect an executive dynamic sales dashboard inside Microsoft Excel from ground zero.
ЁЯФ┤ Set Reminder on YouTube Live
Click above to stream the step-by-step masterclass recording and follow along in real time.
From Raw Database to Boardroom Dashboard: Bridging SQLite and Microsoft Excel
Every working data professional eventually hits the "Excel wall." You open a spreadsheet with tens of thousands of rows, write nested VLOOKUP or XLOOKUP formulas, calculate running totals across dynamic ranges, and watch your workbook freeze. While Microsoft Excel remains the gold standard for executive reporting and dynamic UI prototyping, it was never designed to be an enterprise relational transactional engine.
The modern data analyst separates concerns into two distinct workflows:
- Data Layer (SQL): Clean, filter, categorize, and aggregate transactions directly within a lightweight relational database engine like SQLite.
- Presentation Layer (Excel): Import the lean aggregated result, link it to dynamic Pivot Tables, and present high-level KPIs and slicers to leadership.
Phase 1: Setting Up SQLite via the Command Prompt
Unlike client-server database systems such as PostgreSQL or Microsoft SQL Server, SQLite is a serverless, zero-configuration, single-file relational database engine. It runs directly on your machine through a simple executable.
1. Navigating to the Project Directory
Open your command line interface (Terminal or CMD) and navigate to the directory where you have extracted your SQLite tools:
C:\> CD /D "C:\DataProjects\SQLiteTools" C:\DataProjects\SQLiteTools> sqlite3.exe sales_training.db
2. Configuring Terminal Display Formatting
By default, SQLite outputs raw text separated by pipes (`|`), which is difficult to read. Activate clean tabular formatting before running queries:
-- Turn on column headers in output displays sqlite> .headers on -- Align results in structured columns sqlite> .mode column
3. Creating the Relational Sales Schema
Define a structured table named sales with defined data types to store individual order transactions:
CREATE TABLE sales (
order_id INTEGER PRIMARY KEY,
order_date TEXT,
customer TEXT,
region TEXT,
category TEXT,
product TEXT,
quantity INTEGER,
unit_price REAL
);
Verify your schema structure anytime using:
sqlite> .schema sales
Phase 2: Writing Business Queries to Solve Management Problems
Management teams rarely ask for "raw rows"; they ask specific business questions. Below are the core SQL queries implemented in our tutorial project.
Query 1: Retrieving Specific Columns
Management Question: Can we review who bought what, and in what quantities?
SELECT customer, product, quantity FROM sales;
Query 2: Applying Conditional Filters with WHERE
Management Question: Which orders came specifically from the West territory?
SELECT * FROM sales WHERE region = 'West';
Query 3: Compound Conditions with AND / OR
Management Question: Show all orders that were either delivered in the West or South territories, but only if they belong to the Electronics category.
SELECT * FROM sales WHERE (region = 'West' OR region = 'South') AND category = 'Electronics';
Query 4: Sorting & Calculated Revenue Columns
Management Question: What are the individual line-item revenues ordered from highest to lowest?
SELECT order_id,
customer,
product,
quantity,
unit_price,
(quantity * unit_price) AS line_revenue
FROM sales
ORDER BY line_revenue DESC;
Query 5: Business Aggregations (GROUP BY & HAVING)
Management Question: Which territories generated over ₹10,000 in total gross revenue?
Note on HAVING vs WHERE: The WHERE clause filters individual records before grouping. The HAVING clause filters aggregated summary groups after the GROUP BY calculation occurs.
SELECT region,
COUNT(order_id) AS total_orders,
SUM(quantity * unit_price) AS total_revenue
FROM sales
GROUP BY region
HAVING total_revenue > 10000
ORDER BY total_revenue DESC;
Query 6: Transaction Tiering via CASE Expressions
Management Question: Can we categorize every transaction as High, Medium, or Low value for sales incentives?
SELECT order_id,
customer,
(quantity * unit_price) AS revenue,
CASE
WHEN (quantity * unit_price) >= 5000 THEN 'Tier 1 - High Value'
WHEN (quantity * unit_price) >= 2000 THEN 'Tier 2 - Mid Value'
ELSE 'Tier 3 - Standard Value'
END AS revenue_tier
FROM sales;
Phase 3: Exporting SQLite Datasets to CSV
To transfer your database output cleanly into Excel without manual copy-pasting, redirect SQLite's output directly into a CSV file:
-- Set CSV mode and designate output filename sqlite> .headers on sqlite> .mode csv sqlite> .output sales_export.csv -- Run analytical query to export full dataset with calculated line revenue sqlite> SELECT order_id, order_date, customer, region, category, product, quantity, unit_price, (quantity * unit_price) AS revenue FROM sales; -- Terminate output redirection and exit SQLite sqlite> .output stdout sqlite> .quit
Phase 4: Building the Executive Excel Dashboard
Now that the dataset is cleaned and exported, open Microsoft Excel to build the visual presentation.
1. Importing and Formatting Data
- Navigate to Data > Get Data > From File > From Text/CSV.
- Select
sales_export.csvand click Load. - Press
Ctrl + Tto ensure the data is formatted as an official Excel Table namedTable_Sales. This ensures structured references update automatically when new records arrive.
2. Constructing Executive KPI Metric Cards
Create a dedicated dashboard worksheet and add 4 KPI cards at the top using structured table formulas:
- Total Revenue:
=SUM(Table_Sales[revenue])(Format as Currency: ₹) - Total Orders:
=COUNTA(Table_Sales[order_id]) - Units Sold:
=SUM(Table_Sales[quantity]) - Average Order Value:
=AVERAGE(Table_Sales[revenue])
3. Developing Charts with Pivot Tables
- Territory Column Chart: Insert a Pivot Table with
regionin Rows andrevenuein Values. Insert a 2D Clustered Column Chart, format currency labels, and hide field buttons. - Category Share Donut Chart: Insert a Pivot Table with
categoryin Rows andrevenuein Values. Create a Donut Chart, set Donut Hole Size to65%, and display percentage data labels. - Monthly Revenue Trendline: Group the
order_datefield by Months, plot a Smooth Line Chart, and highlight high/low sales points.
4. Connecting Interactive Slicers
To make the dashboard dynamic for leadership review:
- Click any Pivot Chart and select PivotChart Analyze > Insert Slicer.
- Select Region and Category.
- Right-click each slicer, choose Report Connections, and tick all Pivot Tables on your calculation sheet.
- Result: Clicking "West" or "Electronics" updates every KPI card, donut chart, and trendline simultaneously across your report.
Frequently Asked Interview Questions on SQL & Dashboards
Q1: What is the primary operational difference between WHERE and HAVING in SQL?
The WHERE clause filters individual records before any grouping or aggregation takes place. The HAVING clause filters aggregated values generated by a GROUP BY statement.
Q2: Why export from SQLite to Excel instead of creating charts directly in BI tools?
Excel remains the most widely deployed analytical tool in corporate environments. Exporting curated SQL extracts to Excel enables non-technical executives and stakeholders to slice and explore data without needing direct database access credentials or costly BI software licensing.
Q3: How do you ensure dashboard slicers update all charts together in Excel?
By default, a slicer only controls the specific Pivot Table it was created from. You must right-click the slicer, open Report Connections, and select all Pivot Tables linked to your charts so that filter actions cascade across the entire sheet.
Discover Talent™ Digital Network
Connect with our learning platforms, open-source repositories, business listings, and digital tools.
Comments