How to Build an Interactive Dashboard in Excel

An interactive Excel dashboard is one sheet that shows your key numbers and charts, with slicers that let anyone filter everything by region, product or date in one click. You build it from three layers: a clean data table, PivotTables that summarize it, and a dashboard sheet with KPI cards, PivotCharts and connected slicers. This tutorial walks through each layer so you end up with a dashboard that updates with a single refresh.

Plan the dashboard before you open Excel

The most common reason dashboards fail is not technical. They try to show everything and end up answering nothing. Before building, write down answers to three questions:

1. Who will use it? A store manager, a sales director and a business owner need different levels of detail.
2. What decisions will it support? For example: which regions need attention this week, which products to reorder, whether we are on track for the monthly target.
3. Which 3–6 numbers matter most? These become your KPI cards. Typical choices are revenue, units, number of orders, average order value and change versus last period.

Then sketch the layout on paper: KPI cards across the top, a trend chart in the middle, comparisons by category below, and slicers on the left or top. Readers scan from top-left, so the most important number goes there. Our guide to KPIs every business should track can help you choose the right measures.

Layer 1: Prepare the data

Put the raw data on its own sheet named Data and convert it to an Excel Table with Ctrl+T. Name the table tblSales. For this tutorial, assume columns OrderDate, Region, Store, Product, Category, Units and Revenue, with one row per order.

• Check that dates are real dates and numbers are real numbers.
• Remove blank rows, subtotals and merged cells.
• If the data comes from an export you receive regularly, load it with Power Query instead of pasting it, so refreshing the dashboard also refreshes the data. Our Power Query guide shows how.

Layer 2: Build the PivotTables

On the Calc sheet, create one PivotTable for each visual you plan to show. Each one starts the same way: click inside tblSales, Insert > PivotTable, Existing Worksheet, and choose a location on Calc. Leave enough empty space between PivotTables so they do not overlap when they grow.

Name each PivotTable in PivotTable Analyze > PivotTable Name. Clear names make slicer connections and formulas much easier to manage. If you need a refresher on grouping and sorting, see our step-by-step PivotTable guide.

Layer 3: Design the dashboard sheet

KPI cards

On the Dashboard sheet, create a row of cards: a label cell ("Revenue") above a large number cell. Pull each number from ptKPI with GETPIVOTDATA, which keeps working even when the PivotTable layout changes. Type = and click the value in the PivotTable and Excel writes the formula for you:

Revenue:
=GETPIVOTDATA("Sum of Revenue", Calc!$A$3)

Units:
=GETPIVOTDATA("Sum of Units", Calc!$A$3)

Average order value:
=IFERROR(GETPIVOTDATA("Sum of Revenue", Calc!$A$3) / GETPIVOTDATA("Count of OrderDate", Calc!$A$3), 0)

The data field names must match the names shown in your PivotTable's Values area. Format the KPI cells with a large font and a number format such as $#,##0,"K" to display thousands compactly. Add a smaller line underneath for context, such as the target or the previous period.

Charts

Click inside ptTrend and choose PivotTable Analyze > PivotChart > Line. Cut the chart (Ctrl+X) and paste it onto the Dashboard sheet. Repeat for ptRegion and ptProduct with clustered bar charts. Then clean each chart:

• Right-click a field button on the chart and choose Hide All Field Buttons.
• Delete the legend when there is only one series.
• Write titles that state the question or the takeaway, such as "Revenue by month."
• Use one accent color and neutral grays; avoid 3D effects.
• Align charts to the grid by holding Alt while dragging, and make widths consistent.

Add context with targets and comparisons

A number on its own rarely tells the reader whether things are good or bad. Put a monthly target in a labeled input cell on the Calc sheet and show progress next to the Revenue card with a simple formula such as =B4/Calc!$H$2 formatted as a percentage, where B4 holds the Revenue KPI and H2 the target. You can then use Home > Conditional Formatting to color that percentage green when it reaches 100% and amber when it falls short. Keep color meaningful: if everything is highlighted, nothing stands out. A short text line under the trend chart, written each week, can explain the main change in plain language.

Add interactivity with slicers and a timeline

1. Click any PivotTable on Calc, go to PivotTable Analyze > Insert Slicer, and select Region and Category.
2. With a PivotTable selected, choose Insert Timeline and pick OrderDate.
3. Cut the slicers and timeline and paste them onto the Dashboard sheet.
4. Right-click each slicer > Report Connections (Filter Connections for the timeline) and check every PivotTable: ptKPI, ptTrend, ptRegion and ptProduct.
5. Test: click a region and confirm that the KPI cards and all three charts change together.

Slicers can only connect PivotTables built from the same source, which is another reason to keep all your data in one Table or in the Data Model. Use the Slicer tab to set the number of columns and a style that matches your color scheme.

Finish, protect and maintain the dashboard

• Turn off gridlines and headings on the Dashboard sheet (View tab) for a clean look.
• Stop columns from resizing on refresh: in each PivotTable's Options, uncheck "Autofit column widths on update."
• Refresh everything with Data > Refresh All after new data arrives, and consider "Refresh data when opening the file."
• Protect the layout with Review > Protect Sheet, allowing "Use PivotTable & PivotChart" so slicers keep working. Test this, since behavior can vary by Excel version.
• Add a "Last updated" note so readers know how current the numbers are.

Next steps

Build a first version with three KPI cards, two charts and one slicer, then show it to the person who will use it and ask what decision they would make from it. Improve based on that answer. When your data grows beyond a single table or you need to share an always-up-to-date dashboard online, compare your options in Excel vs Power BI and apply the same design principles in a Power BI report.

Watch: video lessons

▶

▶

Frequently asked questions