Skip to content
Tutorials Center
My learning

Topics

Lesson 60 of 93 ยท Charts and Visualization

Building an Interactive Dashboard

Build an interactive Excel dashboard: plan the layout, show KPIs, link charts to PivotTables, add slicers and timelines, protect the sheet and refresh data.

A dashboard is a single sheet that answers a few important questions at a glance: how much did we sell, are we ahead of target, which region is weak? It pulls together numbers, charts and filters so that someone can read the state of the business in ten seconds and click to dig deeper. In Excel, an interactive dashboard is not a special feature. It is a careful combination of tools you already know: a clean data table, PivotTables, charts and slicers.

Excel cannot run on this site, so the steps and layouts below are described in text and read-only blocks. The TryIt uses a small JavaScript data-model stand-in: one dataset, two "slicers" and KPI cards that recalculate when you change the selection. It shows the idea (one filter, many outputs). It is not how Excel is implemented.

Anatomy of a dashboard

KPI cards The two or three numbers that matter.
Charts Trend, comparison, share.
Slicers Filters everyone can click.
Refresh One button updates it all.
1 / 2

Start with a question, then a layout

Before touching a chart, write down who reads the dashboard and what decisions it supports. A sales manager might need: total revenue this year, revenue against target, revenue by month, revenue by product category, and the best regions. Five questions mean about five visuals. Anything that answers no question is clutter.

Then sketch a grid. A common, readable layout:

TEXT
+--------------------------------------------------------+
| Title                                  [Year slicer]   |
+----------+----------+----------+-----------------------+
| KPI 1    | KPI 2    | KPI 3    | [Region slicer]       |
| Revenue  | Orders   | Avg order|                       |
+----------+----------+----------+-----------------------+
| Revenue by month (line)        | Revenue by category   |
|                                | (bar)                 |
+--------------------------------+-----------------------+

Put the most important number at the top left, where eyes land first. Keep the dashboard inside one screen if possible, so nobody has to scroll to understand it. Match the width of columns to make shapes line up: it helps to set the whole sheet's column width to something small and uniform and to merge nothing (use Center Across Selection if you need a centered title).

Sheet structure

Use at least three sheets:

  1. Data: an Excel Table named something like Sales, with one row per transaction. Never format the dashboard on this sheet.
  2. Calc: PivotTables and helper formulas. These are the engine room and can look plain.
  3. Dashboard: only titles, KPI cells, charts and slicers.

Keeping them apart means you can paste new data into the table without disturbing the design, and you can hide or protect the working sheets.

KPI cards

A KPI card is a cell (or a few) with a big number and a small label. Build the number with a formula and format it with a number format such as #,##0 or 0.0% (see Formatting Cells).

TEXT
Revenue        =SUM(Sales[Amount])
Orders         =COUNTA(Sales[OrderID])
Average order  =B3/B4
vs target      =B3/Target-1          (formatted as +0.0%;-0.0%)

If the KPIs must react to slicers, plain SUM(Sales[Amount]) will not: slicers filter PivotTables and tables, but a formula over the whole column ignores the filter. Two common solutions:

  • Let a PivotTable calculate the total and read it with GETPIVOTDATA, for example =GETPIVOTDATA("Amount",Calc!$A$3). It follows the slicer because the PivotTable does. See PivotTable Basics.
  • Use SUBTOTAL(109, Sales[Amount]) on a table that is filtered by a table slicer. SUBTOTAL ignores rows hidden by filters.

Add a small comparison line under the number ("+8.2% vs last year") colored by a conditional format, for example green for positive and red for negative. See Conditional Formatting.

Charts linked to PivotTables

On the Calc sheet, build a PivotTable for each chart: Month by Revenue, Category by Revenue, and so on. Select a cell inside one and choose PivotTable Analyze > PivotChart. The chart is tied to the pivot: when a filter changes, the chart changes. Cut the chart (Ctrl+X) and paste it onto the Dashboard sheet; it keeps its link to the pivot on the other sheet.

Tips for chart choice are in Choosing the Right Chart Type. For dashboards in particular:

  • Line chart for change over time, bar chart for comparing categories, and avoid 3-D effects.
  • Remove what does not help: gridlines, the legend when there is a single series, a chart title that repeats the sheet title.
  • In PivotCharts, hide the field buttons (right-click a button, Hide All Field Buttons on Chart), so the chart looks clean.
  • Keep colors consistent: the same category should have the same color in every chart.

Slicers and timelines

A slicer is a set of buttons that filters a PivotTable. Select a pivot, choose PivotTable Analyze > Insert Slicer, and tick fields such as Region and Category. A timeline (Insert Timeline) filters by date, with a bar you drag to choose months, quarters or years.

To make one slicer control several pivots (and therefore several charts), right-click the slicer, choose Report Connections, and tick every PivotTable it should drive. All PivotTables must be built from the same data source (or the same cache) for this to appear. The detail is covered in Slicers, Timelines and PivotCharts.

Style slicers to fit: set the number of columns (Slicer tab > Buttons > Columns) so a list of twelve months becomes a compact grid, and match their color to your theme.

Run it: one filter, many outputs

The stand-in holds a tiny dataset and two selected lists, like two slicers. Each "visual" is a function of the filtered rows, so all of them change together, which is exactly what a connected slicer does.

JS A data model with slicers and KPI cards

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Protecting and polishing

When the layout is done:

  • Turn off View > Gridlines and Headings on the dashboard sheet for a cleaner canvas, and set a light background fill on the area.
  • Hide helper sheets (right-click the tab > Hide), or place them at the end and name them clearly.
  • Review > Protect Sheet to stop accidental edits. Slicers and PivotTables need specific permissions: in the protection dialog tick Use PivotTable & PivotChart. For slicers, right-click the slicer > Size and Properties and untick Locked, then protect the sheet with Edit objects ticked. Without those steps, the buttons do not respond on a protected sheet. A password is not strong security; see Collaboration, Comments and Protecting Work.
  • Set the print area and orientation (Page Layout) if people print or export to PDF.
  • Test with a colleague. If they cannot find the answer within a few seconds, simplify.

Refreshing

PivotTables do not update when the source data changes until you refresh them: PivotTable Analyze > Refresh All or Ctrl+Alt+F5. If the data is an Excel Table, new rows are included automatically in the source; with a fixed range you would need to change the data source. You can also set Refresh data when opening the file in PivotTable Options > Data. If the data comes from Power Query, Data > Refresh All refreshes queries and pivots built on them (check the order if you chain them). Formulas recalculate on their own; pivots and queries need that refresh.

A good habit is to add a "Last updated" cell with a formula or a typed date, so readers know how fresh the numbers are.

Common mistakes

  • Too much on one page. Six to eight elements is plenty.
  • Formulas that ignore slicers. Check every KPI by clicking a slicer and watching it change.
  • Separate data sources for each pivot. Slicers cannot connect them; create the PivotTables from the same table.
  • Merged cells everywhere. They break selection and resizing; use alignment options instead.
  • Misleading charts. A truncated axis or a 3-D pie exaggerates; see Formatting and Improving Charts.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why do dashboards usually keep data, calculations and presentation on separate sheets?

    Separation keeps the dashboard sheet clean and lets you replace or refresh data without disturbing layout. It is a structure choice, not a technical requirement.
  2. 2. A KPI cell contains =SUM(Sales[Amount]). You click a slicer on a PivotTable. What happens to the KPI?

    Slicers filter the PivotTables connected to them. A formula over the whole table column does not know about that filter; use GETPIVOTDATA on a pivot or SUBTOTAL on a filtered table.
  3. 3. How can one slicer control several PivotTables?

    Right-click the slicer and choose Report Connections. The pivots must share a data source for them to appear in the list.
  4. 4. Your pivot and chart show old numbers after you pasted new rows into the data. What do you do?

    PivotTables only recalculate when you refresh them. If the source is a Table, the new rows are already part of it.

Summary

  • Start from the questions the reader has, then sketch a grid with KPIs on top and charts below.
  • Use separate sheets for data (an Excel Table), calculation (PivotTables) and presentation.
  • KPI cells must be built so they follow filters: GETPIVOTDATA on a pivot or SUBTOTAL on a filtered table.
  • Charts linked to PivotTables change with slicers and timelines; Report Connections let one slicer drive many pivots.
  • Protect the sheet with the right permissions, hide gridlines, and refresh with Refresh All when data changes.

Next: PivotTable Basics.