Concept
Needs: Microsoft 365 / Excel 2021+ (TAKE/HSTACK: 365/2024). Data: tblSales with an FY column.
1. Why formulas instead of pivots?
| Pivots + slicers | Formulas + controls |
|---|---|
| fast to build, great for exploring | full control of layout and logic |
| refresh needed | live, instant |
| pivot chart limits (no scatter, waterfall…) | any chart type |
| slicer look is fixed | dropdowns, spin buttons, any control |
Many professional dashboards mix both.
2. The "All" trick for filters
With inp_Region = "All" or a region name:
=SUMIFS(tblSales[Revenue], tblSales[FY], inp_FY, tblSales[Region], IF(inp_Region="All", "*", inp_Region))
"*" matches any text in SUMIFS/COUNTIFS. In FILTER, use a condition that is always TRUE for All:
((tblSales[Region]=inp_Region) + (inp_Region="All")) > 0
3. Top-N products chart — one formula
In CALC!H5:
=LET(
p, UNIQUE(tblSales[Product]),
revenues, SUMIFS(tblSales[Revenue], tblSales[Product], p, tblSales[FY], inp_FY,
tblSales[Region], IF(inp_Region="All", "*", inp_Region)),
TAKE(SORTBY(HSTACK(p, ROUND(revenues/10^5, 1)), revenues, -1), inp_TopN)
)
FY2025-26, All regions, Top 5 (₹ lakh): Laptop 991.0 · Smartphone 754.8 · Smartwatch 210.8 · Earbuds 178.6 · Study Table 160.4.
4. Named ranges for the chart
Formulas → Name Manager → New:
ch_TopNames = CHOOSECOLS(CALC!$H$5#, 1)
ch_TopValues = CHOOSECOLS(CALC!$H$5#, 2)
Insert an empty bar chart → Select Data → Add series: Series values ='Sales Dashboard.xlsx'!ch_TopValues; Horizontal (category) labels ='Sales Dashboard.xlsx'!ch_TopNames.
Change the spin button from 5 to 8 → the spill grows to 8 rows → the chart shows 8 bars. No range editing, ever.
(Excel needs the workbook name in front of names in the series dialog; it fills it in after you press OK.)
5. Monthly trend for a selected region
Month starts of the selected FY in CALC!B20 (LEFT(RIGHT("FY2025-26",7),4) → 2025, so Apr-2025 … Mar-2026):
=EDATE(DATE(LEFT(RIGHT(inp_FY,7),4),4,1), SEQUENCE(12,1,0))
Current-year revenue in C20:
=SUMIFS(tblSales[Revenue], tblSales[OrderDate], ">="&B20#, tblSales[OrderDate], "<"&EDATE(B20#,1), tblSales[Region], IF(inp_Region="All","*",inp_Region))
Last year in D20, hidden by the checkbox from Lesson 2:
=IF(inp_ShowLY, SUMIFS(tblSales[Revenue], tblSales[OrderDate], ">="&EDATE(B20#,-12), tblSales[OrderDate], "<"&EDATE(B20#,-11), tblSales[Region], IF(inp_Region="All","*",inp_Region)), NA())
A line chart on C20# and D20# (via names, as above) shows current year vs last year; unticking the checkbox removes the LY line.
6. A filtered detail table
=TAKE(SORTBY(FILTER(tblSales[[OrderDate]:[Revenue]],
(tblSales[FY]=inp_FY) * (((tblSales[Region]=inp_Region)+(inp_Region="All"))>0)),
FILTER(tblSales[Revenue], (tblSales[FY]=inp_FY) * (((tblSales[Region]=inp_Region)+(inp_Region="All"))>0)), -1), 10)
Top 10 orders for the selection — a live "biggest deals" table next to the charts. (Use LET to avoid writing the condition twice.)
7. Performance
Dynamic arrays over 50,000 rows recalculate quickly, but dozens of SUMIFS over full columns on every change add up. Keep each calculation once in CALC, reuse via # references, and avoid volatile functions (Module 8).
Common mistakes
Pointing chart series at fixed ranges like H5:H9 (the chart doesn't grow). Forgetting "All" handling. Joining names and values into one text column (with &) so the chart has nothing numeric to plot. Writing the same filter condition many times instead of LET.