Charts , VLOOKUP & Pivots Expand With The Table

June 02, 2022 - by Bill Jelen

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.

Build a chart based on a table... — Figure 501. 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.

Add a new row and column to the table and the chart grows. — Figure 502. The chart automatically grows because it is based on the table.

This article is an excerpt from Power Excel With MrExcel

Title photo by Pablo Arroyo on Unsplash