MrExcel.com - Your One Stop for Excel Tips & Solutions

Convert Your Data to a Table Before Adding Records

December 01, 2022 - by

Convert Your Data to a Table Before Adding Records

Problem: I have 100 new records to paste below the original data that is in the pivot table. How do I do that?

Strategy: There are two solutions. I recommend the second one.

  • Paste the new data below your original data. Select a cell in the pivot table. Choose the Change Data Source icon (see Figure 837 above).
  • Select a cell in the original data set. Press Ctrl+T to define the data as a table. Paste new data below the original data. The Data Source is automatically updated. Simply click the Refresh button to incorporate the new records.

The table feature began in Excel 2003 as a List. In Excel 2007, it was renamed to be a Table, creating confusion between the Data Table found in the What-If tools and the Format as Table command on the Home tab.

The Table applies some interesting formatting. The Table adds Filter dropdowns. The Table makes it easier to enter formulas. But the most valuable feature of the table is to have the pivot table data range automatically grow as you add new rows to the underlying data.

Here is an example.

Currently, the pivot table uses rows 1:564 of data.

The Change PivotTable Data Source dialog allows you to change the range used in the pivot table.
Figure 839. Originally, the table used 564 rows.

Select a cell in that data and press Ctrl+T. Confirm the location of the table.

Ctrl+T launches the Create Table dialog. Specify the range and then check My Table Has Headers. Click OK.
Figure 840. Convert the data to a table, even after the pivot exists.

When you paste 2015 Q1 data below the original data, the table definition automatically changes. The other thing that updates is the range used for the PivotTable Data Source. You no longer have to visit this dialog, as it is already updated.

With the records now stretching to row 699, change the PivotTable Data Source to A1:H699.
Figure 841. That range automatically grew because the data is a table.

This still counts as a change to the underlying data, so you have to click the Refresh button to update the pivot table.

This article is an excerpt from Power Excel With MrExcel

Title photo by Markus Winkler on Unsplash