Microsoft Excel is used by many professionals in day-to-day business for data management and reporting. Today we have a mass of data that is often difficult to analyze or understand which is why we use Excel’s reporting tools to create simple visual representations of large data sets.
Enter Pivot Tables, one of Microsoft Excel’s most powerful and useful features for reporting. If you’ve tried to create a report using a Pivot Table or have looked over your colleague’s shoulder while they work on one, you may find the idea of Pivot Tables daunting.
Pivot Tables are actually quite straight forward, once you understand a few basic principles of how they work. We cover this in depth in our MS Excel Intermediate online course, but we thought we’d help you get started by giving you a few key pointers…
Many people make the mistake of trying to Pivot Data that is already aggregated or summarized. Pivots are designed to aggregate and summarize data, so the best data to use for Pivots is your lowest level of granularity data such as Transactional Data.
For example, if you want to Pivot your Sales Data, don’t start with your Sales Data per month. Start with Data that lists each individual line item that makes up your Sales Data.
To use Pivot Tables, your Data needs to be in a Standard Tabular Range, where the First Row contains your Field Headings, and each row thereafter is a line item of your data.
In other words: NO empty rows, NO empty columns and NO merged cells.
Once your Data fits the criteria as detailed above then you are ready to create your Pivot. If you would like to follow along with the example used, then please click on the link below to download the sample workbook.
Creating a Pivot Table:
1. Select one of the cells (a) within your Data, click on the Insert Ribbon (b) and then select Pivot Table (c)
2. A Create Pivot Tables dialogue box should pop up (as shown below). Check that it has detected and selected the correct Range (d) and simply click on OK (e)
3. A new sheet will be created (as shown below) with a Pivot Table container (f) on the left and a list of your Pivot Table Fields on the right (g), which effectively is just the Column Headers of your Source Data.
4. To build your Pivot, simply select the fields from your source data that you want to report on. So, for example, to create a report of Total Sales per Region, simply check the boxes next to Region (h) and Sales (i) ,and voila, there is your report!
6. By checking those 2 boxes, Excel placed the Region dimension (j) – which is Text – in the Rows quadrant and the Sales value (k) – which is Numeric – in the Values quadrant and applied the default aggregation of Sum (l) .
7. To change your report simply change the dimension. So, if you unchecked Region and selected Category (m) instead, you now have a summary report of Total Sales per Category.
8. You can even select more than one dimension. So, going back to our example, instead of switching dimensions, leave the Category dimension checked and select the Region dimension as well (n) . You will now have a report that has a primary dimension of Category (o) with a further breakdown by Region (p) as a secondary dimension below that.
9. To change the order of your dimensions, simply change the order of the fields in the Rows Quadrant by dragging the Region field (q) and dropping it above the Category Field.
10. Or possibly even better still, drag one of the fields into the Columns Quadrant (r) to create a a 2-dimensional tabular report of your Data.
11. To cap it all you can graph you pivot by clicking on the Insert Ribbon (s) and Recommended Charts (t), and you can view your Pivot in pictorial form.
Some Key Points to end off with:
- As you can see, Pivots need space to change and morph according to the dimensions that are selected. If you create multiple pivots it’s always best to put them on different sheets or at least leave a decent gap between them, otherwise they can end up overlapping and erroring as a result.
- To format the numerical values in a Pivot, don’t format the cells that they are in, as these are always changing; format the underlying aggregated value. To do this, right-click on one of Numerical Values and select Number Format, NOT Format Cells.
- To see the detail behind an aggregated value in your Pivot, simply double click on the cell and a brand new sheet will open up displaying all the individual items that make up that value from your source data.
- Pivots have there own dedicated set of Ribbons which we encourage you to explore once you get the hang of the basics. One key point you need to be aware of though, is that these Ribbons as well as the Pivot Table Fields panel on the right will disappear if you click off your Pivot Table or Chart. To get them back again, simply click anywhere on your pivot table and they will return.
- Finally, pivots don’t automatically update if and when the source data changes. To update a Pivot you need to click on Refresh which you will find on the Analyze Ribbon.
This step-by-step guide should definitely help you get started with Pivot Tables. If you like what you see however, and would like to learn more about Pivots and a whole host of other time saving Excel features then why not enrol for our Microsoft Excel Intermediate online course. You’ll be so glad you did! 😊