How to Make a Dashboard in Excel — Step-by-Step

A dashboard turns raw data into a visual summary your manager can read in 10 seconds. You don't need Power BI or Tableau — Excel can build dashboards that auto-update and look professional. Here's how, step by step.

What You'll Build

A sales dashboard with:

• KPI cards (Total Revenue, Total Orders, Avg Order Value)
• Revenue by Region chart
• Monthly trend line
• Top 5 Products table
• Interactive slicers to filter by region/month

Step 1: Prepare Your Data

Your raw data should be in a flat table with headers. Every dashboard starts with clean data.

| Order ID | Date       | Region | Product    | Qty | Unit Price | Revenue |
|----------|------------|--------|------------|-----|------------|---------|
| 1001     | 01-Jan-26  | North  | Widget A   | 5   | 200        | 1000    |
| 1002     | 03-Jan-26  | South  | Widget B   | 3   | 350        | 1050    |

Data Rules:

• No merged cells anywhere in the data
• No blank rows between data rows
• Every column must have a header
• Dates should be real dates (right-aligned), not text
• Convert to a Table: select data → Ctrl+T

Step 2: Create Pivot Tables

You'll need 3-4 Pivot Tables — one for each dashboard section.

Pivot Table 1: KPI Summary

1. Select your data table → Insert → PivotTable → New Worksheet
2. Drag Revenue to Values (Sum)
3. Drag Order ID to Values (Count)
4. Add a calculated field for Avg Order Value: =Revenue/'Order ID'

Pivot Table 2: Revenue by Region

1. Create another PivotTable on the same sheet
2. Drag Region to Rows
3. Drag Revenue to Values (Sum)
4. Sort descending by Revenue

Pivot Table 3: Monthly Trend

1. Create another PivotTable
2. Drag Date to Rows → right-click → Group → Months
3. Drag Revenue to Values (Sum)

Pivot Table 4: Top Products

1. Create another PivotTable
2. Drag Product to Rows
3. Drag Revenue to Values (Sum)
4. Right-click → Filter → Top 10 → change to Top 5

Step 3: Create Charts

Bar Chart for Region Revenue

1. Select Pivot Table 2
2. Insert → Bar Chart → Clustered Bar
3. Remove gridlines, legend (if only one series), and field buttons
4. Add data labels → position "Outside End"
5. Change bar color to a single solid color (dark blue or green)

Line Chart for Monthly Trend

1. Select Pivot Table 3
2. Insert → Line Chart → Line with Markers
3. Remove gridlines, add data labels on last point only
4. Change line color to match your brand

Step 4: Build KPI Cards with Formulas

KPI cards are just cells with large fonts and borders. No special tool needed.

Total Revenue:    =GETPIVOTDATA("Revenue", PivotTable1)
Total Orders:     =GETPIVOTDATA("Order ID", PivotTable1)
Avg Order Value:  =Total_Revenue / Total_Orders

Format these cells:

• Font size: 28-36pt for the number
• Font size: 10pt for the label above
• Add a colored bottom border (green for positive, red for negative)
• Number format: ₹#,##0 for currency

Step 5: Add Slicers for Interactivity

1. Click any Pivot Table
2. PivotTable Analyze → Insert Slicer
3. Check Region and Date (Month)
4. Click OK — slicers appear as filter buttons
5. Connect to ALL Pivot Tables: Right-click slicer → Report Connections → check all 4 Pivot Tables

Now when someone clicks "North" on the slicer, ALL charts and KPIs filter to North region.

Step 6: Design the Dashboard Layout

Create a Dashboard Sheet

1. Create a new sheet named "Dashboard"
2. Hide gridlines: View → uncheck Gridlines
3. Set background color to light gray (#F5F5F5)
4. Move all charts and slicers to this sheet (right-click chart → Move Chart)

Layout Grid (suggested):

┌──────────────────────────────────────────────────┐
│  DASHBOARD TITLE           [Region] [Month]      │  ← Row 1-3
├────────────┬────────────┬────────────────────────┤
│ ₹ Revenue  │ # Orders   │ ₹ Avg Value           │  ← KPI Cards
├────────────┴────────────┴────────────────────────┤
│ Revenue by Region (Bar)  │ Monthly Trend (Line)  │  ← Charts
├──────────────────────────┴───────────────────────┤
│ Top 5 Products (Table)                           │  ← Table
└──────────────────────────────────────────────────┘

Design Tips:

• Colors: Use max 2-3 colors. Dark blue for headers, green for positive, gray for secondary
• Fonts: One font family only (Calibri or Segoe UI). Size hierarchy: Title 18pt, KPI numbers 28pt, labels 10pt
• Alignment: Snap charts to cells using Alt+drag for perfect alignment
• White space: Don't cram everything — leave at least 1 row/column gap between sections
• Remove chart clutter: No 3D, no unnecessary legends, no heavy gridlines

Step 7: Protect and Share

1. Hide the raw data sheet and Pivot Table sheet
2. Protect the Dashboard sheet: Review → Protect Sheet (allow: Select locked cells only)
3. Save as .xlsx for sharing

Formulas You Need for Dashboards

• SUMIFS / COUNTIFS — KPI calculations with filters
• GETPIVOTDATA — pull values from Pivot Tables
• INDEX-MATCH — dynamic data pull
• TEXT — format numbers/dates in labels
• OFFSET / INDIRECT — dynamic chart ranges
• IFERROR — handle missing data gracefully