Excel Dashboard Exercises
Learn to build interactive Excel dashboards using KPI cards, PivotTables, charts, slicers, and summary metrics. Practise transforming business data into clear visual reports that support analysis and decision-making.
1. Introduction to Excel Dashboards
An Excel dashboard is a visual summary of important information from a dataset. It combines key performance indicators (KPIs), charts, summaries, and filters into a single worksheet so users can understand business performance quickly.
For example, a sales dashboard may display total sales, total profit, number of orders, and average order value. Charts can compare sales across regions and products, while slicers allow users to explore selected categories.
A good dashboard is accurate, readable, consistent, and focused on the questions its audience needs to answer.
Excel Dashboard Architecture
A dashboard combines validated data, analysis, metrics, visualizations, and interactive controls.
2. Prepare the Sales Dataset
Create an Excel worksheet named SalesData.
Enter the following records with unique column headings.
This dataset will be used throughout the exercises.
| Order ID | Date | Region | Product | Quantity | Sales (₹) | Cost (₹) |
|---|---|---|---|---|---|---|
| ORD001 | 01-Oct-2026 | West | Laptop | 2 | 1,00,000 | 70,000 |
| ORD002 | 02-Oct-2026 | South | Mobile | 3 | 60,000 | 42,000 |
| ORD003 | 03-Oct-2026 | North | Tablet | 4 | 48,000 | 32,000 |
| ORD004 | 04-Oct-2026 | West | Laptop | 1 | 50,000 | 35,000 |
| ORD005 | 05-Oct-2026 | North | Mobile | 2 | 40,000 | 28,000 |
3. Calculate Dashboard KPIs
Key performance indicators are summarized values that help users evaluate performance. For a sales dashboard, start with total sales, total cost, total profit, order count, and average order value.
3.1 Add Calculated Columns
Assume Sales is in column F and Cost is in column G. Add a Profit column in H and calculate the profit for each order.
=F2-G2
Fill the formula down through the remaining rows.
3.2 KPI Formulas
| KPI | Formula | Expected Result |
|---|---|---|
| Total Sales | =SUM(F2:F6) |
₹2,98,000 |
| Total Cost | =SUM(G2:G6) |
₹2,07,000 |
| Total Profit | =SUM(H2:H6) |
₹91,000 |
| Order Count | =COUNTA(A2:A6) |
5 |
| Average Order Value | =AVERAGE(F2:F6) |
₹59,600 |
| Profit Margin | =IFERROR(SUM(H2:H6)/SUM(F2:F6),0) |
30.54% approximately |
Profit margin is calculated as total profit divided by total sales. Format the result as a percentage with two decimal places.
4. Design KPI Cards
KPI cards display the most important values prominently. In Excel, they can be created with formatted cells or shapes linked to cells containing formulas.
Total Sales
₹2,98,000
All sample ordersTotal Profit
₹91,000
Sales minus costTotal Orders
5
Order recordsProfit Margin
30.54%
Profit ÷ SalesHow to Create KPI Cards in Excel
- Create a separate worksheet named
Dashboard. - Calculate each KPI on a Summary worksheet or in designated cells.
- Insert a shape or format a group of cells as a KPI card.
- Link the displayed value to the cell containing the KPI formula.
- Apply a consistent number format, font size, and heading style.
- Keep the layout aligned and ensure that the labels are clear.
5. Create a Region-Wise Sales Chart
A column chart is useful for comparing sales across regions. Create a PivotTable with Region in Rows and Sum of Sales in Values, then insert a PivotChart or regular chart based on the summarized data.
Expected Summary
| Region | Total Sales |
|---|---|
| North | ₹88,000 |
| South | ₹60,000 |
| West | ₹1,50,000 |
Steps to Create the Chart
- Select the region-wise summary.
- Choose Insert → Column or Bar Chart.
- Add a meaningful title such as "Sales by Region".
- Format the value axis as currency if appropriate.
- Ensure that region names are readable.
- Verify the chart against the source summary.
Use a column chart to compare categories. Use a line chart when the main goal is to show change over time, such as monthly sales.
6. Choose the Right Chart
Different chart types communicate different kinds of information. Choose a chart according to the question the dashboard needs to answer.
| Chart Type | Best Use | Example |
|---|---|---|
| Column Chart | Compare values across categories. | Sales by region. |
| Bar Chart | Compare categories, especially with long labels. | Product-wise sales ranking. |
| Line Chart | Show trends over time. | Monthly sales trend. |
| Pie or Doughnut Chart | Show a small number of parts of a whole. | Sales share by product. |
| Combo Chart | Compare related measures with different scales. | Sales and profit margin. |
Selecting a Chart for the Business Question
7. Add Slicers for Interactive Filtering
Slicers are visual filter controls that allow users to select categories using buttons. They are especially useful for dashboards built with PivotTables and PivotCharts.
How to Add a Region Slicer
- Create a PivotTable from the SalesData worksheet.
- Click inside the PivotTable.
- Open PivotTable Analyze → Insert Slicer.
- Select Region and confirm.
- Move and resize the slicer on the Dashboard worksheet.
- Test each region button and verify the filtered figures.
If the dashboard contains several PivotTables, use Report Connections where available to connect the slicer to compatible reports. A slicer will not automatically control every unrelated chart or formula in a workbook.
8. Dashboard Layout and Formatting
Arrange the dashboard so that users can identify the most important information without searching through the worksheet. Keep related information together and use consistent spacing.
Recommended Layout
- Top: Dashboard title, reporting period, and filter controls.
- First row: KPI cards for Sales, Profit, Orders, and Margin.
- Middle: Sales by Region and Sales by Product charts.
- Bottom: Trends, detailed summaries, and supporting tables.
Use readable fonts, suitable number formats, clear chart titles, and a restrained colour palette. Avoid unnecessary decoration, too many chart types, and excessive gridlines.
9. Build the Dashboard Step by Step
Prepare the Data
Remove unintended blank rows, check column headings, validate dates and numeric values, and convert the source range into an Excel Table if appropriate.
Create Summary Calculations
Calculate total sales, cost, profit, order count, and profit margin. Validate the results.
Create PivotTables
Summarize sales by region and product. Create additional summaries for time periods if needed.
Insert Charts
Choose charts that communicate the comparisons or trends required by the business question.
Add KPI Cards and Slicers
Display the important metrics and provide interactive controls for supported PivotTables and PivotCharts.
Test and Validate
Test filter behaviour, refresh the reports, compare totals with the source data, and check readability.
10. Test Dashboard Accuracy
A visually attractive dashboard is not sufficient if its calculations are incorrect. Validate the underlying numbers before sharing the workbook.
| Test | Expected Check |
|---|---|
| Total Sales | Matches the sum of the source Sales column. |
| Total Profit | Matches total sales minus total cost. |
| Order Count | Matches the number of valid order records. |
| Region Filter | Charts and connected summaries display the selected region. |
| New Records | New source records are included after updating the source range and refreshing. |
| Chart Labels | Categories, values, and units are clear and readable. |
Dashboard Validation Process
11. Common Dashboard Mistakes
| Mistake | Why It Is a Problem | Recommended Improvement |
|---|---|---|
| Too many charts | Important findings become difficult to identify. | Keep only charts that answer meaningful questions. |
| Unclear chart titles | Users cannot tell what is being compared. | Use descriptive titles and meaningful units. |
| Hard-coded KPI values | Values become outdated when source data changes. | Link cards to formula-driven cells. |
| Unconnected slicers | Some charts or metrics may not respond to filters. | Check Report Connections and KPI calculation methods. |
| Unrefreshed PivotTables | Reports may exclude new source records. | Update source ranges and refresh reports. |
| Inconsistent formatting | Figures become harder to compare. | Use consistent currency, percentage, and date formats. |
12. Practical Dashboard Assignments
Complete these assignments using the sample dataset. Try creating the dashboard yourself before comparing your output with the expected checks.
| No. | Assignment | Expected Output |
|---|---|---|
| 1 | Create a Total Sales KPI card. | ₹2,98,000 |
| 2 | Create a Total Profit KPI card. | ₹91,000 |
| 3 | Calculate profit margin. | Approximately 30.54% |
| 4 | Display total orders and average order value. | 5 orders; ₹59,600 average order value |
| 5 | Create a region-wise sales chart. | North, South, and West comparisons |
| 6 | Create a product-wise sales chart. | Laptop, Mobile, and Tablet comparisons |
| 7 | Add a Region slicer. | Connected reports filter by region |
| 8 | Test totals after adding a new record. | Updated figures after refreshing the report |
13. Important Points
- Keep the source data clean and use clear column headings.
- Calculate KPIs from formulas or validated summaries.
- Select charts according to the business question.
- Use slicers with compatible PivotTables and PivotCharts.
- Confirm that KPI cards respond appropriately to filters.
- Refresh PivotTables after updating source data.
- Use consistent number formats and readable labels.
- Reconcile dashboard values with the source data before sharing.
14. Summary
In this module, you learned how to plan an Excel dashboard, calculate sales and profit KPIs, create KPI cards, select appropriate charts, add slicers, and arrange the dashboard for easy interpretation.
You also explored data validation, PivotTable refresh, filter testing, and common dashboard mistakes. These practices help ensure that the dashboard is both readable and reliable.
Practise the assignments using the supplied dataset. Once you can build and validate a dashboard manually, you will be better prepared to automate reporting tasks with VBA.