Quick, clean, and to the point

Why you should use a table for your pivot table

When you're building a pivot table based on data in an Excel worksheet, you should use a Table. A table offers important advantages and makes working with pivot tables a lot easier.

Let's take a look.

Here we have a worksheet that contains property listings. Let's quickly build a pivot table without using a table.

We'll select a cell in the data, choose Pivot Table from the Insert tab on the ribbon, and accept default settings.

As you can see, we get a pivot table on a new sheet. Let's add the field Address to get a count of all properties, then add the field Status as a Row Label. You can see that we have a total of 16 properties: 8 for sale, 2 pending, and 6 Sold.

Now let's add some additional properties to the data. We'll just paste the new listings at the bottom of the existing data. Now let's refresh the pivot table. Notice that the pivot table doesn't change. That's because the new data isn't automatically added to the pivot table.

We can fix the problem by going to the pivot table Options tab, and choosing Change Data Source on the Ribbon. After we update the data source to include the full set of data, we see 26 properties in the pivot table as expected.

Now let's remove the pivot table, and try again. This time, we'll convert the data to a Table first.

To convert data to a table, select a cell anywhere in the data, then choose Table from the Insert menu. Then change the table style if you like.

You can also change the table name to something more meaningful, but this is optional.

Now let's create the Pivot Table again. You can use the command on the Insert tab, or you can use the dedicated button the Table Design tab: Summarize with PivotTable.

We'll accept default settings, then build out the pivot table as before.

The results are the same.

Now let's add the additional data and refresh. This time, we see all 26 properties.

When you use a Table as source data for your Pivot Table, you get a big advantage: any new rows you add to the table will automatically be included in the pivot table. In addition, any new columns in your Table will appear as new fields in the pivot table.


Related shortcuts

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.