How to Build an Interactive Excel Dashboard with Linked PivotTables

A complete interactive Excel dashboard, built from scratch in one sitting. We start with a raw sales dataset and finish with a one-page report that filters with a click — monthly trends, top categories and states, salesperson rankings, and in-cell bar charts. No add-ins, no VBA.

I’ll also explain the design decisions along the way, not just the clicks: where the eye goes first, why the layout is arranged the way it is, and how to choose colours that don’t fight each other.

Watch the full build

Prefer reading? The main stages are written out below the video.

The build, stage by stage

The video is a full 52-minute build. Here are the main stages and the key settings, so you can follow along or jump to the part you need.

1. Plan the layout first

Put the most important number top left. That’s where the eye starts reading. Place the next most important items across and down from there, following the “golden triangle” people’s eyes tend to follow.
Stick to two colours: one for your main metric and one for the second, repeated throughout.
Line everything up. Symmetrical boxes of tables and charts let the eye move cleanly from one element to the next.

2. Turn the data into a named table

The example data is 11 months of sales transactions: customer, salesperson, shipping state, payment type, product, category, quantity, unit price, revenue and more, with each month added to the bottom. Click inside the data and press Ctrl+T to make it a table, then rename it Sales on the Table Design tab.

3. Build the first PivotTable and chart: sales by month

Go to Insert > PivotTable (the range shows as Sales) and place it on a new sheet called Monthly. Drag Order Date into Rows and keep only the Months grouping (in older versions, right-click a date and choose Group > Months). Drag Revenue into Values and format it with comma style and no decimals.

Insert a Line with Markers chart, then declutter it: delete the legend, gridlines and vertical axis, and hide the field buttons. Title it Sales by Month. Style the markers with no border, a dark red fill, a round bevel and size 8.

4. Copy the sheet to make the other charts

Hold Ctrl and drag the sheet tab to copy it. Every copy uses the same data source, which is what lets one slicer control them all later. Rearrange each copy’s PivotTable:

Distribution: Revenue in Rows, grouped from 0 to 4,000 in steps of 1,000, with Count of Revenue in Values. Show it as a bar chart titled Count of Transactions, with data labels and a 50% gap width.
Category: Category in Rows and Sum of Revenue, sorted largest to smallest, filtered to the top 5 (Value Filters > Top 10, changed to 5). Use centred white labels with the custom format #,##0,"K" to show thousands.
State: State in Rows, with Revenue shown as % of Grand Total, sorted, as a column chart titled Revenue by State.
Payment: Payment Type in Rows, showing how revenue comes in by debit, credit and cash.

5. Make the three data tables

Make three more copies without charts, for Salesperson, Product (top 10) and Customer. Each shows Sum of Quantity, Sum of Revenue, and Revenue again as % of Grand Total, sorted largest to smallest.

6. Bring the tables onto the dashboard

On the Dashboard sheet, link the first cell to each table (for example =Salesperson!A4 in B6). Copy it across the block, then Paste Special > Formulas (Ctrl+Alt+V).

Add an in-cell bar chart next to each table with =REPT("|",D6/MAX($D$6:$D$13)*100), set the font to Playbill so the bars pack tightly, and colour them to match your theme.

7. Place and align the charts

Copy each chart onto the dashboard and resize it into its box. Use the Format tab to give matching charts identical heights and widths, and Page Layout > Align (Left or Right) to line them up. Finally, remove each chart’s fill and border so they sit cleanly on the layout.

8. Add one slicer that controls everything

Click a PivotChart and go to PivotChart Analyze > Insert Slicer > State. Set it to 6 columns, turn off its header in Slicer Settings, and pick a style. Then right-click the slicer, choose Report Connections and tick every PivotTable. Because they all share the same data source, one click now filters the whole dashboard, and Ctrl+click selects several states.

9. Add next month’s data

Paste the new month’s rows as values directly under the last row of the Sales table, so the table expands to include them. Then go to PivotTable Analyze > Refresh All, and every table and chart updates.

Go further

My Excel Dashboard & Data Analysis Masterclass takes these techniques further, with 10 interactive dashboards built from scratch.