A financial-year selector is a dropdown in one cell that holds a label such as 2025-26. Every total on the dashboard reads the start and end date calculated from that cell, so changing the dropdown changes every number. You need a financial-year column in your data, UNIQUE to build the list (Excel 365, Excel 2021 or Google Sheets), and one SUMIFS pattern reused everywhere.
Which helper column do you add to the data?
Assume your Data sheet has dates in column A, descriptions in B, amounts in C and categories in D. In column F, give each row its financial year label:
=IF(A2="", "", YEAR(A2)-(MONTH(A2)<4)&"-"&RIGHT(YEAR(A2)-(MONTH(A2)<4)+1,2))
A date in August 2025 shows 2025-26, and a date in February 2026 shows 2025-26 as well. Fill it down to row 500 or convert the data into a table so it extends itself.
How do you build the year list with UNIQUE?
On a Lists sheet, in A2, type:
=SORT(UNIQUE(FILTER(Data!$F$2:$F$500, Data!$F$2:$F$500<>"")), 1, -1)
FILTER removes blank rows, UNIQUE removes repeats and SORT with -1 puts the newest year first. In Excel the result spills down automatically. In Google Sheets it does the same. On Excel 2019 or earlier there is no UNIQUE, so type the years yourself, one per row.
How do you add the dropdown?
- Pick the cell for the selector, say Dashboard!B1.
- Excel: Data, Data Validation, Allow List, and for Source type =Lists!$A$2:$A$10. Blank cells beyond the real list are fine to leave in the range, or use the spill reference =Lists!$A$2# in Excel.
- Google Sheets: Data, Data validation, Add rule, Dropdown (from a range), and choose Lists!A2:A10.
- Pick a year in B1. It should say something like 2025-26.
How do the start and end dates come from the label?
Put them in cells that every formula can point to:
- B2, start date: =DATE(VALUE(LEFT(B1,4)), 4, 1)
- B3, end date: =DATE(VALUE(LEFT(B1,4))+1, 3, 31)
For 2025-26 these give 1 April 2025 and 31 March 2026. LEFT takes the first four characters, VALUE turns them into a number, and DATE builds the real date.
What does a dashboard SUMIFS look like?
A category total for the chosen year is:
=SUMIFS(Data!$C$2:$C$500, Data!$D$2:$D$500, $A6, Data!$A$2:$A$500, ">="&$B$2, Data!$A$2:$A$500, "<="&$B$3)
Here A6 holds the category name. The same two date criteria go on every formula, so a single change of year updates every table and chart. A total for the whole year uses the same formula without the category pair.
| Cell | Holds | Example |
|---|---|---|
| B1 | Chosen year | 2025-26 |
| B2 | Start date | 1 April 2025 |
| B3 | End date | 31 March 2026 |
| A6 | Category | Groceries |
What would you do next?
- Add a second selector for category, with an All choice, and use a wildcard criterion "*" when All is picked.
- Add a chart that points at the category totals so it redraws with the year.
- Protect the formula cells so a stray typing does not break them.
How do you make a year-on-year line next to it?
Add a second selector in B4 for the comparison year and a second pair of start and end dates, B5 and B6, built with the same DATE and LEFT formulas. Each dashboard table can then have two columns using the same SUMIFS, one pointing at B2 and B3 and the other at B5 and B6. A difference column between them turns the dashboard into a quick two-year comparison without any extra data.
Test the selector by choosing the oldest year, the newest year and a year with no rows. The first two should match totals you can verify by filtering the Data sheet by hand, and the empty year should show zeros rather than errors.
When a sheet like this starts to need monthly paste-ins from every bank, SpendixAI can take the statements as uploads and give you reports and a CSV download; the dropdown design above still works on the exported file.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


