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 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?

share|improve this question
    
Don't forget, in Excel 2010 you'd have to go =Foo!$A2:$A1048576, which is even uglier! –  dav Aug 24 '12 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 '12 at 14:08
    
Don't do that-the solution below takes all the ugliness away! –  dav Aug 24 '12 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 '12 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 at 1:02

2 Answers 2

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.

share|improve this answer
    
Named ranges sounded interesting. Looking forward to example :) –  Svish Aug 24 '12 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 at 1:04

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...

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.