Google Sheets dashboards with pivot tables

A pivot dashboard separates raw data, pivot summaries, and presentation. The data sheet holds one clean table, the pivot sheets hold the calculations, and the dashboard sheet holds the charts and labels. When all three share a tab, a new pivot row pushes a chart aside and a formatting change can break the layout.

Pivots are cached summaries, not live formulas. They refresh when the source changes and the file recalculates, but a viewer can see the last saved state. If the dashboard is presented at a fixed time, refresh it beforehand and check a control total rather than assuming the numbers updated.

Use one header row with unique names, one record per row, and no merged cells, subtotal rows, or blank separators. Keep dates as dates and amounts as numbers. Text that looks numeric becomes a separate category or a blank measure.

A pivot range is fixed. Rows added below it are not included until the range is extended, and rows inserted inside it usually are. Decide how new data arrives: append inside a range with headroom, or extend the range as part of the refresh. Write the step down, because it is the most common cause of a stale dashboard.

Normalise categories before pivoting. North, north, and North with a trailing space become three rows. Use validation on the input sheet and TRIM in a staging formula. A pivot cannot repair inconsistent labels.

Rows are the things you compare: customer, region, product, month. Columns should be a small set of categories, because every extra column narrows the charts. Values are the measures. Filters set the scope, and a filter that changes the question should be visible, not hidden.

Group dates by month or quarter for trends and keep day-level detail in a separate pivot. Sort by the measure descending. Show totals deliberately: a grand total of a ratio is not the average of the rows, and a pivot invites that misreading.

Ratios need care. A calculated field stays inside the pivot but is harder to audit. A formula tile referencing fixed cells is easier to check but breaks when the pivot moves. Choose one approach per dashboard and document it.

Charts bound to a pivot range can resize when categories appear or disappear. Put charts on a separate dashboard sheet, give each a fixed block, and leave empty rows and columns around it. A chart placed directly below a pivot will be overlapped the first time the pivot grows.

Match the chart to the question: bar for comparison, line for trend, stacked bar for composition with few series. A pie chart with twelve slices is a table with extra steps. Label the unit and the period.

Keep KPI tiles fixed. Reference a cell you control, or calculate the tile with SUMIFS or QUERY independently of the pivot. If a tile and a pivot use different logic, reconcile them; two definitions will eventually disagree.

Protect the pivot and dashboard sheets and leave the input area editable. Use range protection when a script needs to write to a block. Protection prevents accidents; it does not hide data from anyone who can open the file.

There is no dependable scripted refresh for every pivot layout. If the dashboard must be current on a schedule, rebuild the summary with QUERY, which recalculates, or make refresh a named human step before distribution.

Distribute two formats: the live file for people who filter, and a PDF or values-only snapshot for people who need a fixed record. Emailing a copy of the live file creates a version that will drift.

A dashboard is ready when the data layer, the pivot, and the presentation each pass a simple test.