I am working with a client to create dashboards to summarize data. Data is appended on a weekly basis, so any charts that show trending data must either be manually updated to include the new data (not workable), or the range somehow magically updated. As we are working in Excel 2007, I have used the excellent Table functionality as the ability use table references to automatically update formulas to include the new data works very nicely.
As it turns out, any chart that is based on data within a table, even if the table functionality was added to the data after the chart was drawn, causes the chart to automatically include the new data when the table expands.
When you're looking at the chart, this isn't obvious - the series names for the data don't reflect this - they just get automatically updated with the new cell references:
=SERIES("Series name",Sheet1!$A$69:$A$79,Sheet1!$K$69:$K$79,3 increases to
=SERIES("Series name",Sheet1!$A$69:$A$80,Sheet1!$K$69:$K$80,3 ) when a new row is added.
Even if you try to manually change the series references to a table reference:
=SERIES("Series name",Sheet1!$A$69:$A$80,Sheet1!TableName[TableColumn],3), Excel recognizes the table reference, but then renames the series back again anyway.
I find this kind of behavior annoying for two reasons - I should explicitly chose if I want a chart to expand a range when I have chosen data based on cell references. Secondly I spent a while trying to use the methods you needed to in Excel 2003 to create dynamic charts, as I assumed (I know..) that the chart was not going to update because of the use of absolute cell referencing.
If you want to know more about how you can create dynamic charts without table referencing, nip over to Jon Peltier's website.
If you're intrigued by these posts on dashboards, reporting, and charts in Excel, I have the perfect one-day training for you - Jon Peltier and I are hosting a Dashboards in Excel seminar on June 2nd. For more details, see here.
14 April 2010
Subscribe to:
Post Comments (Atom)
Actually, the List feature in Excel 2003 (which is the precursor to the Table feature in Excel 2007) handles expanding ranges in the same way. This is why the shortcut to insert a Table in 2007 is CTRL+L (L for list).
ReplyDeleteYou should note that not only charts, but any series that references a column of data in a List/Table updates as the number of rows changes.
Well there you go - didn't know that. I find it frustrating that the series won't keep table notation though. I am one for consistency..
ReplyDelete