Excel Charts , VLOOKUP & Pivots Expand With The Table
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Charts , VLOOKUP & Pivots Expand With The Table
Problem: I always have to add new data to the bottom of my data. Then, I have to redefine the charts, pivot tables, and lookup tables that are based on this data.
Strategy: Using tables simplifies this process. Even if you have existing charts, VLOOKUP, and pivot tables, you can benefit from changing the data set to a table.
Below, a chart is based on a table that contains 4 weeks and 3 months.
Figure 509 This chart is based on a table.
If you enter new data next to the table, the rows and columns will be added to the table and automatically added to the chart.
Gotcha: Tables were designed during the Excel 2007 development cycle. While one team was designing tables, the charting team was busy completely rewriting the chart engine. Time was running short, and the chart team opted not to support table syntax in the SERIES formula.
Figure 510 The chart automatically grows because it is based on the table.
Additional Details: Pivot tables will expand with the table, but you have to click the Refresh button on the PivotTable Tools Options ribbon tab to refresh the cache. This is still far easier than redefining the data range like you would have to do for non-table pivots.