Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
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. –  Lance Roberts Nov 7 '10 at 0:41

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
That worked! Thanks for such a simple solution. –  Mike Sickler Nov 8 '10 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:

=OFFSET(Summary!$B$2,0,0,COUNTA(Summary!$B:$B)-1)

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 can just reference a whole table. If you try that, Excel will convert the chart references to hard-coded ranges.
  • 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.