An Excel dashboard without pivot tables: SUMIFS, a summary sheet, and bars

Three sheets, one direction

Log — one row per thing that happened: a transaction, a task, a stock movement. Only inputs and the simplest per-row formulas. Setup — the lists: categories, people, the year, the as-of date. Dashboard — nothing but formulas that read the other two.

Data flows one way: Setup → Log → Dashboard. Nothing on the dashboard is typed, so it can’t drift from the log; nothing on the log refers to the dashboard, so you can sort, filter and insert rows without breaking a thing.

The one formula

=SUMIFS(Log!$E$6:$E$405,            what to add up
      Log!$C$6:$C$405, $A5,          this row's category (from Setup, so spelling can't differ)
      Log!$A$6:$A$405, ">="&$B$2,    from the date in B2
      Log!$A$6:$A$405, "<="&$B$3)    to the date in B3

That’s most of a dashboard. Categories down column A, one SUMIFS per row; a COUNTIFS beside it for how many; a share = row ÷ total. Bound the ranges ($6:$405) rather than whole columns — Google Sheets stays fast and the formula says where it ends.

Driven by a cell, not a dropdown on a pivot

The pivot’s filter is a cell here. Put the year in B2, the month in B3, or a person picked from a dropdown in B4, and every SUMIFS reads it:

B3:  first of the month, typed or =DATE(B2, B4, 1)
B5:  =EOMONTH(B3, 0)                          last day of that month
… criteria:  dates, ">="&$B$3, dates, "<="&$B$5

or a person:
B6:  dropdown from Setup!people
… criteria:  owners, $B$6

Change the cell and the whole sheet recalculates — no Refresh, no “PivotTable field list”. Our inventory dashboard is filtered by category this way; the project dashboard by owner.

The grid: category × status

A pivot’s row-and-column layout is a COUNTIFS with mixed references:

       B          C          D
4      Overdue    Today      Soon            ← headers are the status words
5  Work   =COUNTIFS(Tasks!$C$8:$C$157, $A5, Tasks!$G$8:$G$157, B$4)
6  Home   (copied down and across)

$A5 pins the column (the category), B$4 pins the row (the status). One formula, the whole grid. The same shape gives person × month, product × quarter, anything × anything.

Top, bottom, and “which one”

The questions a pivot answers by sorting are one lookup each:

Highest value:     =MAX(C5:C16)
Which category:    =INDEX(A5:A16, MATCH(MAX(C5:C16), C5:C16, 0))
Lowest non-zero:   =SUMPRODUCT(MIN(C5:C16 + (C5:C16=0)*1E9))       skip zeros
Second highest:    =LARGE(C5:C16, 2)
Rank of a row:     =COUNTIF($C$5:$C$16, ">"&C5) + 1

Ties go to the first match with INDEX/MATCH; if that matters, add the row number as a tiebreaker in a helper column.

Distinct counts

“How many customers this month” — the one thing a pivot does that SUMIFS can’t directly. Two portable ways:

=SUMPRODUCT((dates>=$B$3)*(dates<=$B$5) / COUNTIFS(customers, customers&"", dates, dates&""))
 each row contributes 1 ÷ (rows sharing its customer and date) — slow past a few thousand rows

or a helper column on the log:
F6: =IF(COUNTIFS($C$6:C6, C6, $A$6:A6, ">="&$B$3)=1, 1, 0)      1 on a customer's first row in range
then =SUM(F6:F405)

The helper column is faster and shows its work. Our workout log counts sessions (distinct dates) the first way because the log is short; a sales dashboard should use the second.

Bars before charts

A data bar on the value column turns the table into a chart without a chart object: Home → Conditional Formatting → Data Bars (Excel) or a colour scale / custom formula in Sheets. Set the minimum to 0 always, and a fixed maximum when the column is a percentage (1) or when two tables must be comparable. For anything else, MAX of the column is fine.

Add one real chart only when the shape matters — a trend over twelve months, income against expenses — and point it at a fixed summary range, never at the log. (There’s a separate guide on charts that update themselves.)

Status tiles

The numbers people read first — total, open, overdue, this month — belong at the top in big type, each one cell:

Open        =COUNTIF(status, "Open")
Overdue     =COUNTIF(status, "Overdue")                        red when >0 via conditional format
This month  =SUMIFS(amount, dates, ">="&$B$3, dates, "<="&$B$5)
Progress    =IF(total=0, 0, done/total)                        with a data bar, max fixed at 1

Guard every division with IF(denominator=0, 0, …); an empty log should show zeros, not #DIV/0! in the tile the boss reads.

Empty rows, zero not blank

Every formula on the dashboard must return a number, even when there’s no data: 0, hidden by the number format 0;-0;;@. A "" looks tidier and breaks the next SUM or AVERAGE that reads the cell. This is the rule every workbook we ship follows, and it’s the reason the dashboards recalculate in LibreOffice and a second engine without a single error cell.

What you give up, and don’t

No drag-and-drop regrouping — the dashboard answers the questions you designed it for. In exchange: it updates the moment a row is typed, it can be filtered by any cell, it’s readable (every number is a formula you can click), it opens identically in Excel 2016, Google Sheets, Numbers and LibreOffice, and the Formulas sheet can explain it in a page.