Excel 2019: Make Pivot Tables Expandable Using Ctrl+T
May 16, 2019 - by Bill Jelen
If you choose all of columns A:J and you later want to add more records below the data, it takes only a simple Refresh to add the new data instead of having to find the Change Data Source icon. In the past, this made sense. But today, Change Data Source is right next to the Refresh button and not hard to find. Plus, there is a workaround in the Ctrl+T table.
When you choose your data set and select Format as Table by using Ctrl+T, the pivot table source will grow as the table grows. You can even do this retroactively, after the pivot table exists.
This figure below shows a data set and a pivot table. The pivot table source is A1:C16.
Say that you want to be able to easily add new data below the pivot table, as shown below. Select one cell in the data and press Ctrl+T. Make sure that My Table Has Headers is checked in the Create Table dialog and click OK.
Some nice formatting is applied to the data set. But the formatting is not the important part.
You have some new records to add to the table. Copy the records.
Go to the blank row below the table and paste. The new records pick up the formatting from the table. The angle-bracket-shaped End-of-Table marker moves to C19. But notice that the pivot table has not updated yet.
Click the Refresh button in the Pivot Table Tools Analyze tab. Excel adds the new rows to your pivot table.
Title Photo: Kyle Glenn at Unsplash.com