Let’s start learning how to use Pivot tables in Microsoft Excel. Pivot tables is a powerful feature in MS Excel that can help us analyze and summarize large amounts of data quickly and easily.
We will study the data of a company that has stores all over the country – east, west, north and south – and has many product groups like milk, ice-cream, fruit and cereals. The company has a system to record unit sales and the corresponding revenues each month of the year quite meticulously.
We’ll study how we can quickly summarize the total units sales in each of the regions east, west, north and south for each of the product groups in different months and years. We’ll utilize the Pivot Tables feature in MS Excel to achieve this goal..
We arrange our data in such a way that there are no empty rows or columns in our data and the data also has clear headers like year, month, store. group, product, units and revenue.
Now we click inside our data
Click on the Insert tab
In the Tables group we select Pivot Table
In the new window called ‘Create Pivot Table’ hat pops up we notice that our complete data range has been selected.
Under the sub-title ‘Choose the data you want to analyze’ the option ‘Select a table or range’ has been checked and next to Table Range the text box has been filled with our data range address
The option New Worksheet under the sub-title ‘Choose where you want the PivotTable report to be placed
We click on OK
A new worksheet Sheet5 is created automatically.. On the left we observe an area called PivotTable and below that is written: To build a report, choose fields from the PivotTable Field List
The PivotTable Field List is displayed on the right-hand side and is made up of our data headers
Below the Field list we observe 4 distinct areas called Report Filter, Column Labels, Row Labels and values and the message: Drag fields between areas below
If you check the field Store under ‘Choose fields to add to report’ the field is placed under the ‘Row Labels’ area and on the worksheet you can the title ‘Row Labels’ in cell A3 with a drop down arrow. Below that we can observe the regions east, north, south and west displayed from cells A4 to A7. Finally we have the Grand Total in cell A8
If you click on the field ‘Units’ the field is sent to the values area as ‘Sum of units’ and the worksheet is populated from cells B3 to B8 with Sum of Units in cell B3, numerical data in cells B4 to B7 and the total of the units is displayed in cell B8
Now if we drag the Store field from the Row Labels area to the Column labels then you’ll notice that east, north, south and west – the region headers – are arranged in columns on the worksheet and below each region you have the unit values. In cell F4 we see the column header ‘Grand Total’ below whcih the total of the units is displayed in cell F5
If you de-select the fields ‘Store’ and ‘Units’ from the PivotTable Field List, the fields will be removed from the Column and Values areas and also the complete data will be removed from the worksheet
So you notice that we can remove data as quickly as we can add it!
Now e click on the Year field and it is placed under the Values area but we drag it to the Column Labels area because that’s where we want to place it
We click on the Month field and it goes to the Row Label areas but we drag it to the Reprts Filter area
We click on the Store, Group and Product fields which are placed automatically in the Row Labels
Clicking on Units and Revenue fields sends them to the Values area
The worksheet now has the Years in columns and the Total Units and the Total revenues are placed at the end of the Years
The Rows consists of east, north south and west with each region having the product groups placed under them in alphabetical order like cereal, fruit, Ice cream and milk
Let’s see how we can summarize the unit sales of the milk group in the year 2007 in the first quarter.
We’ll click on the Column Labels drop down arrow, de-select all and select only 2007
From the drop down arrow next to the Row Labels we’ll select Group,deselect all and select only milk and we can observe on the worksheet ‘milk’ under the regions
Now from the Reports Filter at the top on the worksheet, we’ll click on the drop down arrow next to Month (ALL), de-select (All) and check only January, February and March
Now we can see the sales in the first quarter of 2007 in all the regions
Now we’ll click on the ‘-‘ sign next to the regions and we’ll now only see the regions on our worksheet with the total sales and the revenue generated in each region in the first quarter of 2007 for milk
In this manner we see that we can use Pivot Tables effectively to analyze and summarize our Excel data quickly and easily.
PivotTable reports 101