A Google Sheets expense dashboard is one tab that reads your transaction tab and redraws whenever you change a month cell. You need a Data tab, a month dropdown, three scorecard cells built with SUMIFS, two charts and a QUERY formula for the top-10 list. Column letters below are examples, so adjust them to your own sheet.
Set up the Data tab so the formulas stay simple
Keep every transaction on a tab called Data with headers in row 1: A Date, B Merchant, C Category, D Amount, E Type. Type is either Expense or Income, and Amount is always a positive number. Dates must be real dates, which means they sit on the right of the cell, not the left. Point formulas at rows 2 to 2000 rather than whole columns so the sheet stays quick.
How do you add a month selector?
On a new tab called Dashboard, type 01-Apr-2026 in H2 and =EDATE(H2,1) in H3, then fill down to H13 for an April to March year. Format H2:H13 as mmm yyyy. In B2 choose Data > Data validation, add a rule of type Dropdown (from a range), and select H2:H13. Every formula below treats B2 as the selected month.
Which formulas give spend, savings rate and top category?
| Scorecard | Cell | Formula |
|---|---|---|
| Spend | B4 | =SUMIFS(Data!$D$2:$D$2000, Data!$E$2:$E$2000, "Expense", Data!$A$2:$A$2000, ">="&$B$2, Data!$A$2:$A$2000, "<"&EDATE($B$2,1)) |
| Income | B5 | =SUMIFS(Data!$D$2:$D$2000, Data!$E$2:$E$2000, "Income", Data!$A$2:$A$2000, ">="&$B$2, Data!$A$2:$A$2000, "<"&EDATE($B$2,1)) |
| Savings rate | B6 | =IF(B5=0, 0, (B5-B4)/B5) formatted as a percentage |
| Top category | B7 | =A12, the first row of the category table built in the next section |
Say income for the month is ₹60,000 and spend is ₹42,000. The savings rate cell works out (60,000 - 42,000) / 60,000 = 0.30, shown as 30%. Make the four cells large, with a light fill, and they already read as scorecards.
How does QUERY build the category table and the top 10?
QUERY lets you write a small database request inside a cell. Put the first formula below in A11. It groups the selected month's expenses by category, largest first, and gives the spend column the label Spend.
- Category table, in A11: =QUERY(Data!$A$1:$E$2000, "select C, sum(D) where E = 'Expense' and A >= date '"&TEXT($B$2,"yyyy-mm-dd")&"' and A < date '"&TEXT(EDATE($B$2,1),"yyyy-mm-dd")&"' group by C order by sum(D) desc label sum(D) 'Spend'", 1)
- Ten biggest single expenses, in D11: =QUERY(Data!$A$1:$E$2000, "select A, B, C, D where E = 'Expense' and A >= date '"&TEXT($B$2,"yyyy-mm-dd")&"' and A < date '"&TEXT(EDATE($B$2,1),"yyyy-mm-dd")&"' order by D desc limit 10", 1)
The final 1 tells QUERY that row 1 of the range is a header. Leave the cells below and to the right of each formula empty, because the results spill into them. Dates inside a QUERY string must be written as date 'yyyy-mm-dd', which is why TEXT is used.
What two charts are worth adding?
- Category chart: select A11:B20, choose Insert > Chart, and pick a column or bar chart. It follows the month dropdown automatically because the QUERY output changes.
- Monthly trend: in K2 enter the same SUMIFS as the Spend scorecard but with H2 in place of $B$2, fill down to K13, then chart H2:H13 against K2:K13 as a column chart. This one ignores the dropdown on purpose, so you always see the whole year.
If you like, select B4 and use Insert > Chart > Scorecard chart for a bigger number with a change indicator. Turn off gridlines from View > Show to make the tab look like a page.
Before you rely on it
- Add new rows directly under the last row of Data, inside the 2000-row range.
- Use one spelling for each category, so the QUERY groups do not split.
- Extend the month list when the next financial year begins.
- Copy the sheet before changing formulas, so you can go back.
When a sheet stops being enough, for example because you are retyping statement rows every month, SpendixAI can import the statement files and show category reports without the formulas.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


