Why should the dashboard sheet contain no formulas?

So the layout can be rearranged without breaking anything. Once logic lives inside a presentation sheet, moving a tile becomes a debugging exercise, and the person who wants to move it is usually not the person who built it.

QUERY or pivot tables in Google Sheets?

QUERY for anything that should stay current, because it recalculates automatically where a pivot table has to be refreshed. Pivot tables remain better for interactive exploration where someone is dragging fields around.

How do I make the dashboard refresh by itself?

In Google Sheets, an Apps Script time-driven trigger repopulates the data tab on a schedule. In Excel, a Power Query connection refreshes on open. Either is a short automation project .

Building Interactive Dashboards in Google Sheets

Three layers, always

Almost every dashboard we are asked to repair has the same fault: data, calculations and presentation share the same cells. Someone formats a range and breaks a formula; someone inserts a row and the chart silently loses a series.

The fix is structural and costs nothing to adopt at the start.

The three layers and the rule that governs each.
Sheet	Contains	Rule
Data	The raw table, appended to and never edited	No formatting, no formulas, no manual edits.
Calc	Named ranges and every measure the dashboard reads	No presentation. Nobody looks at this sheet.
Dashboard	Tiles, charts and control cells	No logic. It only references Calc.

QUERY does most of the work

QUERY is the function that makes Sheets dashboards different from Excel ones. It runs a small SQL-like statement over a range and recalculates automatically, which a pivot table does not.

=QUERY(Data!A:H,
  "select B, sum(F) where D = 'EU' group by B order by sum(F) desc", 1)

That single formula replaces a pivot table someone has to remember to refresh. The column letters refer to the range you pass, not to the sheet, which is the detail that trips people up first.

Control cells instead of filters

Make the period and the segment into cells on the dashboard, then have every measure read them. Changing one cell updates the entire dashboard, and — unlike a filter — the state is visible and shared with everyone else in the file.

=QUERY(Data!A:H,
  "select B, sum(F) where D = '" & Dashboard!B2 & "'
   and A >= date '" & TEXT(Dashboard!B3,"yyyy-mm-dd") & "'
   group by B", 1)

Note the date keyword and the explicit format: QUERY compares dates as literals, and this is where most date filters in Sheets quietly return nothing.

Back each control cell with data validation pointing at a spilled distinct list, so new categories appear on their own and nobody can type a value that matches nothing:

=SORT(UNIQUE(FILTER(Data!D:D, Data!D:D <> "")))

Guard every denominator

A dashboard filtered to a segment with no rows will divide by zero, and one #DIV/0! propagates into every chart that touches the column.

=IF([@Sessions] = 0, 0, [@Conversions] / [@Sessions])

Guard the specific failure rather than wrapping the whole formula in IFERROR, which also hides the errors you needed to see.

Keep it fast

Sheets recalculates the whole dependency chain, so a dashboard slows down for reasons that are usually avoidable:

Reference A2:A rather than A:A where you can — whole-column references force evaluation over a million rows.
Compute a measure once on the Calc sheet and reference it, rather than repeating the same QUERY in four tiles.
Avoid chains of IMPORTRANGE: each one is a network call, and they refresh on their own schedule.
Past roughly 100,000 rows carrying formulas, move the data to BigQuery and connect it rather than adding more sheets.

Then make it refresh itself

A dashboard someone has to update by hand gets updated until they are on holiday. An Apps Script time-driven trigger can repopulate the Data sheet overnight and email the summary, running in Google's infrastructure whether or not anyone is logged in.

Building interactive dashboards in Google Sheets