I have a table on an Excel 2010 Worksheet 'Consolidated Data' where:

  • Column A is filled with arbitrary dates sorted by increasing age
  • Column F has numeric data relevant to this question
  • Row 1 is the header / title row.
  • Data begins on Row 2.
  • There are no intermediary rows with empty cells.
  • Data currently ends on Row 34.

In the same Workbook is Worksheet 'Graphs' which has a chart named Data over Time and the data range of:

Consolidated Data'!$A$2:$A$100,'Consolidated Data'!$F$2:$F$100

Charted data on the graph after Row 34 appears as a straight line at the highest value.

I am trying to get the graph to stop adding the empty data after 'Consolidated Data'!F34, but to also grow in width dynamically if I add new data to 'Consolidated Data'!F35.

I tried to follow the advice from here, but am getting an error when setting the series as the Chart data range.

I tried to create names as follows:

'Consolidated Data'!YVal    =OFFSET('Consolidated Data'!$F$2,0,0,COUNTA('Consolidated Data'!$F:$F)-1,1)
'Consolidated Data'!XVal    =OFFSET('Consolidated Data'!YVal,0,-5)

but when I set the series per the article

=SERIES(,'Consolidated Data'!XVal,'Consolidated Data'!YVal,1)

I get an error.

Of note, this is the first time I'm creating names in Excel. When I click the name box, type a name, hit 'enter', and then enter the data in the formula, the current cell I'm in (happens to be unused Column G on 'Consolidated Data') displays a value.

What am I doing wrong, and how can I achieve the intended goal?

  • Try creating your name via Formulas->Name Manager->New instead and put the offset formula in the "refers to" box. When you use the name box directly in the toolbar, it assumes the "refers to" is the current selection, instead of the dynamic range you are seeking. – Madball73 Apr 10 '14 at 18:51
  • @Madball73 - I will; do you think if it's done that way, the process will work as expected, or is there an actual problem with my code I'm setting? – JoshDM Apr 10 '14 at 18:57
  • I'd have to setup sample data to double-check (although, yes, the dynamic chart method mentioned in the link should work). But by the way you describe your name creation method, I can guarantee it won't work the way you were doing it! – Madball73 Apr 10 '14 at 18:58
  • I figured it might be the way I am creating names. I'll let you know. – JoshDM Apr 10 '14 at 19:33

I would convert the data range to a Table (Select the range, Insert ribbon, Table button). This will automatically stretch and shrink as you add and delete rows.

Then I would change the chart to Select Data from the Table. This range will also automatically stretch and shrink as you add and delete rows.

No formulas are required and Tables have a lot of other benefits.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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