How to create a dynamically-growing graph which ignores empty tail cells

chartsmicrosoft excelmicrosoft-excel-2010

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?

Best Answer

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.

Related Question