Excel dashboard: from your first chart to keeping it updated

An Excel dashboard fits on one screen and answers a simple question: where do we stand? You build it in three stages. First, clean data in a table. Then a few well-chosen indicators. Finally, a display that can be read in ten seconds. This guide follows those steps with the menu names of English-language Excel, as they appear on Microsoft Support.

To follow along in a real file, download our free Excel dashboard template. It has two sheets: ‘Dashboard’, with the indicators and their formulas, and ‘Sales’, with 120 sample sales. It contains no PivotTable and no chart: you’ll add them by following the steps below.

What is an Excel dashboard?

An Excel dashboard is a sheet that summarises raw data into a few indicators, charts and filters. It doesn’t hold the data itself: it reads it from a source table, then calculates and displays it. Its purpose is to let you make decisions without opening the data sheet or recalculating anything by hand.

A common mistake is to put the data, the calculations and the charts in the same place. The file works for the first month. Then every new row shifts a chart or breaks a formula. It’s better to keep three layers apart:

This way you can add a hundred rows of sales without touching the formulas: they read the whole Sales table.

How should you prepare the data?

A dashboard’s data must be laid out as a list: one row per event (a sale, an order, a job), one column per piece of information, and a single header row. Then turn that range into an Excel table with Ctrl+T. Any formula that refers to the table will pick up every row you add.

The rules of a clean data sheet

Microsoft sets out conditions for analysing data with a PivotTable. You need columns with a single header row, and no blank rows or columns (Microsoft Support, ‘Create a PivotTable to analyse worksheet data’). The same page advises against mixing data types in one column. In practice, check three things before you create the table:

• No blank rows or columns in the middle of the data.
• One data type per column: real dates in ‘Date’, plain numbers with no unit in ‘Amount (excl. VAT)’. Type 1250, not ‘€1,250 excl. VAT’; the unit belongs in the column name.
• Values always entered the same way: ‘Sarah Jones’ and ‘S. Jones’ count as two sales reps.

Format the data as a table and name it

Click in a cell of the data, then press Ctrl+T or choose Insert, Table. In the Create Table dialog, confirm the range and check that ‘My table has headers’ is ticked (Microsoft Support, ‘Insert a table’). The same page mentions another route: Home, Format as Table, then pick a style.

Next, give the table a meaningful name. Select a cell in the table and the Table Design tab appears on the ribbon. Type the name in the Table Name box, for example ‘Sales’, then press Enter.

That name changes everything that follows. Instead of writing =SUM(F2:F121), you write =SUM(Sales[[Amount (excl. VAT)]]). Microsoft calls this a structured reference, and it adjusts automatically when you add rows to the table (Microsoft Support, ‘Using structured references with Excel tables’). No more stretching ranges by hand every month.

Why the double brackets? The same page explains that a column header containing special characters, such as a full stop, must be enclosed in a second pair of brackets. ‘Amount (excl. VAT)’ contains one, so it’s written Sales[[Amount (excl. VAT)]].

The template follows this rule. The ‘Sales’ sheet holds a table named Sales with six columns: Date, Customer, Sales rep, Product, Quantity, Amount (excl. VAT). Its 120 sample rows are dated from January to October 2026.

Which indicators should you choose?

A readable dashboard shows four to six key performance indicators (KPIs), not twenty. Choose the ones that prompt action when they move. For sales tracking, total revenue, number of sales, average order value, this month’s figure, the monthly trend and the split by sales rep cover most needs.

A useful test: for each indicator, ask yourself what you would do if it fell by 20%. If the answer is ‘nothing’, it doesn’t belong at the top of the page. Here are the template’s indicators and where they sit on the ‘Dashboard’ sheet.

The labels of the first four indicators are in D2:D5, next to their formulas. The same principles apply to other lines of business. A workshop would track late orders and the scrap rate instead. A stockroom would track items below their reorder point, as in our guide to inventory management in Excel.

How do you calculate KPIs with SUMIFS and COUNTIFS?

SUMIFS adds up a column, keeping only the rows that meet one or more conditions; COUNTIFS counts those rows. These two functions cover almost every indicator on a dashboard. Their advantage is that they’re formulas: they recalculate as soon as a value changes, with no refresh needed.

SUMIFS syntax

The syntax is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …), with up to 127 range and criteria pairs (Microsoft Support, ‘SUMIFS function’). Microsoft flags the classic trap: the range to add up is the first argument of SUMIFS, but the third of SUMIF.

In the template, the ‘Revenue by sales rep’ heading is in A18, the column headers in A19:C19 and the sales reps’ names in A20:A23. The revenue of the rep named in A20 is written like this, in B20:

=SUMIFS(Sales[[Amount (excl. VAT)]],Sales[Sales rep],A20)

The formula is copied down to B23, one row per sales rep. To add a condition on the product, add a ,Sales[Product], pair followed by the product name in double quotes.

Revenue by month, without a ‘Month’ column

Many files add a ‘Month’ column to the data sheet so they can total by month. There’s no need. The template puts the first day of each month of 2026 in A6:A17, under the headers in A5:B5. In B6, two conditions frame the dates:

=SUMIFS(Sales[[Amount (excl. VAT)]],Sales[Date],">="&A6,Sales[Date],"<="&EOMONTH(A6,0))

EOMONTH(A6,0) returns the last day of the month of the date in A6. The formula therefore adds up the sales from the 1st to the last day of the month. It’s copied down to B17. As the sample data stops in October, the November and December rows show 0 until you add sales.

Revenue this month

The same principle, applied to today’s date, gives an indicator in E5 that changes on its own on the 1st of every month:

=SUMIFS(Sales[[Amount (excl. VAT)]],Sales[Date],">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),Sales[Date],"<="&EOMONTH(TODAY(),0))

Note that from November 2026, this cell shows 0 in the template, as there are no sample sales for the month. It fills up as soon as you enter your own sales.

Counting with COUNTIFS

COUNTIFS works the same way, without a column to add up. Microsoft explains that it counts the number of times all the criteria are met, again with up to 127 pairs (Microsoft Support, ‘COUNTIFS function’). In C20, the rep’s number of sales is =COUNTIFS(Sales[Sales rep],A20). For their average order value, enter =B20/C20 in D20.

PivotTable and chart: the interactive part

A PivotTable summarises a table with a few drags and drops, without a single formula: revenue by product, number of sales by customer. Microsoft describes it as a tool that can calculate, summarise and analyse data. It’s what will drive the chart and the slicers. The template doesn’t include one, so create it in five steps.

Why start from the Sales table? The Microsoft page quoted above explains: rows added to a table are included in the PivotTable when you refresh.

1. Click in the Sales table.
2. Choose Insert, then PivotTable.
3. Check that the source is ‘Sales’. Choose Existing Worksheet, then cell G2 of the ‘Dashboard’ sheet, and click OK.
4. In the field list, drag ‘Product’ into the Rows area.
5. Drag ‘Amount (excl. VAT)’ into the Values area. Microsoft notes that values are shown as a sum by default.

You now have revenue by product, the one angle that the template’s formulas don’t cover.

Formulas or PivotTable: which should you choose?

The two tools complement each other. Formulas are precise and always up to date; a PivotTable is quick to build and filters in one click.

The template follows this split: the indicators are formulas, and the filterable detail will be your PivotTable.

Add a PivotChart

A PivotChart is a chart linked to a PivotTable. Click in the PivotTable, choose Insert, PivotChart, then select a chart (Microsoft Support, ‘Create a PivotChart’). On a Mac, the same page says you must create the PivotTable first. Only four chart types are supported there: column, line, pie and radar.

To compare products, a column or bar chart is enough: the gap is clear at a glance. Avoid 3D effects, which distort proportions. Place the chart below the PivotTable, to the right of the indicators.

Slicers and timelines: filtering in one click

Slicers are filter buttons placed on the sheet; a timeline is a date slider. Connected to several PivotTables, they filter every chart that depends on them in a single click: one sales rep, one product, one quarter. That’s what makes an Excel dashboard interactive.

Insert a slicer

Click in the PivotTable, then on the Insert tab, select Slicer. In the Insert Slicers dialog, tick ‘Sales rep’ and ‘Product’, then click OK (Microsoft Support, ‘Use slicers to filter data’). Place the slicers above the PivotTable.

To make a slicer control another PivotTable, select it, then go to the Slicer tab, Report Connections, and tick the PivotTables concerned. Microsoft sets one condition: the PivotTables must share the same data source. So create them all from the Sales table.

Insert a timeline

To filter by period, use a timeline. Click in the PivotTable to display the PivotTable Tools, then choose Analyse, Insert Timeline, and tick ‘Date’. It filters by years, quarters, months or days (Microsoft Support, ‘Create a PivotTable timeline to filter dates’). A single timeline can filter several PivotTables with the same source, via Options, Report Connections.

One limit to bear in mind: a slicer or a timeline only filters the PivotTables connected to it. The template’s SUMIFS formulas still read the whole Sales table. Show the period under each indicator (‘2026’) to avoid misunderstandings.

Laying out the dashboard on one screen

A dashboard reads like a newspaper page: the most important item top left, the detail at the bottom. Put the four key indicators as tiles in a row, the trend chart in the centre, the breakdown to the right or below, and the filters at the top. The whole thing should fit on one screen without scrolling.

A few layout rules make all the difference:

• Tiles that state their period (‘2026’, ‘November’), with the figure in large type.
• One accent colour, not five. Grey for context, colour for what should catch the eye.
• Rounded numbers: ‘€48.2k’ reads faster than ‘€48,213.57’ in a tile.
• An ‘Updated on’ line with the date of the last refresh.

How do you keep the file up to date?

SUMIFS and COUNTIFS formulas update on their own as soon as a row is added to the Sales table. PivotTables and their charts don’t: you have to choose Data, Refresh All, or press Ctrl+Alt+F5. Skip this step and the PivotTables show figures that lag behind the formulas.

Microsoft documents both shortcuts: Ctrl+Alt+F5 refreshes all the data in the workbook, Alt+F5 only the selection (Microsoft Support, ‘Refresh an external data connection in Excel’). To avoid forgetting, set up each PivotTable: PivotTable Analyse, Options, Data tab, ‘Refresh data when opening the file’.

That leaves the real question: where do the new rows come from? Often, someone exports a file from the invoicing software or the CRM, then pastes it into the ‘Sales’ sheet. Here is the typical cycle, and what can go wrong each time:

Excel can take over part of this work when the data arrives through a connection, such as a Power Query query. The same Microsoft page describes two settings in the connection properties, on the Usage tab. The first refreshes when the file opens, the second every N minutes. You still need an accessible source, and someone to maintain the query when the export changes.

How do you share an Excel dashboard?

To work on the same workbook with others, Excel offers co-authoring: you save the file to OneDrive or SharePoint Online, then share it with the Share button. It requires a Microsoft 365 subscription; someone without a compatible version can edit in the browser. Otherwise, you’re left with email, one copy per recipient.

Microsoft sets out the requirements (Microsoft Support, ‘Collaborate on Excel workbooks at the same time with co-authoring’). The file must be in .xlsx, .xlsm or .xlsb format, on OneDrive, OneDrive for Business or SharePoint Online. On-premises SharePoint sites aren’t supported. If someone opens the file in a version without co-authoring, the others see a ‘File Locked’ message.

By default, every recipient can edit. For a reader who only needs to look, untick ‘Can edit’ when you share. To find out who changed what, use Review, Show Changes, which covers the last 365 days (Microsoft Support, ‘Show changes that were made in a workbook’).

One point is often underestimated: sharing the workbook means sharing all its sheets. The manager who was only meant to see four tiles can also open every sale on the ‘Sales’ sheet.

Where does an Excel dashboard reach its limits?

Excel is a very good tool for building a dashboard on your own, from data you control. Its limits appear when several people enter data, others want to view it, and the data comes from elsewhere. At that stage, the problem is no longer the formula: it’s the organisation around the file.

An Excel dashboard is often the first prototype of a team tool. It proves which figures matter and in what form. It’s the dashboard’s success that exposes its limits, not its failure. Here are the signs that it’s time to move on:

If you’re the only one filling in the file and reading it, none of this applies to you: stick with Excel. If two rows of this table describe your situation, the rest of this article is for you.

Moving to a dashboard that updates itself

A shared dashboard reads shared data directly, rather than a copy pasted into a file. Every entry, through a form or in the base, shows up in the figures and charts. There’s no file to send any more: readers open a link and see the current state.

That’s the principle of a no-code database such as Rangee, which we make. Sales live in a table that several editors fill in. The dashboard is an interface built from blocks (key figures, charts, lists) on top of that table. Each reader sees only the interface pages shared with them.

Start from your existing Excel file

The work you did in Excel isn’t lost. Rangee’s smart import reads an Excel file and turns it into a base and an interface suited to how you use it: dashboards, lists, records. The AI model receives only the file’s profile: column names, types and at most ten sample values. It never receives the whole file, and it runs in the European Union.

With our template, the ‘Sales’ sheet becomes a Sales table with six fields. The 120 sample rows fit within the Free plan, which is limited to 500 records per base (see pricing).

The aggregation field, the cross-table equivalent of SUMIFS

In the Excel template, revenue by sales rep is a column of SUMIFS formulas, one per name. In Rangee, the same idea takes the form of an aggregation field. In a Sales reps table, a ‘Revenue 2026’ field adds up the amounts from the Sales table. It keeps only that rep’s sales dated 2026.

The aggregation field calculates totals, averages or counts across two tables, with no junction table and no helper column. It saves you the ‘Month’ or ‘Year’ tables you often have to create to group data. The interface’s charts and key figures, for their part, group directly by period (year, quarter, month, week, day) or by value.

What changes compared with Excel

On price, the Free plan doesn’t expire and gives access to the same features as the other plans, with lower volumes: one base, 500 records, five interfaces. The Pro plan costs €5 per editor per month, and only editors are billed. To put these prices in context, see our comparison of no-code databases and our survey of per-user pricing.

Rangee doesn’t replace Excel for everything. For a one-off analysis, a simulation or a financial calculation, the spreadsheet remains more flexible. Rangee suits a dashboard that several people feed and others read.

Excel or a database: how do you choose?

Stay with Excel if you’re the only one entering data, if the data changes once a month and if the dashboard is read in meetings. Move to a database if several people enter data, if others view it without editing, or if you copy data across every week.

One last criterion, often overlooked: the time spent updating. Note how many minutes the export, the pasting and the refresh take each month, then compare that time with the price of a tool.

Either way, start with Excel. The free dashboard template saves you the first hour: sample data, indicators and formulas are ready. Add the PivotTable, a chart and a slicer by following the steps above. You’ll soon know whether your dashboard should stay a file.

The day it needs to become a team tool, import that same file into Rangee: the base and the dashboard are built from your columns. Try Rangee for free: the Free plan doesn’t expire.