Excel – All cells in column as graph source except header

chartsmicrosoft-excel-2010worksheet-function

I want to create a graph of all values in a column, except the first which is a header. I could do this by selecting the following as Series X Values:

=Foo!$A$2:$A$8

Problem is that I'd have to adjust it for every new value I wanted to add. To get all, no matter how many there are I can do this:

=Foo!$A:$A

With this the graph shows all values and I don't have to adjust anything when adding new rows. Problem now is it includes the header, which messes things up in the graph. How can I exclude just the first row, without hardcoding the end row?

I suppose I could do the following, which seems to work, but it feels a bit ugly…

=Foo!$A2:$A65535

Any ideas?

Best Answer

Easiest solution is to turn your data column into a single-column table. Then assign that to your data series. The data in your chart will grow as you add values to your table, and the column name will be used for your data series.

EDIT: You could also use a named range for your data, using the Count function to determine the end of your range, and Offset to help name it. I'll look up the exact syntax when I'm back at my computer with Excel. Then use the Range as your chart's data source-this is a pretty common approach for interactive charting.

EDIT2 Named Ranges: To use this solution, follow these steps:

  1. Create a named range for your chart data using the following formula:

    Name MyRange, Refers to =OFFSET(Foo!$A$1,1,0,COUNT($A:$A),).

    This works by using your header cell ($A$1 in this example) as an anchor point for the offset formula. Offset looks at all the data starting 1 row beneath your header, in the same column, and has a height equal to the count of data in your column A. This works as long as there are no non-data cells in your column-if there are, you'll need to use one of the variants of Count (e.g. CountIF, CountIFS, CountA) to deal with the odd data.

  2. Insert a blank chart (your choice as to type).

  3. Right-click chart, then Select Data.

  4. Series Name =Foo!$A$1, Series Values =Foo!MyRange

  5. Your series should reflect your data, including new cells as they're added.

    Beware that newly added data could throw off your formats on axis if its out of range of manually set min/max values.

Both this solution and the table solution will result in the same chart series and both will expand when new data is added.