Dynamic chart range in Excel

A chart stores a reference to its source data. It's literally a formula, like =SERIES(Sheet1!$B$1, Sheet1!$A$2:$A$13, Sheet1!$B$2:$B$13, 1). If that reference is a fixed range ending at row 13, then when March's data arrives in row 14, the chart doesn't know it exists. You paste in the new data and glance at the chart, and it shows the same twelve months. Nothing looks broken. If you're unlucky, you present last month's picture as this month's. The fix is to make the source grow itself, and there are two modern ways to do it:

• Bind the chart to an Excel Table (Ctrl+T). When the source range is a Table, Excel extends the chart's series automatically as rows are added to the Table. Type a new month at the bottom of the Table and press Enter, and the Table absorbs the row. Pressing Tab from the last cell adds a row too. The chart redraws with the new point immediately. This is the default, dependable approach, and it's the reason "every reference range should be a real Table" is a rule and not a preference. To set it up, convert the data to a Table first (Ctrl+T, then confirm "My table has headers"), and only then insert the chart from inside the Table. New rows flow through automatically.
• Bind the chart to a spilled dynamic-array range with the # operator (M365 only). Say your chart data is produced by a spilling formula, such as =FILTER(Sales, Sales[Region]="South") in H2, which spills down as many rows as match. You can point the chart, or a name, at H2#, the "spilled-range reference." The # means "the whole spill, however big it currently is." When the filter matches more rows next month, the spill grows, and the # reference and the chart grow with it. A hand-maintained Table suits the first route. This one is the elegant path when the charted data is itself a live query result (a filtered, sorted, or de-duplicated view).

What about the older OFFSET named range?

There's also a legacy third way, a named range built on OFFSET or INDEX/COUNTA, which predates both and still turns up in inherited workbooks. You should be able to read it. =OFFSET($A$2,0,0,COUNTA($A:$A)-1,1) means "a column starting at A2, as tall as there are non-blank cells." You shouldn't write it, though. A Table or a # spill does the same job more legibly, and OFFSET is volatile. It recalculates on every change, everywhere, which slows large workbooks down. The same principle runs under all three. A chart's source should be defined by a rule ("all the data"), never by a fixed address ("rows 2 to 13").