An Excel chart ignores new transactions when its series are tied to a fixed range, such as A2:A50, and the new rows fall below it. The lasting fix is to turn the data into a Table with Ctrl+T and build the chart from the Table, which grows automatically. For an existing chart, edit the series range under Select Data to cover the new rows.
Work through the causes in order
- Look at the coloured outline. Click the chart, then click a series. Excel outlines the source cells. If the outline stops above your newest rows, the range is fixed.
- Check calculation mode. Under Formulas, Calculation Options, make sure Automatic is selected. On Manual, SUMIFS and charts stay stale until you press F9.
- Check for hidden rows. Charts skip hidden and filtered rows by default.
- Check the numbers. Values stored as text, left-aligned with a green triangle, plot as zero.
- If it is a pivot chart, refresh. New rows never appear until you choose Data, then Refresh All.
Fix a chart that uses a fixed range
Right-click the chart, choose Select Data, click the series, and choose Edit. In Series values, delete the old reference and select the full column of numbers again, including the rows that were missed. Edit the Horizontal (Category) Axis Labels the same way. This repairs the chart today, but it will miss new rows again next month.
Why does a Table solve it for good?
A Table, made with Ctrl+T, is a named block that extends itself. When you type or paste directly under the last row, the Table swallows the new row, and a chart built from it extends along with it. Use the Table from the start for every chart that reads transactions or monthly totals.
- Click inside your data and press Ctrl+T. Tick My table has headers.
- Select the Table columns and insert the chart, or click the chart and re-select the series from the Table columns.
- Paste new rows in the first empty row below the Table, with no blank row in between. Excel extends it.
- If it does not extend, open File, Options, Proofing, AutoCorrect Options, AutoFormat As You Type, and tick Include new rows and columns in table.
Is a pivot chart better than a normal chart?
It is a different trade. A pivot chart reads from a pivot, which reads from a Table. It lets you group by month and filter, but you must refresh after adding rows, and formatting can reset when the layout changes. A normal chart on a Table of monthly totals updates the moment the totals change. For a monthly summary, either works; choose the one whose refresh habit you will actually remember.
| Symptom | Likely cause | Fix |
|---|---|---|
| New months missing from the right | Fixed series range | Edit the series or move to a Table |
| Values never change | Calculation set to Manual | Formulas, Calculation Options, Automatic |
| Bar is zero though cell has a number | Number stored as text | Convert with Text to Columns or VALUE |
| Rows vanish when you filter | Hidden rows are not plotted | Select Data, Hidden and Empty Cells, show data in hidden rows |
| Pivot chart shows old totals | Pivot not refreshed | Data, Refresh All |
What about a blank row in the middle?
A single empty row stops Excel treating the block as one list. If you press Ctrl+T on data with a gap, only the part above the gap becomes the Table. Delete the blank row first, or select the entire range yourself before pressing Ctrl+T. Also avoid typing a running total directly beneath the data, because the Table will treat it as a new transaction.
Before you move on
Test it once: add a made-up row at the bottom and see whether the chart moves. Then delete the row. If it does not, repeat the checks above in the same order.
Keeping several charts alive month after month is where many people give up on sheets. SpendixAI has a dashboard and insights that read from imported statements and hand-added cash expenses, so there is no range to maintain.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


