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


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


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?

  • 1
    I ran out of time to give you an exact answer, if when I get back I see you don't have one, I'll pin it down for you. The general answer is that you use named ranges so that you can always change your range. Commented Nov 7, 2010 at 0:41

2 Answers 2


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.

  • That worked! Thanks for such a simple solution. Commented Nov 8, 2010 at 4:09

A robust method is to use dynamic named ranges. The idea is to define a named range that expands and shrinks as your data changes. This is most easily done if your data is by itself as opposed to in a sheet with lots of little tables of data scattered around. In your case, you can create a named range with a name like Series1 and a Refers To: formula that is something like this:


This assumes that B1 has a header or something you want to ignore, hence the -1 after the COUNTA. There are a few annoyances to this solution:

  • You have to create a different named range for each series and axis. You cannot just reference a whole table. If you try that, Excel will convert the chart references to hard-coded ranges and any new data will be irgnored.
  • When pointing the chart to a named range, you have to use the full name including the workbook name (or worksheet name if it's a worksheet-level named range). This means you can't just use =Series1, you have to use =Book1.xlsx!Series1.

However, it's robust so it works even if your data goes past what you originally thought would be a lot.

In summary, for short-term use or very small datasets, use Mike's answer. For data that very become very large or if you also want dynamic axis labels, use named ranges.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .