Spreadsheets

Build a spending dashboard in Google Sheets

Build a one-tab spending dashboard in Google Sheets with QUERY, a pivot table, SPARKLINE and charts, plus what to do when you paste in new data.

Editorial illustration for Build a spending dashboard in Google Sheets

A Google Sheets spending dashboard needs one Data tab and one Dashboard tab. On the Dashboard, use SUMIFS for the headline total, QUERY for the category and merchant tables, SPARKLINE for the twelve-month trend, and Insert, Chart on the QUERY output. Everything recalculates when you paste new rows into Data.

Set up the Data tab

Row 1 holds headers: Date, Merchant, Category, Amount. Each transaction is one row below. Dates must be real dates, which Sheets right-aligns. If a pasted statement shows dates the wrong way round, change File, Settings, Locale to India and paste again.

On the Dashboard tab, put the month's first date in B1, for example 01/11/2026.

The headline number

=SUMIFS(Data!D:D, Data!A:A, ">="&$B$1, Data!A:A, "<"&EDATE($B$1,1))

This adds every amount dated from the first of the month up to, but not including, the first of the next month.

Category and merchant tables with QUERY

QUERY takes the data range, a query written in a small SQL-like language, and the number of header rows. For spending by category in the chosen month:

=QUERY(Data!A:D, "select C, sum(D) where A >= date '"&TEXT($B$1,"yyyy-mm-dd")&"' and A < date '"&TEXT(EDATE($B$1,1),"yyyy-mm-dd")&"' group by C order by sum(D) desc label sum(D) ''", 1)

Dates inside a query must be written as date 'yyyy-mm-dd', which is why TEXT builds them. For the top five merchants, select B instead of C, group by B, and add limit 5 after the order by clause. The result spills down by itself.

A twelve-month trend with SPARKLINE

In F2:F13 list twelve month-start dates, using =EDATE(F2,1) to fill down. In G2 write =SUMIFS(Data!D:D, Data!A:A, ">="&F2, Data!A:A, "<"&EDATE(F2,1)) and fill down. Then in any cell:

=SPARKLINE(G2:G13, {"charttype","column"; "highcolor","red"})

It draws twelve tiny columns inside one cell, with the tallest in red. Leave out the options for a plain line. SPARKLINE is useful beside a KPI card, where a full chart would be too big.

Where do pivot tables and charts fit?

Part of the dashboardToolWhy
Total spent this monthSUMIFSOne number, no layout needed
Spending by categoryQUERY, then bar chartChosen month, sorted, chart-ready
Top merchantsQUERY with limit 5Short ranked list
Month by category gridPivot tableDrag-and-drop, good for exploring
Trend inside a cardSPARKLINETiny chart inside one cell

For the pivot, select the Data columns, choose Insert, then Pivot table, put Category in Rows and Date in Columns, then right-click a date and choose Create pivot date group, Year-Month. To chart the QUERY output, select its cells and choose Insert, then Chart. Make the chart range a few rows taller than the table, so extra categories fit.

What happens when I paste in new data?

  • SUMIFS, QUERY and SPARKLINE recalculate on their own, because they read whole columns.
  • Charts built on QUERY output update with it, as long as the chart range covers the rows the query can return.
  • Pivot tables refresh automatically, but only over the source range. Select whole columns, such as A:D, when you create one.
  • Pasting below the data but leaving a blank row is fine for formulas, yet it breaks a pivot whose source ends at the gap.

Leave the cells under each QUERY empty. The result spills downwards, and if anything sits in its way Sheets shows a #REF! error saying the array result was not expanded. Put tables side by side with a blank column between them rather than stacked tightly.

Keeping it up

Add a short note on the tab saying where data comes from and which month B1 shows. Share the sheet as view-only if others only need to read it, since it holds every transaction.

A sheet like this is good for one person and a few accounts. When pasting statements every month becomes the chore, SpendixAI can take the same statements as PDF, CSV or Excel uploads and give you reports and a dashboard without formulas.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports