How do I dynamically change a chart's series in Excel?

Question

I have a large number of charts whose underlying data is updated weekly. A typical series looks like this:

Summary!B2:B7

Each week I add another row of data and update the series accordingly:

Summary!B2:B8

The problem is that I have to manually update the series in dozens of charts each week. How can I have my charts dynamically update their series values, perhaps by referencing a single cell or cells that I can change in one place?

Answer 1

In most cases you can have the series pointing to a larger range of cells than you have data for. Cells with no data are simply ignored and not included on your chart.

So in your case, you could define the series as Summary!B2:B100 and gradually add data as it becomes available.