Excel Automatic Chart Guide: Automatically Update Charts with WRAPROWS and VSTACK
Excel automatic charts use dynamic array spills (#), WRAPROWS, and VSTACK to update automatically as data grows. Copy and paste the sample below to reproduce the same results.
Quick Fix: An Automatic Chart in 5 Minutes
- Convert the source data to a table with Ctrl+T (Sales).
- Create a summary table with spill formulas (HSTACK/MAP, etc.).
- Insert a chart → enter
=Sheet!StartingCell#for the data range. - For multiple series, use WRAPROWS and VSTACK to keep the table layout consistent.
How It Works: Spill (#) + WRAPROWS + VSTACK
A spill (#) reference automatically tracks changes in range size. WRAPROWS turns a one-dimensional array into a table, while VSTACK combines multiple tables vertically.
Practical Examples: Line and Column Charts
Daily Totals (Line Chart)
=LET(
d, SORT(UNIQUE(Sales[Date])),
y, MAP(d, LAMBDA(x, SUM(FILTER(Sales[Revenue], Sales[Date]=x)))),
HSTACK(d, y)
)Columns by Category (Multiple Series)
=LET(
d, SORT(UNIQUE(Sales[Date])),
c, SORT(UNIQUE(Sales[Category])),
grid, MAP(c, LAMBDA(cat, MAP(d, LAMBDA(dt, SUM(FILTER(Sales[Revenue], (Sales[Date]=dt)*(Sales[Category]=cat))))))),
HSTACK( VSTACK({"Date", TRANSPOSE(c)}), VSTACK(d, grid) )
)Arrange into Two Columns (Name, Value) with WRAPROWS
=LET(
cats, UNIQUE(Sales[Category]),
sums, MAP(cats, LAMBDA(c, SUM(FILTER(Sales[Revenue], Sales[Category]=c)))),
ranktbl, SORTBY(HSTACK(cats, sums), INDEX(HSTACK(cats, sums),,2), -1),
flat, TOCOL(TAKE(ranktbl,5),1),
WRAPROWS(flat, 2, "")
)Combine Data from Different Monthly Sheets (VSTACK)
=VSTACK(Sales_2025_08, Sales_2025_09, Sales_2025_10)Alternatives, Notes, and Checklist
- For large datasets, use Power Query or Power Pivot, and consider PivotCharts.
- Always link charts using
StartingCell#. - Use the WRAPROWS pad argument to handle blank values.
Troubleshooting
| Issue | Cause | Solution |
|---|---|---|
| Only part of the chart is displayed | Fixed cell reference | Change it to StartingCell# |
| #SPILL! | Spill range is blocked | Clear the area |
| Date axis is incorrect | Dates are stored as text | Use –Date or check the formatting |
| Number of series changes | Variable number of columns | Use WRAPROWS to keep the layout consistent |
Conclusion
You can create Excel automatic charts using only WRAPROWS, VSTACK, and spills (#). In the next article, you will create an automated dashboard with KPI cards and sparklines without using a PivotTable.