To make one Excel dashboard serve every month, put a list of months on a helper sheet, turn one dashboard cell into a data-validation dropdown from that list, and point every SUMIFS and chart range at the month it picks. Change the dropdown and the cards and charts recalculate, so you never copy the sheet for a new month.
Build the month list
On a sheet called Lists, put the first month's start date in B2, say 1 April 2026, and use =EDATE(B2,1) in B3, filled down to B13. In A2 write =TEXT(B2,"mmm yyyy") and fill down, so A holds labels such as Apr 2026 and B holds the true dates. Select A2:A13 and give it the name MonthList using the Name box beside the formula bar.
Create the dropdown
- On the Dashboard sheet, click B1, the cell that will hold the month.
- Choose Data, then Data Validation.
- Under Allow, pick List. In Source type =MonthList.
- Keep In-cell dropdown ticked, and on the Error Alert tab leave the style as Stop so nobody can type a month that is not on the list.
- Pick any month from the dropdown once, so the cell is not empty.
Use text labels in the list, not the dates themselves. A dropdown built from date cells can behave unpredictably across regional settings and cell formats. Text labels are what you see and exactly what the formula looks up.
Turn the chosen month into dates
The formulas need a start date and an end date, not a label. On a Calc sheet, put in B2:
=INDEX(Lists!$B$2:$B$13, MATCH(Dashboard!$B$1, Lists!$A$2:$A$13, 0))
MATCH finds the position of the label you chose, and INDEX returns the real date from the same position in column B. B2 is now the month's first day. The first day of the next month is =EDATE(Calc!$B$2,1), and it is used as an upper bound below.
Which formulas follow the dropdown?
| Visual | Formula or range | Reads from |
|---|---|---|
| Total spent card | =SUMIFS(Data[Amount], Data[Date], ">="&Calc!$B$2, Data[Date], "<"&EDATE(Calc!$B$2,1)) | Calc!B2 and the Data table |
| Category bar block | Same formula with Data[Category], A5 added before the dates | Category names in column A |
| Previous month card | Same formula using EDATE(Calc!$B$2,-1) as the start | Calc!B2 |
| Chart title | ="Spending in "&Dashboard!B1, linked to the title | Dashboard!B1 |
For the category block, say categories sit in A5:A12 on the Dashboard and the formula in B5 is =SUMIFS(Data[Amount], Data[Category], A5, Data[Date], ">="&Calc!$B$2, Data[Date], "<"&EDATE(Calc!$B$2,1)). Point the bar chart at A5:B12. It never reads the Data sheet directly, so when B1 changes, the block changes and the chart follows.
To link a chart title to a cell, click the title, type an equals sign in the formula bar, click the cell holding the text, and press Enter.
What if the chart looks empty or wrong?
- Chart is blank for a month: the SUMIFS block returns zeros, so the axis may look flat. Fix the axis maximum, or add an IF that shows a message.
- Dropdown arrow missing: the cell is not selected, or Source points to a name that does not exist. Re-enter the name.
- #N/A on the Calc sheet: the label in B1 does not match the list, often because someone pasted over the cell.
- Numbers look low: dates in Data are text, so SUMIFS skips them.
Small improvements
Protect the formula cells, leaving only B1 unlocked, so nothing is overwritten by accident. Add a second dropdown for Category if you want an optional filter, using a wildcard such as * when nothing is chosen. Place the month cell where it stays visible when you scroll, using Freeze Panes.
Checklist: MonthList named, validation on B1, Calc!B2 returns a date, every SUMIFS reads Calc!B2, and charts point at dashboard blocks. If you would rather not maintain this, SpendixAI offers a dashboard and 25 ready-made reports that work from statements you upload.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


