Excel Dashboard Creation

An Excel Dashboard is a single-screen visual summary that brings together the most important data, charts, and metrics from a workbook. Instead of scrolling through multiple sheets of raw data, a dashboard presents key information at a glance in a clean, organized, and interactive format. Dashboards are widely used in business to monitor performance, track KPIs (Key Performance Indicators), and support decision-making.

What Makes a Good Dashboard?

• Shows only the most important information — no clutter.
• Allows the viewer to understand the data at a glance.
• Is interactive — filters and slicers allow the user to explore the data.
• Is visually clear — uses charts, colors, and layout effectively.
• Updates automatically when the underlying data changes.

Planning the Dashboard

Before building, it is important to plan the dashboard structure. Answer these questions first:

• Who is the audience? (Manager, client, analyst?)
• What are the key questions the dashboard must answer?
• What data is available as the source?
• What charts and metrics should be displayed?

Example Dashboard Goal

  Sales Performance Dashboard

  Key Questions:
  1. What is the total revenue this year?
  2. Which region has the highest sales?
  3. How are monthly sales trending?
  4. Which product is the top seller?
  5. What is the sales target vs actual comparison?

Setting Up the Workbook Structure

A well-organized workbook separates raw data, calculations, and the dashboard into different sheets.

Recommended Sheet Structure

• Data Sheet: Contains the raw, unformatted source data. This sheet is usually hidden from regular users.
• Calculations Sheet (optional): Contains Pivot Tables, helper formulas, and intermediate summaries that feed the dashboard.
• Dashboard Sheet: The visual front-end — contains charts, KPI boxes, slicers, and summaries.

Preparing the Data

Format Data as an Excel Table

1. Click inside the raw data.
2. Press Ctrl + T to convert it to an Excel Table.
3. Give the table a meaningful name in the Table Design tab (e.g., "SalesData").

Using an Excel Table ensures that Pivot Tables and formulas automatically include new rows of data when the source is updated.

Creating KPI Summary Cards

KPI cards (also called metric tiles or scorecards) are small boxes displaying a single key number — such as Total Revenue, Total Orders, or Average Deal Size. They provide instant context at the top of the dashboard.

How to Create a KPI Card

1. Select a group of merged cells (e.g., merge B2:D4 into one block).
2. Add a border and a background fill color to create a card effect.
3. In the merged cell, enter a formula to pull the metric (e.g., =SUM(SalesData[Revenue])).
4. Add a text label above or below using a separate merged cell (e.g., "Total Revenue").
5. Format the number as Currency or with appropriate decimal places.

Example KPI Layout

  +------------------+  +------------------+  +------------------+
  | Total Revenue    |  | Total Orders     |  | Avg Deal Size    |
  | $1,250,000       |  | 3,420            |  | $365             |
  +------------------+  +------------------+  +------------------+

Building Pivot Tables for the Dashboard

Each chart on the dashboard is typically driven by a Pivot Table on the Calculations sheet. Set up one Pivot Table per chart or metric group.

Example Pivot Tables

  Pivot Table 1: Sales by Region
  Rows: Region | Values: Sum of Sales

  Pivot Table 2: Monthly Sales Trend
  Rows: Month | Values: Sum of Sales

  Pivot Table 3: Sales by Product
  Rows: Product | Values: Sum of Sales

  Pivot Table 4: Target vs Actual
  Rows: Month | Values: Sum of Target, Sum of Actual

Creating Charts for the Dashboard

Each Pivot Table gets a connected Pivot Chart. The chart should be placed on the Dashboard sheet.

Chart Recommendations

• Monthly Trend: Line chart — shows the trend over time clearly.
• Sales by Region: Clustered Bar or Column chart — easy to compare categories.
• Sales by Product: Pie or Doughnut chart — shows proportion of whole.
• Target vs Actual: Combo chart — Column for actual, Line for target.

Moving Charts to the Dashboard Sheet

1. Right-click the chart → Move Chart.
2. Select the Dashboard sheet as the destination.
3. Click OK. Position and resize the chart on the dashboard.

Adding Slicers for Interactivity

Slicers allow users to filter the entire dashboard by clicking buttons — without needing to know how Pivot Tables work.

Adding and Connecting Slicers

1. Click on any Pivot Table on the Calculations sheet.
2. Go to PivotTable Analyze → Insert Slicer.
3. Select the fields to filter by (e.g., Region, Year, Product Category).
4. Click OK. Move the slicers to the Dashboard sheet.
5. Right-click each slicer → Report Connections → check all relevant Pivot Tables.

Now clicking a slicer button updates all connected charts and KPI values simultaneously.

Adding a Timeline Slicer for Dates

A Timeline Slicer provides a visual date filter with a horizontal bar that can be dragged to select date ranges.

How to Add a Timeline

1. Click a Pivot Table that contains a date field.
2. Go to PivotTable Analyze → Insert Timeline.
3. Select the date column and click OK.
4. Move the timeline to the dashboard and connect it to all Pivot Tables via Report Connections.

Designing the Dashboard Layout

Layout Tips

• Place KPI cards at the top — most important numbers should be seen first.
• Place slicers and timeline on the left or top for easy access.
• Place charts in the main body area, arranged in a logical order.
• Use consistent colors — limit to 2–3 main colors for a professional look.
• Remove gridlines: View → uncheck Gridlines.
• Hide row and column headers: View → uncheck Headings.
• Hide the formula bar: View → uncheck Formula Bar.
• Use a dark or neutral background (rectangle shape or cell fill) behind sections to create visual separation.

Removing Chart Borders and Backgrounds

Right-click a chart → Format Chart Area → Set border to "No Line" and fill to "No Fill" so charts blend into the dashboard background.

Protecting the Dashboard

Once the dashboard is complete, it should be protected so users cannot accidentally break formulas or charts.

Hiding Source Sheets

1. Right-click the Data or Calculations sheet tab.
2. Select Hide. The sheet is hidden but still works.

Protecting the Dashboard Sheet

1. Go to Review → Protect Sheet.
2. Set a password (optional).
3. Specify what users are allowed to do (e.g., use slicers but not edit cells).
4. Click OK.

Refreshing the Dashboard

When the source data is updated with new records, the dashboard needs to be refreshed.

• Go to Data → Refresh All — this refreshes all Pivot Tables and their linked charts at once.
• If using an Excel Table as the data source, new rows added to the table are automatically included on refresh.

Complete Dashboard Checklist

• Source data is formatted as an Excel Table.
• Pivot Tables are set up on a separate Calculations sheet.
• Pivot Charts are created and moved to the Dashboard sheet.
• KPI cards display key metrics using formulas.
• Slicers and timelines are added and connected to all Pivot Tables.
• Gridlines, headings, and formula bar are hidden.
• Colors and layout are clean and consistent.
• Source data sheets are hidden.
• The Dashboard sheet is protected.
• Refresh All is tested and working.

Summary

• A dashboard is a single-screen summary of the most important data, charts, and metrics.
• The workbook is organized into Data, Calculations, and Dashboard sheets.
• KPI cards show key numbers at a glance using formulas linked to the data.
• Pivot Tables on the Calculations sheet power the Pivot Charts on the Dashboard sheet.
• Slicers and timelines make the dashboard interactive — filtering all connected charts at once.
• Visual design elements such as consistent colors, hidden gridlines, and clean layouts make the dashboard professional and easy to read.
• Protecting sheets and hiding source data prevents accidental changes by users.