How to Build an Excel Dashboard Without Pivot Tables (Formulas Only)

PivotTables are the usual way to build an Excel dashboard, but they're not the only way, and for a dashboard you check every day they're not always the best. They need refreshing, they change shape when the data does, and they decide part of your layout for you.

This guide shows how to build an Excel dashboard without pivot tables, using formulas such as SUMIFS, COUNTIFS and XLOOKUP, plus drop-down lists for filtering. Formula dashboards update the moment you add data, and every number sits exactly where you put it. It's how we build our niche dashboard templates.

PivotTables vs formulas for a dashboard

The short version: PivotTables are best for exploring data and building quickly. Formulas are better for a finished dashboard that people check every day, especially one that compares actuals with targets.

The formulas you need

A handful of functions does nearly all the work:

If your dashboard has to work in Excel 2016 or 2019, stick to SUMIFS, COUNTIFS, AVERAGEIFS, INDEX, MATCH and SUBTOTAL.

Build a formula-driven dashboard in 6 steps

The example is a small online shop with an Excel table called Sales and these columns: Date, Order ID, Region, Channel, Product and Amount.

Step 1: Prepare the data table

Put the data in an Excel table (Insert > Table) so every formula can refer to whole columns, like Sales[Amount], that grow as you add rows. Our guide on how to create a dashboard in Excel covers the data setup in detail. Then add one helper column called Month that turns each date into the first day of its month:

=EOMONTH([@Date],-1)+1

Format the column as mmm yyyy so it shows months rather than date serial numbers. Monthly totals become a simple match on that column, which is easier to read and faster than testing date ranges in every formula.

Step 2: Add the filter controls

On the Dashboard sheet, set up two input cells that every formula will read:

• B2, the month to show. Type the first day of a month (for example 10/1/2026) and format the cell as mmm yyyy.
• B3, the region. Select the cell, choose Data > Data Validation, set Allow to List and type All,North,South,East,West in the Source box.

These two cells do the job slicers do on a PivotTable dashboard.

Step 3: Calculate the KPIs

On a Calc sheet, build each headline number. The IF($B$3="All","*",$B$3) part means "any region" when the drop-down says All, because * is a wildcard that matches any text. (It doesn't match blank cells, so make sure every row has a region.)

Revenue for the selected month and region:

=SUMIFS(Sales[Amount],Sales[Month],Dashboard!$B$2,Sales[Region],IF(Dashboard!$B$3="All","*",Dashboard!$B$3))

Orders (this counts rows, so it assumes one row per order):

=COUNTIFS(Sales[Month],Dashboard!$B$2,Sales[Region],IF(Dashboard!$B$3="All","*",Dashboard!$B$3))

Revenue for the month before, for comparison:

=SUMIFS(Sales[Amount],Sales[Month],EDATE(Dashboard!$B$2,-1),Sales[Region],IF(Dashboard!$B$3="All","*",Dashboard!$B$3))

With revenue in C2 and orders in C3, average order value is revenue divided by orders, wrapped in IFERROR so an empty month shows 0 rather than an error: =IFERROR(C2/C3,0).

Step 4: Build the trend

For a 12-month trend ending in the selected month, list the months down column A of the Calc sheet. In A10:

=EDATE(Dashboard!$B$2,-11)

Then in A11, =EDATE(A10,1), filled down to A21, and format A10:A21 as mmm yyyy (EDATE returns a plain date serial number). Next to each month, in B10, filled down:

=SUMIFS(Sales[Amount],Sales[Month],A10,Sales[Region],IF(Dashboard!$B$3="All","*",Dashboard!$B$3))

Change the month or region on the dashboard and all 12 values move with it.

Step 5: Rank the top products

In Microsoft 365 or Excel 2021 and later, three formulas build a ranked list that grows when you add a new product. In E10, a list of every product:

=SORT(UNIQUE(Sales[Product]))

In F10, the revenue for each one (the # means "the whole list that spills from E10"):

=SUMIFS(Sales[Amount],Sales[Product],E10#)

And the top five, ranked by revenue:

=INDEX(SORTBY(E10#,F10#,-1),SEQUENCE(5))

That returns the names only, so put their revenue in the next column with =LARGE(F10#,SEQUENCE(5)).

In Microsoft 365, GROUPBY can do the whole job in one formula. This returns every product with its revenue, sorted from highest to lowest, with no header or total row:

=GROUPBY(Sales[Product],Sales[Amount],SUM,0,0,-2)

In Excel 2016 or 2019, use LARGE to find the top values and INDEX with MATCH to fetch their names. Watch for ties, which MATCH will only find once.

Step 6: Chart the Calc ranges and lay out the dashboard

Select the 12-month range (A10:B21) and choose Insert > Recommended Charts for a line or column chart. Chart the top-products list as a bar chart. Move the charts to the dashboard, link KPI cards to the Calc cells, and put the month and region inputs at the top where people will find them.

That's it: no refresh, no PivotTables changing shape, and every number in the cell you designed for it.

Keep it fast

Formula dashboards stay quick with a few habits:

• Reference table columns or fixed-size ranges, not whole columns, in functions such as SUMPRODUCT that process every cell they're given.
• Use helper columns like Month instead of repeating the same date calculation inside hundreds of formulas.
• Avoid volatile functions such as OFFSET and INDIRECT where you can. They recalculate every time anything in the workbook changes.
• Calculate once, reuse often. Work out a number on the Calc sheet once and point the dashboard at that cell, rather than repeating the formula in several places.

When a PivotTable is still the better choice

Formulas aren't always the answer. A PivotTable is better when:

• You're exploring the data and don't yet know which questions matter.
• The data runs to hundreds of thousands of rows, or comes from several related tables in the Data Model.
• People need to regroup the data themselves, by any field they like.
• You want slicers and timelines that filter everything with no extra formulas. See our guide to building an interactive Excel dashboard with slicers.

Many good dashboards mix both: PivotTables for the breakdowns, formulas for the KPI cards and target comparisons. If you go with PivotTables, our guide to dashboards that update automatically shows how to keep them refreshed.

Frequently asked questions

Can you make a dashboard in Excel without pivot tables?

Yes. SUMIFS, COUNTIFS and AVERAGEIFS can calculate the totals, counts and averages a dashboard usually needs, and drop-down lists replace slicers for filtering. Charts built on those formulas update as soon as the data changes.

What can I use instead of a PivotTable in Excel?

For totals and counts, use SUMIFS and COUNTIFS. For summary tables that build themselves, use UNIQUE with SUMIFS in Microsoft 365 or Excel 2021 and later, or GROUPBY and PIVOTBY in Microsoft 365.

Are formulas slower than PivotTables?

For typical small-business data, a few thousand to tens of thousands of rows, a well-built formula dashboard is quick. With hundreds of thousands of rows, PivotTables are usually faster, because they calculate once per refresh rather than every time something changes.

Will a formula dashboard work in Excel 2016?

Yes, if you use SUMIFS, COUNTIFS, AVERAGEIFS, INDEX, MATCH and SUBTOTAL. Functions such as XLOOKUP, FILTER, UNIQUE and SORT need Microsoft 365 or Excel 2021 and later, and GROUPBY and PIVOTBY need Microsoft 365.

How do I make a formula dashboard interactive?

Use drop-down lists as inputs and have every formula read them. Or add a slicer to your data table and total it with SUBTOTAL, which ignores rows the slicer hides.

Sources

• Microsoft Support: SUBTOTAL function
• Microsoft Support: UNIQUE function
• Microsoft Support: XLOOKUP function
• Microsoft Support: Excel functions (alphabetical)
• Microsoft Support: Create a drop-down list