TechSutra

Microsoft Excel Pivot Tables Unplugged!

Guest Post By Sanghamitra

Have you ever felt lost in the sea of numbers and data? Have you broken into a cold sweat when you have to make sense of large volumes of information, analyse trends and patterns? Do you take hours copy-pasting, sorting and filtering the data and still remain inconclusive on what the data really means? Then, help is here!

I will be dedicating this entire post to the known but little-used function in Microsoft Excel called ‘Pivot Table’. Now, for starters, let’s get familiar with the word ‘Pivot’.  The dictionary.com defines it as a pin, point, or short shaft on the end of which something rests and turns, or upon and about which something rotates or oscillates. The Pivot Table does precisely this – it acts as the central point around which hundreds of thousands of pieces of information swing into places, giving out the interpretation of data, in the way you want. It helps you summarize, compare trends and what’s more, it gives you the power to decide what you want to analyse and how you want to do it. You can view the worksheet in any layout and in any view that you want, giving you the ease to make data analysis at your comfort.

Let’s get started…

Imagine you are the owner of a large chain of Fast Food Restaurants. You have operations in fifteen countries and twenty-nine cities around the world. You have, before you, an Excel spreadsheet which contains the income, expenses and profit from all these cities for all the twelve months, for the year 2007. That’s a whooping 2088 cells and 348 rows! As you can see below, the numbers and figures just go on and on and on...!

the-first-look

Decide what you want to analyse.

Say, I want to analyse the following :

1.    What is the total annual profit that I have earned from each country? 2.    What is the monthly profit that I have earned from each city? 3.    What is the average income I have earned from Mumbai and Chennai for the months of January to June?

Enter the Pivot Table Wizard

Path : Go to Data tab on the Menu Bar and click on PivotTable and PivotChart Report which will open the PivotTable Wizard

Step2-Enter the Wizard

The first step in the Wizard is about the source of data and representation of data. •    Where is the data you want to analyze? - determines where the source of data is located at. In this example, we will select Microsoft Excel List or Database since our data is in the current spreadsheet we are using. If you want to create a pivot table based on rows from an external database, choose "External data source." You can also create a pivot table that uses multiple separate ranges, which is occasionally useful if you need to combine reports in separate worksheets or workbooks. In this case, select "Multiple consolidation ranges." •    What kind of report do you want to create? - determines whether you want to create a Pivot Table or a Pivot Chart. In this example, we will currently limit ourselves to the Pivot Table.

Step 3 - First step in the Wizard

Click NEXT

The second step in the Wizard is about source of data.

It asks you to select the data range; the default settings would have automatically selected all the cells. If not, Click the icon indicated and select all the relevant cells whose data you want to analyse.

Step3 - Second Step in Wizard

Click NEXT

The final step in the Wizard asks for the location of the Pivot Table.

You can choose to insert the table in the worksheet you are currently working on or else, you may choose to insert it in a new worksheet. Normally, to get a clean interface, I would recommend inserting it in a new worksheet.

Step3 - Final Step in the Wizard

At this stage, the Pivot Table Wizard gives you two options - you may click on the LAYOUT tab to move to a screen where you will be able to arrange your column headers in the manner in which you want to represent data (Let's call it LAYOUT TAB METHOD, for simplicity) or you may click FINISH, exit the wizard and then do this exercise (the FINISH TAB METHOD). I've explained both the options below; however, for getting answers to the questions and data analysis, I have used the FINISH TAB METHOD.

LAYOUT TAB METHOD

Once you click the Layout Tab, it will give you the screen as shown below. The fields shown on the right hand side, are the exact column headers in your database. A mouse-over on the fields will give you the complete name, as has been illustrated for Profit (in dollars). You can now drag and drop the fields into the spaces labelled Row / Column / Data / Page to get your data representation.

Step 3 - Layout Tab Method

FINISH TAB METHOD

If, instead of clicking on the LAYOUT TAB, you click on the FINISH TAB, you will exit from the Wizard. The fields shown on the left hand side, are the exact column headers in your database. You can now drag and drop the fields into the spaces labelled Row / Column / Data / Page to get your data representation.

finish-tab-method1

Now that we have a broad outline of the Pivot Table, let us see how we can get different representation of the same data.

If you click anywhere in the layout, you will get the PivotTable Field List. In the PivotTable Field List are the names of the columns from the source data : Country, City, Month, Income (in dollars), Expense (in dollars) and Profit (in dollars). Each column in the source data has become a field with the same name. To create a Pivot Table, you must drag the relevant field name from the field list and drop them onto the layout area. For example, let us drag the Country field into the Drop Row Fields Here. It gives one row for each Country Name.

data-by-row

Similarly, if you drag the Country field into the Drop Column Fields Here, it will give one column for each Country Name.

Data by COLUMN

To remove any field from the layout, just drag the field and release it out of the layout. It will get removed from the Pivot Table automatically.

QUESTIONS : Now that we know how to use the Pivot Table, let us go back to our original questions and see how we can use the Pivot Table to answer them.

Q1. What is the total annual profit that I have earned from each country? It is clear that you need to use the two fields – Country and Profit (in dollars) to answer the question. Once you have decided which fields you would need to use, you need to decide where to drop each field.

To display each country name on its own row, drag the Country field into the Drop Row Fields Here. It gives one row for each Country Name, as shown above.

Remember that numbers should always go into Drop Data Items Here. Drag the Profit (in dollars) field into the area labelled Drop Data Items Here. Excel automatically adds up numbers in this area and the total then appears against each of the Country names. A Grand Total of all the profits earned appears in the last row of the table.

Now, you may find that the numbers you had entered in your original database were in dollars and here is displayed in numbers. To get the same formatting as was in your database, right-click on Sum of Profit (in dollars) – click Field Settings. On the display which appears, click on Number and format cells accordingly.

Getting original formatting (Dollars, in this case)

Voila! The Pivot Table has given you a summation of the profits of all the months, for each country.

Q1 - Final Output

Q2. What is the monthly profit that I have earned from USA and UK?

It is clear that you need to use the three fields – Month, Country and Profit (in dollars) to answer the question. Once you have decided which fields you would need to use, you need to decide where to drop each field. Since it asks for monthly profit, to display each month name on its own row, drag the Month field into the Drop Row Fields Here. It gives one row for each Month Name.

Now, to display each country name on its own row, drag the Country field into the Drop Row Fields Here; (you can display the Country name column-wise as well by dragging and dropping the Country field in the Drop Column Fields Here). It gives one row for each Country Name under the broad row of each month.

Now, drag the Profit (in dollars) field into the area labelled Drop Data Items Here. Excel automatically adds up numbers in this area and the total then appears against each of the Country names for each of the months. Under each month, a total of profits earned for the month also appear.

A Grand Total of all the profits earned appears in the last row of the table.

Now, click the filter (drop-down) on the Country field in the Layout and select USA and UK. The monthly profits earned from them are displayed.

Q2 - Total monthly profits from each country

The monthly profit from USA & UK get generated.

Q2 - Filtering for US & UK

Q3. What is the average income I have earned from Mumbai and Chennai for the months of January to June?

It is clear that you need to use the three fields – Income (in dollars), Month and City to answer the question. Once you have decided which fields you would need to use, you need to decide where to drop each field.

Since it asks for income for the first six months, to display each month name on its own row, drag the Month field into the Drop Row Fields Here. It gives one row for each Month Name.

Now, to display each city name on its own row, drag the City field into the Drop Row Fields Here; (you can display the City name column-wise as well by dragging and dropping the City field in the Drop Column Fields Here). It gives one row for each City name under the broad row of each month.

Now, drag the Income (in dollars) field into the area labelled Drop Data Items Here. Excel automatically adds up numbers in this area and the total then appears against each of the City names for each of the months. Under each month, a total of income earned for the month also appears.

A Grand Total of all the income earned appears in the last row of the table.

Now, click the filter (drop-down) on the Month field in the Layout and select months from January to June. The income earned from them is displayed.

Now, click the filter (drop-down) on the City field in the Layout and select the cities – Mumbai and Chennai. The income earned from them for the months of January to June are displayed.

Q3 - Filter the months and Cities

Now, right-click on Sum of Profit (in dollars) – click Field Settings. Click on summarize by – Average to get the average income.

Q3 - Change default SUM to other Functions (Average, in this case)

Once you click OK, the average income you have earned from Mumbai and Chennai for the months of January to June are displayed.

Q3 - Final Output

Unlike most other elements in Excel, pivot tables don't refresh themselves automatically. This means that if you change the source data, the pivot table may show incorrect data. To correct this, you can refresh the pivot table - just right-click anywhere in the Pivot Table and select Refresh Data or go to Data tab on the Menu Bar and select  Refresh Data. You can click on the exclamation mark in the PivotTable toolbar as well. Excel will then scan the source data and regenerate the pivot table. This is just a glimpse into the various ways in which a pivot table can be used for data analysis and interpretation.

blog comments powered by Disqus