# Excel: indirect referencing of range of cells for a graph? **URL:** \ **Category:** Factual Questions\ **Created:** [February 14, 2017, 9:02pm UTC](https://boards.straightdope.com/t/excel-indirect-referencing-of-range-of-cells-for-a-graph/779843 "2017-02-14T21:02:45Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\ **Post date:** [February 14, 2017, 9:02pm UTC](https://boards.straightdope.com/t/excel-indirect-referencing-of-range-of-cells-for-a-graph/779843/1 "2017-02-14T21:02:45Z")
I have several graphs set up to display changes in data. The source data is constantly being added to. Thus, …let’s say we’re talking about column A… back in 2004 maybe there were values only as far down as A129, whereas by 2011 there were values down to A406 and nowadays there is data down to A793. The ideal situation for the graphs is that they graph only the rows that have data. I, being ignorant of any more clever way of making this happen, right-click each ^$@#!@@ graph and change the range of cells in “Select Data” as new data comes in. It finally occurs to me that just because I don’t know the secret way to do indirect referencing doesn’t mean no one else knows either. Anyone know of a way I can define the range in Select Data indirectly, such as instead of **A1:A793** it’s something like **A1:!{ax12}** where !{ax12} = **{use the value in Cell AX:12 to tell you what the ending cell oughta be}**? And then I put a formula in Cell AX12 that fetches the max row number containing a value in row A? ---
**Author:** ![thelurkinghorror](https://avatars.discourse-cdn.com/v4/letter/t/7c8e57/32.png) [@thelurkinghorror](https://boards.straightdope.com/u/thelurkinghorror)\ **Post date:** [February 15, 2017, 12:17am UTC](https://boards.straightdope.com/t/excel-indirect-referencing-of-range-of-cells-for-a-graph/779843/2 "2017-02-15T00:17:26Z")
A:A captures the entire column (1:1 for the first row), if that’s what you mean. ---
**Author:** ![Tim\_T-Bonham.net](https://avatars.discourse-cdn.com/v4/letter/t/46a35a/32.png) [@Tim\_T-Bonham.net](https://boards.straightdope.com/u/Tim_T-Bonham.net)\ **Post date:** [February 15, 2017, 1:42am UTC](https://boards.straightdope.com/t/excel-indirect-referencing-of-range-of-cells-for-a-graph/779843/3 "2017-02-15T01:42:49Z")
Or change the graphing formula so that it only considers cells that have valid data in them. Something like ‘IF cell NOT EMPTY THEN \’. Then define the entire column range to be considered. ---
**Author:** ![moes\_lotion](https://avatars.discourse-cdn.com/v4/letter/m/7ba0ec/32.png) [@moes\_lotion](https://boards.straightdope.com/u/moes_lotion)\ **Post date:** [February 15, 2017, 2:46am UTC](https://boards.straightdope.com/t/excel-indirect-referencing-of-range-of-cells-for-a-graph/779843/4 "2017-02-15T02:46:40Z")
A quick google search on “excel graph variable data range” led me to this: > **[Two ways to build dynamic charts in Excel](https://www.techrepublic.com/article/two-ways-to-build-dynamic-charts-in-excel/)** > > Users will appreciate a chart that updates right before their eyes. In Microsoft Excel 2007 and Excel 2010, it's as easy as creating a table. In earlier versions, you'll need the formula method. It looks like the second method, using dynamic ranges, is pretty much what you want. The author explains it better than I can, but it uses the excel OFFSET function to return a range, and the excel COUNTA function to determine the number of cells in the range. They do note the if your column of data has a gap in it the method will not work, because COUNTA does not count empty cells. ---
**Author:** ![Dervorin](https://avatars.discourse-cdn.com/v4/letter/d/eb8c5e/32.png) [@Dervorin](https://boards.straightdope.com/u/Dervorin)\ **Post date:** [February 15, 2017, 11:52am UTC](https://boards.straightdope.com/t/excel-indirect-referencing-of-range-of-cells-for-a-graph/779843/5 "2017-02-15T11:52:14Z")
You might also look into the possibility of changing your data range into a table, and basing the chart on the table. It’s the first method listed [here](http://www.techrepublic.com/blog/microsoft-office/two-ways-to-build-dynamic-charts-in-excel/), which is also the same link referred to by **moes lotion** , coincidentally. Tables are simple and dynamically update when there’s new data, and also automatically update the chart. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [February 15, 2017, 12:41pm UTC](https://boards.straightdope.com/t/excel-indirect-referencing-of-range-of-cells-for-a-graph/779843/6 "2017-02-15T12:41:25Z")
The VERY low-tech way to do this has been around since the days of Lotus 1-2-3. In the beginning you put your data in, say, rows 2 through 5. Create your chart(s) to display rows 2 through 6. When you next want to add data, don’t simply type it into row 6. Instead insert a new row between 5 and 6 and put your new data there. Viola! The chart range automatically expands to include the new row. When your chart has 5 rows of data, the extra empty data point at the end is distracting. When it has 500 data rows the extra data point is pretty much invisible. ---
**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\ **Post date:** [February 15, 2017, 2:44pm UTC](https://boards.straightdope.com/t/excel-indirect-referencing-of-range-of-cells-for-a-graph/779843/7 "2017-02-15T14:44:09Z")
> [@LSLGuy](#): > > The VERY low-tech way to do this has been around since the days of Lotus 1-2-3. > > In the beginning you put your data in, say, rows 2 through 5. Create your chart(s) to display rows 2 through 6. > > When you next want to add data, don’t simply type it into row 6. Instead insert a new row between 5 and 6 and put your new data there. > > Viola! The chart range automatically expands to include the new row. > > When your chart has 5 rows of data, the extra empty data point at the end is distracting. When it has 500 data rows the extra data point is pretty much invisible. You know, that has a certain compelling elegance. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [February 15, 2017, 3:23pm UTC](https://boards.straightdope.com/t/excel-indirect-referencing-of-range-of-cells-for-a-graph/779843/8 "2017-02-15T15:23:07Z")
This approach also works for named ranges, functions like =SUM(A2:A6), etc. We used to have a standard that all financial spreadsheets had to have a row of dashes above & below each range that was to be summed. And all formulas had to sum from the row of the upper dashes through the row of the lower dashes. That ensured that no matter how somebody crammed an extra row in there, it’d always end up included in the totals. I cringe whenever I see a total row with a formula like =A2+A3+A4+A5. That approach virtually guarantees that the next time a row is added there will be totaling errors all over the sheet. ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [February 17, 2017, 5:01am UTC](https://boards.straightdope.com/t/excel-indirect-referencing-of-range-of-cells-for-a-graph/779843/9 "2017-02-17T05:01:00Z")
+1 on both points