Excel charts - setting series end dynamically

Question

I've got a spreadsheet with plenty of graphs in it and one sheet with loads of data feeding those graphs.

I've plotted the data on each graph using

=Sheet1!$C5:$C$3000

This basically just plots the values in C5 to C3000 on a graph.

Regularly though I just want to look at a subset of the data i.e. I might just want to look at the first 1000 rows for example. Currently to do this I have to modify the formula in each of my graphs which takes time.

Would you know a way to simplify this? Ideally if I could just have a cell on single sheet that it reads in the row number from and plots all the graphs from C5 to C 'row number' would be best.

Any help would be much appreciated.

Answer 1

OK, I had to do a little more research, here's how to make it work, completely within the spreadsheet (without VBA):

Using A1 as the end of your desired range, and the chart being on the same sheet as the data:

Name the first cell of the data (C5) as a named range, say TESTRANGE.
Created a named range MYDATA as the following formula:

=OFFSET(TESTRANGE, 0, 0, Sheet1!$A$1, 1)

Now, go to the SERIES tab of the chart SOURCE DATA dialog, and change your VALUES statement to:

=Sheet1!MYDATA

Now everytime you change the A1 cell value, it'll change the chart.

Thanks to Robert Mearns for catching the flaws in my previous answer.

Answer 2

You could look at dynamic ranges. If you use the OFFSET function, you can specify a starting cell and the number of rows and columns to select. This site has some useful information about assigning a name to an OFFSET range.

Answer 3

You can set the range for a chart dynamically in Excel. You can use something like the following VBA code to do it:

Private Sub Worksheet_Change(ByVal Target as Range)
    Select Case Target 
    Case Cells(14, 2)
        Sheet1.ChartObjects(1).Chart.SetSourceData Range("$C5:$C$" & Cells(14,2))
    ...
    End Select
End Sub

In this case, the cell containing the number of the last row to include is B14 (remember row first when referring to the Cells object). You could also use a variable instead of the Cells reference if you wanted to do this entirely in code. (This works in both 2007 and 2003.) You can assign this procedure to a button and click it to refresh your chart once you update the cell containing the last row.

However, this may not be precisely what you want to do ... I am not aware of a way to use a formula directly within a chart to specify source data.

Edit: And as PConroy points out in a comment, you could put this code in the Change event for that worksheet, so that neither a button nor a key combination is necessary to run the code. You can also add code so that it updates each chart only when the matching cell is edited.

I've updated the example above to reflect this.

Answer 4

Enhancing the answer of @Robert Mearns, here's how to use dynamic cells ranges for graphs using only the Excel's formulas (no VBA required):

Create a dynamic named Range

Say you have 3 columns like:

A5 | Time | Data1 | Data2 |

A6 | 00:00 | 123123 | 234234 |

...

A3000 | 16:54 | 678678 | 987987 |

Now, the range of your data may change according to the data you may have, like you have 20 rows of data, 3000 rows of data or even 25000 rows of data. You want to have a graph that will be updated automatically without the need to re-set the range of your data every time you update the data itself.

Here's how to do it simply:

1. Define another cell that it's value will have the number of the occupied cells with data, and put the formula =COUNTIF(A:A,"<>"&"") in it. For example, this will be in cell D1.

2. Go to "Formulas" tab -> "Define Name" to define a name range.

3. In the "New Name" window:

   i. Give your data range a name, like DataRange for example.

   ii. In the "Refers to" set the formula to: =OFFSET(Sheet1!$A$1, 0, 0,Sheet1!$D$1,3),

   where:

   • Sheet1!$A$1 => Reference: is the Reference from which you want to base the offset.

   • 0 => Rows: is the number of rows, up or down, that you want the upper-left cell of the results to refer to.

   • 0 => Columns: is the number of columns, to the left or right, that you want the upper-left cell of the results to refer to.

   • Sheet1!$D$1 => Height: is the height, in number of rows, that you want the result to be.

   • 3 => Width: is the width, in number of columns, that you want the result to be.

4. Add a Graph, and in the "Select Data Source" window, in the Chart data range, insert the formula as you created. For the example: =Sheet1!DataRange

The Cons: If you directly use the named range as the data source for the chart, Excel will automatically convert the named range into an actual range. Any future changes to your named range will therefore not update your chart.

For that you need to edit the chart and re-set the range to =Sheet1!DataRange every time. This may not be so usable, but it's better than editing the range manually...

Answer 5

+1s for the name solution.

Note that names don't really really reference ranges, they reference formulae. That's why you can set a name to something like "=OFFSET(...)" or "=COUNT(...)". You can create named constants, just make the name reference something like "=42".

Named formulae and array formulae are the two worksheet techniques that I find myself applying to not-quite-power-user worksheets over and over again.

Answer 6

An easy way to do this is to just hide the rows/columns you don't want included - when you go to the graph it automatically excludes the hidden rows/columns