Building a Dashboard in Excel

A dashboard in Excel is a visual summary page that shows key information at a glance.

Think of a dashboard like:

A car dashboard — it shows speed, fuel, temperature — all in one place.

In Excel, a dashboard shows:

• Key numbers (KPIs)
• Charts
• Trends
• Comparisons
• Filters

All in one clean screen.

1️⃣ What Is an Excel Dashboard?

An Excel dashboard is:

• A single sheet
• Containing summary data
• Using charts and KPIs
• Often interactive

It helps decision-makers:

• Understand performance quickly
• Identify problems
• Monitor progress

2️⃣ Step 1: Prepare Your Data

Dashboards depend on clean data.

Example Raw Sales Data

| Date       | Region | Product | Sales | Expenses |
|------------|--------|----------|--------|----------|
| 01-Jan-24  | East   | A        | 5000   | 3000     |
| 05-Jan-24  | West   | B        | 7000   | 4000     |
| 12-Jan-24  | East   | C        | 4000   | 2500     |
| 20-Jan-24  | West   | A        | 8000   | 5000     |
| 28-Jan-24  | East   | B        | 6000   | 3500     |

3️⃣ Step 2: Create Summary Calculations (KPIs)

KPI = Key Performance Indicator

Examples:

• Total Sales
• Total Expenses
• Profit
• Average Sales

Example Calculations

=SUM(D2:D6)        // Total Sales
=SUM(E2:E6)        // Total Expenses
=SUM(D2:D6)-SUM(E2:E6)   // Profit
=AVERAGE(D2:D6)    // Average Sales

KPI Summary Table

| KPI             | Value  |
|-----------------|--------|
| Total Sales     | 30000  |
| Total Expenses  | 18000  |
| Profit          | 12000  |
| Average Sales   | 6000   |

4️⃣ Step 3: Create Charts

Dashboards use charts to visualize trends.

Example 1: Sales by Region

| Region | Total Sales |
|--------|------------|
| East   | 15000      |
| West   | 15000      |

Best Chart:

• Column Chart
• Pie Chart (if comparing proportion)

Example 2: Sales Trend Over Time

| Date      | Sales |
|-----------|-------|
| 01-Jan-24 | 5000  |
| 05-Jan-24 | 7000  |
| 12-Jan-24 | 4000  |
| 20-Jan-24 | 8000  |
| 28-Jan-24 | 6000  |

Best Chart:

• Line Chart

5️⃣ Step 4: Use Pivot Tables (Optional but Powerful)

Pivot Tables help summarize data dynamically.

Example:

| Region | Product | Total Sales |
|--------|----------|------------|
| East   | A        | 5000       |
| East   | B        | 6000       |
| East   | C        | 4000       |
| West   | A        | 8000       |
| West   | B        | 7000       |

Pivot Tables make dashboards:

• Flexible
• Interactive
• Dynamic

6️⃣ Step 5: Add Filters (Slicers)

Slicers allow users to filter data visually.

Example:

• Filter by Region
• Filter by Product
• Filter by Date

No formulas needed — use:

Insert → Slicer

7️⃣ Step 6: Design the Dashboard Layout

A good dashboard should:

• Have KPIs at the top
• Charts in the middle
• Filters on the side
• Consistent colors
• Clear titles

Example Layout Structure:

| Section | Content |
|----------|----------|
| Top | KPI Cards |
| Middle | Charts |
| Side | Filters |
| Bottom | Detailed Table |

8️⃣ Dashboard Best Practices

| Best Practice | Why It Matters |
|---------------|----------------|
| Keep it simple | Avoid confusion |
| Use consistent colors | Professional look |
| Limit charts | Focus attention |
| Label everything | Improve clarity |
| Use white space | Clean design |

9️⃣ Example Simple Dashboard Structure

Imagine this:

Top Section:

• Total Sales: 30000
• Profit: 12000

Middle Section:

• Line Chart (Sales Trend)
• Column Chart (Sales by Region)

Right Side:

• Region Filter
• Product Filter

This is a basic business dashboard.

🔟 Common Beginner Mistakes

• Too many charts
• No clear purpose
• Using wrong chart type
• Cluttered design
• Not cleaning data first