What is a Pivot Table?
A Pivot Table is defined by Microsoft as “a powerful tool to calculate, summarize, and analyse data that lets you see comparisons, patterns, and trends in your data”.
Whenever you have information in a tabular set of data (or list) in Microsoft Excel, you can create a pivot table from this data to quickly give you some insights into your dataset. (For a few examples as to how Pivot Tables can help you answer questions about your data, see our article here).
Creating a pivot table is as simple as telling Excel what data to use, and then telling it where to put the pivot table.
Below we will step through an example of how to create a basic PivotTable. You can apply the same steps to your own data.
The data set we used for this example was a listing of sales transactions for the 1st quarter of the year (January, February, March):
The data set had a total of 104 transactions. As you can see above, each sales transaction records information regarding the month, salesperson, make of the item sold, price of the item sold, region in which the item was sold and the payment method.
So lets see how to create a pivot table to give more meaningful insight into this sales data.
Creating the Pivot Table
Excel automatically works out the dimensions of your dataset based on the position of the cursor, by including all touching cells. If there is an empty column, say, in Column E, the last few columns on the right would be excluded. So make sure to clean up your dataset first.
With the cursor still positioned somewhere in the dataset, click on the Insert tab, then click PivotTable:
This will display the Create PivotTable dialogue box:
Make sure that Select a table or range and New Worksheet have been selected, then click OK.
This will create your blank PivotTable shell in a new Worksheet:
Populating the Pivot Table
For this particular sales data, we wanted to gain an insight into how much each salesperson had sold over the three months. To do this we want to show the price (sales) by month and by salesperson.
In the PivotTable Fields area on the right of the sheet, we dragged the Month field into the Columns area:
Then we dragged the Salesperson field into the Rows area:
And finally the Price field into the Values area:
This created our basic PivotTable that has taken all the individual sales transactions and presented them showing the sum of the price (sales) by month and salesperson:
As you can see, pivot tables are very powerful for gaining deeper insight into data and can help with decision making.
The above example shows the basics of how to create a simple PivotTable, however PivotTables can be extremely powerful and there are many ways to use them.
If you would like a step-by-step guide on PivotTables that includes Excel files for you to work along with, check out our 10 Steps to PivotTable Success by clicking here.