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.
- 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.
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.
- The chart automatically grows because it is based on the table.