How to Create an Excel Dashboard from Raw Data

Sample Dataset

We will build a Sales Performance Dashboard using this dataset structure (imagine 1,000 rows):

The dashboard will answer: Total Revenue, Total Profit, Revenue by Region, Revenue by Month (trend), Top 5 Products, and Revenue by Category. A Region slicer and a Timeline slicer will make it interactive.

Step 1 Define the Questions

Before opening Excel, write down 4–6 specific questions the dashboard must answer. Vague dashboards serve no one. Specific questions lead to specific charts.

Our questions: (1) What is total revenue this year? (2) What is total profit this year? (3) Which region is performing best? (4) How is revenue trending month-on-month? (5) Which are the top 5 products by revenue? (6) What is the revenue split by product category?

Each question maps to a specific visual: Q1 and Q2 → KPI cards; Q3 → Bar chart by region; Q4 → Line chart by month; Q5 → Horizontal bar chart; Q6 → Pie or donut chart.

Step 2 Clean the Raw Data

Follow the 13-step data cleaning process (see our guide: How to Clean Messy Data in Excel). Key checks for a sales dataset:

• No duplicate OrderIDs
• OrderDate is a proper date, not text
• Region, Category have consistent capitalisation
• Revenue and Cost are numbers, not text
• No negative Revenue values (unless returns are valid)
• Calculated column check: Revenue = Quantity × UnitPrice

Step 3 Format as an Excel Table

Click anywhere in your data → Insert → Table → confirm the range includes headers → click OK. Rename the table "SalesData" in the Table Design tab.

Why a Table? Excel Tables automatically expand when you add new rows. PivotTables built on a Table pick up new data on refresh without needing to update the source range. This is the foundation of a maintainable dashboard.

Step 4 Set Up a PivotTable Sheet

Create a new sheet named "PIVOTS". Keep all PivotTables on this sheet — never put them on the dashboard sheet. This keeps the dashboard clean and makes the PivotTables easy to maintain.

Create 4 PivotTables on the PIVOTS sheet:

1. PT_Revenue_Region: Region (Rows) → Revenue (Values, Sum)
2. PT_Revenue_Month: OrderDate (Rows, grouped by Month) → Revenue (Values, Sum)
3. PT_Top_Products: Product (Rows) → Revenue (Values, Sum), sorted descending, show top 10
4. PT_Category_Split: Category (Rows) → Revenue (Values, Sum)

Also add two summary cells (not in a PivotTable) on the PIVOTS sheet for the KPI cards:

=SUM(SalesData[Revenue])   → Total Revenue
=SUM(SalesData[Revenue])-SUM(SalesData[Cost])  → Total Profit

Step 5 Build PivotCharts

Click inside PT_Revenue_Region → Insert → PivotChart → Clustered Bar chart. The chart appears on the PIVOTS sheet — do not move it to the dashboard yet.

Repeat for each PivotTable:

• PT_Revenue_Region → Clustered Bar (horizontal for region names)
• PT_Revenue_Month → Line chart with markers
• PT_Top_Products → Bar chart (sorted descending), filter to Top 5
• PT_Category_Split → Donut chart

Remove chart titles, legends and gridlines that add clutter. You will add clean titles on the dashboard sheet. Remove the PivotChart field buttons (right-click the chart → Hide All Field Buttons).

Step 6 Create KPI Summary Cells

On the PIVOTS sheet, set up named ranges for KPI values:

=GETPIVOTDATA("Revenue",PT_Revenue_Region_cell)
=GETPIVOTDATA("Revenue",PT_Revenue_Region_cell)-GETPIVOTDATA("Cost",PT_Revenue_Region_cell)

Or more simply, use SUMIF against the source table — but note that these will not respond to slicers. For slicer-responsive KPIs, use GETPIVOTDATA pulling from a PivotTable that the slicer controls.

Step 7 Design the Dashboard Layout

Create a new sheet named "DASHBOARD". Set the zoom to 85% so the full dashboard fits in one screen. Freeze row 1 as the title bar. Hide the row and column headers (View → uncheck Headings). Turn off gridlines (View → uncheck Gridlines).

Sketch the layout before adding elements:

Step 8 Populate the Dashboard Sheet

Go to the PIVOTS sheet. Click a chart → Ctrl+C to copy. Go to the DASHBOARD sheet → Ctrl+V to paste. Resize and position it according to your layout sketch. Repeat for all four charts.

For KPI cards: draw rounded rectangles (Insert → Shapes), fill with your brand colour, and in a cell inside the shape, type ="₹"&TEXT(TotalRevenue,"#,##0") referencing the named cell on PIVOTS. Apply large, bold font (20pt+) so the number is readable at a glance.

Step 9 Add Slicers and Timelines

Click any PivotTable on the PIVOTS sheet → PivotTable Analyze → Insert Slicer → check "Region" → OK. A Region slicer appears. Copy and paste it to the DASHBOARD sheet.

Insert a Timeline: PivotTable Analyze → Insert Timeline → check "OrderDate" → OK. This creates a date-range slider. Copy to DASHBOARD and position in the slicer bar.

Style slicers to match your dashboard colour scheme: right-click slicer → Slicer Settings / Slicer Style.

Step 10 Connect Slicers to All PivotTables

Right-click the Region slicer → Report Connections (or PivotTable Connections). Check all four PivotTables: PT_Revenue_Region, PT_Revenue_Month, PT_Top_Products, PT_Category_Split. Click OK.

Do the same for the Timeline slicer. Now clicking "North" in the Region slicer updates all four charts simultaneously.

Test: click "South" in the Region slicer. All four charts should filter to South data. Click "Clear Filter" (the X button on the slicer). All charts should revert to showing all regions.

Step 11 Format and Polish

• Use a consistent colour palette — maximum 3 primary colours. One for bars, one for the line chart, one for the donut segments.
• Add a text label above each chart explaining what it shows (e.g., "Revenue by Region (₹)" as a plain cell — not a chart title).
• Format all KPI numbers with thousands separators and ₹ prefix.
• Add a "Last Updated:" cell that uses =TODAY() or =NOW() to show the refresh date.
• Remove all chart borders and chart area backgrounds — let the dashboard background colour show through.

Step 12 Protect and Test

Right-click the RAW_DATA sheet tab → Protect Sheet. Right-click the PIVOTS sheet tab → Hide.

On the DASHBOARD sheet: Review → Protect Sheet → uncheck "Select locked cells" but leave "Use PivotTable & PivotChart" checked so slicers still work.

Test the dashboard with 5 scenarios:

1. Filter to one region — all charts update
2. Filter to one month — all charts update
3. Filter to a region AND a month — charts show the intersection
4. Clear all filters — charts revert to all data
5. Add a new row to RAW_DATA, right-click any PivotTable → Refresh All — new data appears in all charts

Recommended Dashboard Layout

┌─────────────────────────────────────────────────────────┐
│  SALES PERFORMANCE DASHBOARD   │  Last Updated: 29-Sep  │
├─────────────────────────────────────────────────────────┤
│  [Region Slicer]         [Timeline: Jan–Sep 2026]       │
├──────────────┬──────────────┬──────────────────────────┤
│ Total Rev    │ Total Profit │  Profit Margin            │
│ ₹48,25,000   │ ₹12,60,000   │  26.1%                   │
├──────────────────────────┬──────────────────────────────┤
│  Revenue by Region (Bar) │  Monthly Revenue Trend (Line)│
│                          │                              │
├──────────────────────────┼──────────────────────────────┤
│  Top 5 Products (Bar)    │  Revenue by Category (Donut) │
│                          │                              │
└──────────────────────────┴──────────────────────────────┘

QC Checklist Before Sharing

Capstone Assignment

Trainer's Practical Advice

Common Mistakes

Frequently Asked Questions