About Microsoft Excel

Pivot Tables in Microsoft Excel

Home Back


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 ro analyze and summarize large amounts of data quickly and easily.
We will study the data of a compant 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 tcalled 'Create Pivot Tabel' 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 clheck 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 dispplayed 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 lables 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 Yeras 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 PivotTables effectively to analyze and summarize our Excel data quickly and easily.



Further Reading

Home Back