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 worksheet. Let's add the Address field to get a count of all properties, then add the Status field 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. 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 this problem by 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 the extra data we added, 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 tab. 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 in the Table Design tab: Summarize with PivotTable.

We'll accept the default settings, then build our 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.