MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Make Pivot Tables Expandable Using Ctrl+T


May 16, 2019 - by Bill Jelen

Excel Make Pivot Tables Expandable Using Ctrl+T. Photo Credit: Kyle Glenn at Unsplash.com

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.

A pivot table in E2:F6 is using a data source in A1:C16. The source data is not formatted as a table yet.

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.

Choose one cell in the table and press Ctrl+T. In the Create Table dialog, ensure the box for My Table Has Headers is checked.

Some nice formatting is applied to the data set. But the formatting is not the important part.

The source data is now formatting in blue.

You have some new records to add to the table. Copy the records.

Three new records (not formatted in blue) have been copied from another worksheet.

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.

Paste those three new records in the first row below the source data. The newly pasted records are formatted in blue. The End of Table Marker moves from C16 to C19. (The End of Table Marker is a small right-angle marker in the bottom right corner of the last cell of the table.) Although the new rows are now part of the table, the pivot table has not changed, yet.

Click the Refresh button in the Pivot Table Tools Analyze tab. Excel adds the new rows to your pivot table.

Choose one cell in the pivot table and click Refresh on the Analyze tab. The numbers change to include the newly pasted data.

Title Photo: Kyle Glenn at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.