You have a worksheet with lots of data, but do you know what the data means? Pivot tables help you see the “big picture” by summarizing, analyzing, and presenting the data in a meaningful and interactive way. For example, if you had a large sales data set, you can quickly find out how the products were sold in each quarter.
1. How To Insert A Pivot Table
Select the source data range for a pivot table. In the “Insert” ribbon click the Pivot Table drop-down and select the PivotTable option.
Hint – The first row must use unique headings for each column. Additionally, do not include any blank rows or columns.
2. Select The Data Source For The Pivot Table
Excel will now display a pivot table wizard; the first section will already be populated with the data range you selected. You can now specify the pivot table target location, either creating the pivot table on a new worksheet or at a target cell on the current worksheet.
3. Setting Up Your Pivot Table
Once created, you will be presented with an easy-to-use pivot table interface. To configure the report, all you need to do is click and drag and drop the “pivot fields” into the “pivot table panel”. From here you are free to change the fields using different combinations to display the data in a way that is useful to users.
Based on the field panel (in the PivotTable Field List) in the below diagram, the outcome is the following pivot report. Note that based on our selections above:
- The ‘Category’ field is down the columns
- The ‘Person’ field is across the rows
- The ‘Sales’ are distributed across the relevant fields
As you can see, it summarises the data in a presentable format that allows you to understand the data. This is especially useful when you have very large datasets and you’re trying to make something meaningful of it.
