Dashboard Secrets · ~2 min read

Build a Self-Updating Dashboard in Excel

Turn a list of sales into a dashboard with a total, a region summary, in-cell bars and a top seller, all updating by themselves.

Video link coming soon
What you will learn

Step by step

1. The goal

A table of sales numbers is accurate, but nobody wants to read it. Here is how to turn it into a dashboard that keeps itself up to date.

Build a Self-Updating Dashboard in Excel: The goal
The goal

2. Make a Table

The whole dashboard rests on one habit: turn the list into a Table first. Select the data and press Control and T.

Excel asks whether the table has headers. It does, so press Enter.

A Table grows by itself when you add rows, and every formula that points at it grows too. That is the secret behind a dashboard that never needs repairs.

Shortcut: Ctrl + T

Build a Self-Updating Dashboard in Excel: Make a Table
Make a Table

3. The first KPI

Now the first number everyone asks for: total revenue. In E2, type the label Total Revenue.

In F2, type equals SUM, open the bracket, and click through to the Sales column of the Table. Excel writes the column name for you.

Fifteen thousand six hundred. Because the formula uses the column name instead of cell addresses, it will include every future row.

=SUM(Table1[Sales])
Build a Self-Updating Dashboard in Excel: The first KPI
The first KPI

4. Sales by region

Next, sales by region. In E4, type Region, then Tab, and Sales.

Instead of typing the region names, let a formula find them. In E5, type equals UNIQUE of the Region column.

One formula, and the list of regions spills down by itself: North and South. If a new region ever appears in the data, it joins the list.

Beside it, in F5, add up the sales for each region. Type equals SUMIFS, the Sales column, the Region column, then E5 followed by a hash sign.

The hash sign means the whole spilled list. So this one formula fills both cells: six thousand five hundred for North, nine thousand one hundred for South.

=UNIQUE(Table1[Region])
=SUMIFS(Table1[Sales],Table1[Region],E5#)
Build a Self-Updating Dashboard in Excel: Sales by region
Sales by region

5. In-cell bars

Dashboards need visuals, and you do not need a chart for that. In G4, type Trend. In G5, repeat a bar character with REPT, one bar for every three hundred in sales.

Copy the formula down by dragging the fill handle, and every region gets a bar that is perfectly in proportion to its sales.

=REPT("|",F5/300)
Build a Self-Updating Dashboard in Excel: In-cell bars
In-cell bars

6. Top performer

One more insight: who is the top seller? In E8, type Top Rep. In F8, nest MAX inside XLOOKUP, so Excel finds the biggest sale and returns the name next to it.

=XLOOKUP(MAX(Table1[Sales]),Table1[Sales],Table1[Rep])
Build a Self-Updating Dashboard in Excel: Top performer
Top performer

7. The test

Liam, with a single sale of four thousand one hundred. Now the real test: add a new row under the Table.

Type Liam, North, and eight hundred, pressing Tab between the cells.

The Table swallows the new row, and the dashboard follows: the total is sixteen thousand four hundred, and North is now seven thousand three hundred. Nothing was edited by hand.

Build a Self-Updating Dashboard in Excel: The test
The test

8. The spill trap

Here is the trap. Dynamic arrays need empty room to spill. Type anything in E6, just under North, and watch E5.

A spill error. Excel will not overwrite your cell. The fix is simple: delete the blocking entry, and the list returns by itself.

Which number would you add to this dashboard first? Write it in the comments, and I will build it.

Build a Self-Updating Dashboard in Excel: The spill trap
The spill trap
What do you want to learn next? Tell us in the comments on YouTube: your requests decide the next tutorials.

← Previous tutorial: Clean Messy Data with Copilot in Excel
Next tutorial: Excel Slicers: Why Your SUM Total Never Changes →