Classroom training available from 12 April

Excel Pivot Tables: Using A Data Table To Create A Pivot Table

For those of you who know how to create Pivot Tables, have you ever considered turning your list into a Data Table, from this you can then create the Pivot Table.   This would be beneficial if your data range was constantly changing, as explained below.

Pivot Tables and other advanced Excel formulas are covered during our advanced Excel course.  If you are doing large amount of data manipulation you may also find this article on when to use a Vlookup and when to use Index and Match useful.

Using an Excel List

When creating a Pivot Table you specify the Table/Range for that Pivot Table.    As below, A1-I114.

Creating a Pivot Table in Excel

If at a later point the original data has more rows/cols added to it, the Pivot table will not pick up the new data and refresh will not help as this can only be used to update if the original data has changed, such as a salary, so the data range would have to be updated.   You can do this by clicking in the Pivot Table and choosing the Analyse Menu and Change Data Source

Amending the data source used by a pivot table

This would place you back on the original data, enabling you to select the new range for the Pivot Table.

Selecting a new data range for a pivot table in Excel


However, if you convert your original data range into a data table, you only need to refresh as new data is added.    This is because a data table will have a name applied to it, you can also change this name if you wish.

Setting up the Data Table

Click in your Excel List and from the HOME Ribbon, choose FORMAT AS TABLE

Setting up a Data table in Excel

Choose the Colour Scheme for your table.

Once the data table has been created, notice that a new ribbon (Toolbar) appears in your Menu Tabs – DESIGN.    If you click outside the data table this will disappear, so make sure you are within the data table to use the Design Menu.

Selecting the Design Menu for a Data Table

The Table will have been named for you in this instance Table 2, shown on the left-hand side of the ribbon.    This name can be changed, all you have to do is highlight the name in the Table Name box and overtype it.

Selecting the design menu in Excel

The original table name

Amending the Table Name In Excel's QAT

The name will apply to the entire data in the list, if you select the Name box you will see the name in here, if you click on this it will highlight the entire range.  As extra data is added to the data table the name will automatically extend to include these new rows/columns.   The same if rows/columns are deleted.

Inserting the name box to automatically extend the data range of a data table

Creating the Pivot Table

Now that the Data Table has been created a Pivot Table can be created from the Design Ribbon.   Click in your data table, the Design Ribbon will appear and choose SUMMARISE WITH PIVOT TABLE

Selecting Summarise with Pivot Table for a Data Table in Excel




Notice that the Data Table name appears in the Create PivotTable box, instead of the range.  As you adjust the rows and columns in the original list, all you have to do to include these in your pivot table is a Refresh.

Inserting The Data Table name in the Create Pivot Table dialogue box

Refreshing an Excel Pivot Table

Tip:      You can refresh a Pivot Table by right-clicking over the top of it, or by selecting the Analyse Menu and Refresh.   Using the second method you can refresh all Pivot Tables built off the original data by selecting Refresh All.