Pivot tables are Excel’s most powerful feature. A pivot table lets you summarize detailed data automatically. In this tutorial you will learn what is an Excel Pivot table and how to create an Excel pivot table to summarize a simple dataset.
Case Study
A grocery store wants to keep track of daily sales of the main fruits thei are selling: Apples and Oranges. They start with a blank Excel sheet and each day add two lines. One for the sales of Apples and one for the sales of Oranges. Towards the end of the month, the store manager wants to summarize the sales on Apples and Oranges for the month.
For our data-set, let’s take a look at this worksheet. The data-set has 62 records. 31 sales of apples and 31 sales for oranges. One record for each day and each fruit
If we want to get a quick summary of how much money were made selling apples and oranges as well as the total sales value for both, a pivot table is the quickest way to provide that summary
Click anywhere on the data set and go to the insert tab on the ribbon to insert a pivot table
Excel will detect the data-set. It is important that your data-set has column headers and there are no blank columns.
Once the pivot table is created we have to define what we want to summarize and what we want the values of our summary to be.
In this case we will summarize the products “Product” and we want the values to be the total cost of the sales for those products.”Sales Value”. You can do that by dragging and dropping each field in the correct box or selecting the two fields from the select box (it works for simple data-sets like this where Excel “understands” where to put the data based on the data type.)
And this is it. We now have a quick summary of sales for the entire period. Looks like apples sold slightly better.
Pivot tables are powerful. In the next post see how we add more details to this data-set and how we use a multi-row pivot table to summarize them.
Can you do all this without pivot tables? Of course you can. But pivot tables have so many advantages.
Hope this makes a complex concept like a Pivot Table very simple. Please share if you find it helpful.
