Bottom Line: Learn how to use Pivot Tables in Microsoft Excel as a powerful tool to slice and dice your data and conduct quick but effective data analysis.
Skill Level: Basic
Video Tutorial
Disclosure: Some of the links below are affiliate links, meaning, at no additional cost to you, I will earn a commission if you click through and make a purchase.
What are Excel Pivot Tables
Pivot Tables are one of Excel’s most powerful features. They automatically reorganise and summarise selected columns and rows in your source data without actually changing the source data. Instead of spending hours to create lots of summary calculations on your data, just let Excel do the work for you. And even better, if you know how to use Pivot Tables you will be able to build Pivot Charts and Interactive Excel Dashboards on top of it.
Info: If you want to make the most out of Pivot Tables, for example by using Pivot Charts and creating Interactive Excel Dashboard, we recommend to upgrade to the latest version of Microsoft Excel (included in Microsoft 365) to have all advanced charts and features.
How to Create Pivot Tables
Pivot Tables are designed to work with all different types of data, but they are especially helpful if you have raw transactional data. Transactional data means that your data is not summarised in any way and doesn’t contain any row labels, only column labels. Each row should represent one transaction or event, e.g. a sale that happened on a specific date.
In order to create a Pivot Table click on the Insert Tab > Pivot Tables. If you have selected one of the cells in your data range, the whole data range would be automatically selected. If you have not, you can simply select the table or range in the appearing pop-up window. After that, choose whether you want to place the Pivot Table in the Existing Worksheet or a New Worksheet. We recommend to always keep your raw data and your analysis separated and therefore select ‘New Worksheet’. Finally, press ‘Ok’ and an empty Pivot Table area will be created.
Slice Your Data Correctly
To fill the empty Pivot Table area with useful content, let’s have a look at the PivotTable Fields area that should be visible on the right site if you select a cell within the Pivot Table. In this PivotTable Fields area all column/ field names of the source data are listed. Below that, there are four white boxed areas, where we can put the field names via drag and drop.
Values Box
This is where we want to place our target dimension which could be anything from number of sales to revenue. The only condition is: It has to be a numeric value.
Rows, Columns & Filters Box
These dimension boxes are used to slice our data by different dimensions. Depending on into which of these three boxes you drop one of the field names, the values of this field will either be displayed in the row, column or in the filters area of the Pivot Table.
Generally, you can slice your data by as many dimensions as you want. However, we recommend to not use too many dimensions as the Pivot Table results will become more difficult to read. Only use those dimensions that are really needed for your analysis.
Furthermore, we recommend to always start with the Rows box for the first slicing dimension, then the Columns box for the second, and the Filters box for the third and all following dimensions. In some cases it might also be helpful to use multiple dimensions in one of the PivotTables areas. You can simply drop multiple field names into the same dimension box and the Pivot Table automatically integrates both hierarchically, depending on the given order of the fields in the dimension box.
Sorting & Filtering in Excel Pivot Tables
One quick way to filter your data in the Pivot Table is to use the Filters dimension box. This will instantly add a filter above your Pivot Table. However, it is also possible to filter and sort your data within the columns or rows of the Pivot Table. For that click on the arrow you find next to the row or column name in the Pivot Table and a pop-up window will appear. In there you are able to select a sorting dimension and the sorting order. Below that, you can manually filter specific values in that row or column. In case you are using sub dimension (multiple dimensions in a row or column) you can apply filtering and sorting separately to each dimension level.
Value Operations in Excel Pivot Tables
By default, the values of our target dimension are summarised as the sum of values. If you are interested in an alternative summary function, you can simply right click on the field name in the Values dimension box and click on ‘Field Settings’. In the opening pop-up window you are able to select from a variety of alternative summary functions, e.g. AVERAGE, MIN, MAX, PRODUCT, and many more.
Design of Excel Pivot Tables
There are multiple things you can do to make your Pivot Table visually more appealing for you and other potential readers. Let’s have a quick look at two really obvious ones for a quick improvement of visual appeal.
Row and Column Titles
The default titles of the rows and columns in a Pivot Table are generic and not really informative. To address that issue simply click into the respective field and change the titles to something that quickly describes what is in that row or column.
Color and Border Scheme
By default, the color and border scheme of Pivot Tables doesn’t look really appealing, but with a click on the ‘Design’ Tab in the top bar you are able to choose from a variety of alternative designs in the Quick Selection.
Conclusion
Pivot Tables are one of the fundamental tools in Excel that are easy to learn, yet so powerful in what you can do with them in only little time. They enable quick data analysis on huge data tables and let you discover your data from every possible angle in seconds. Moreover, they are the required foundation for Pivot Charts and the creation of outstanding Interactive Excel Dashboards. If you know how to handle them you are one step closer to becoming an Excel Hero!
