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?
Or change the graphing formula so that it only considers cells that have valid data in them. Something like ‘IF cell NOT EMPTY THEN <calculation formula>’. Then define the entire column range to be considered.
A quick google search on “excel graph variable data range” led me to this:
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.
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, 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.
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.
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.