1

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?

Improve this question
5
  • Don't forget, in Excel 2010 you'd have to go =Foo!$A2:$A1048576, which is even uglier!
    – dav
    Aug 24, 2012 at 12:31
  • @DavidVandenbos Ah, great... didn't even think of that... maybe I'll just scrap my excel sheet and make a web app for this file instead :p
    – Svish
    Aug 24, 2012 at 14:08
  • Don't do that-the solution below takes all the ugliness away!
    – dav
    Aug 24, 2012 at 14:18
  • Will def try out your solution when I get home. Might start on the web app later still though, but mostly because I want to try out Asp.Net MVC 4 and also replace an iPhone app I have completely :p
    – Svish
    Aug 24, 2012 at 15:08
  • Came to SuperUser with this exact question: the explicit number for the bottom cell is way ugly!! But ugliness is not it's main problem. The main problem is known what that huge number is, each time you come to type a specifier like this
    – GreenAsJade
    Jun 4, 2014 at 1:02

2 Answers 2

Reset to default
1

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.

Improve this answer
2
  • Named ranges sounded interesting. Looking forward to example :)
    – Svish
    Aug 24, 2012 at 10:11
  • The problem with this solution is the sheer amount of manual manipulation that it requires. I can easily type Foo!$A:$A, without even having to move to manual selection on the chart. I can't easily type Foo!A1:Awftwasthatnumberagain!? which is what drove me to look for a different solution. I think it's quicker to just select (using C-SH-downarrow) the entire column mimus the first cell, than setting up the range. If only there was some easy way to say Foo!A2:$A (If only that were valid syntax!)
    – GreenAsJade
    Jun 4, 2014 at 1:04
1

I have the same problem. For me, the "driver" for the question is "what is the least keystrokes to type in the source range specifier?"

I can do Foo!A$:A$ without having to manually manipulate the spreadsheet at all - I can type it in almost without thinking. This is perfect.

I can't do Foo!A1:Awftisthatnumberagain!? because ... I can't remember what that number is. It is different for different versions of Excel, and who knows for what other reasons: not robust.

For me, the best solution I have so far is to go to the spreadsheet (by clicking the manually select range button), select the cell A2 (in this example) then press C-SH-downarrow twice to select the whole column minus A1.

At least this quickly and correctly puts the proper huge number in the formula without having to remember it, in the least number of keystrokes I've been able to find so far.

Improvements welcome...

Improve this answer

You must log in to answer this question.

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