All About Pivot Tables – Excel Guide

For those of you who know how to create a Pivot Table, have you ever considered turning your list into a Data Table? From this you can then start creating a Pivot Table.  This would be beneficial if your data source 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

Start by going to the insert tab and hit the Pivot Chart button. Then in the drop down menu, click PivotChart & PivotTable.

 

Shows where to find the PivotChart and PivotTable options

 

When creating a Pivot Table you specify the Table/Range, or just any rows or columns you need for that Pivot Table then click ok. As below in the dialog box, A1-I114.

 

Create PivotTable dialog box

 

If at a later point the source data has more rows/cols added to it, the Pivot table will not pick up the new data. Refresh will also 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.

 

Shows the change data source button

 

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

 

Dialog box to pick data range for table

 

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.

 

Format as table in Home ribbon

 

Here you can choose the Colour Scheme for your table.

Once the Excel 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. If the values area looks incorrect, this is because sometimes Excel will interpret your data incorrectly, such as mistaking it for text instead of numbers. You can change the way the Pivot Table is calculating values by clicking on the arrow to the right of the field name, then selecting the Value Field Settings. From here simply change the calculation in the Summarize Values By section, and your Pivot Table data should correct.

 

 

Shows the options available in the design tab

 

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.

 

How to rename pivot table

 

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.

Name box to automatically extend the data of a data table

If you want to learn about other advanced tools in Excel, read our guide on creating random numbers here.

 

Creating The Pivot Table

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

 

Summarise with PivotTable button in ribbon

 

Notice that the Data Table name appears in the Create Pivot Table 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 and click ok.

 

The table name straight into the data range

 

Tip: You can refresh a Pivot Table with a right click 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.

 

Refresh option to flush the table with new data

 

Final Thoughts

Overall, learning to create a Pivot Table from a Data table is a great tool. The main reason for this is the fact that these Pivot Tables are appropiate when your data source is constantly changing. Every Excel user can benefit from learning this skill – making a decision between what kind of Pivot Table you want to create is important, and this guide has hopefully given you the tools to feels comfortable with any situation!

If you are looking for a different way to visualise information in Excel, read our guide on Flowcharts In Excel here!

 

Other Helpful Articles

Using Named Ranges In Excel

Creating A Drop Down List In Excel

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.