Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

Use a table for your next pivot table

Tags 
Summary 
If you're building a pivot table from data in an Excel worksheet, you should be using a table for the source data. In this short video, we show you how to use a table as the data source for your next pivot table, and why you should care.
Video Transcript 

This video shows how to base a pivot table on an Excel table. This creates a dynamic range. The advantage of using a dynamic range with a pivot table is that the range automatically expands when data is added, and contracts when data is removed. By using a dynamic range, your pivot table will always be in synch with your data.

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.

We 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.

Author 
Dave Bruns