Excel: Charts , VLOOKUP & Pivots Expand With The Table

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


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.

  1. 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.

  2. The chart automatically grows because it is based on the table.

For more resources for Microsoft Excel