Pivot Table

Pivot Tables are one of the most powerful features in excel. Even if you are a newbie, you can crunch large amounts of data into useful information. Pivot Table can help you make reports in minutes. Analyse your clean data easily and if the data is not clean, it can help you to clean your data. I don’t want to bore you, so let's jump into it and explore.
How To Create a Pivot Table
It's simple. Just select your data. Go to Insert. Click on the Pivot Table and it's done.
001
But wait. Before you create a pivot table, ensure that all columns have a heading.

If any column heading is left blank, the pivot table will not be created and will go through an error message.

Requirement 1: All columns should have a heading to get started with Pivot Tables in Excel
002
You should have your data organised with proper heading. Once you have it, you can insert the pivot table.
003
Insert the Pivot Table From Ribbon
To insert a pivot table from the menu, follow these steps:

    1. Select your data range

004

    1. Go to the insert tab

005

    1. Click on the Pivot Table icon

006

    1. Create a Pivot Table option box will appear

007

    1. Here, you can see the data range that you selected. If you think that this is not the range you want to select, directly resize your range from here instead of going back and selecting data again.
    2. Next, you can select where you want your pivot table. I recommend using the new worksheet but you can also use the current worksheet. Just define the location in the location box.
  1. Now if you are done with the settings, hit the OK button. You will have your pivot table in a new sheet. Just select your fields for summaries. We will see how we create a summary of data using the pivot table but first let's get the basics clear. In this excel pivot table tutorial you will learn more than you expect.

008
Insert Pivot Table Shortcut (Alt > N > V)
This is a sequential keyboard shortcut to open the Create Pivot Table option box.

Hit the Altbutton and release it. Hit N and release it. Hit V and release it.  Create a Pivot Table option box will open.
009
Now just follow the above procedure to create a pivot table in excel.
Insert Pivot Table Shortcut using Old Excel Shortcut (Alt > D > P)
One thing I like most about Microsoft Excel is that in every new version of Excel they introduce new features and but they don’t discard the old features (like MS did with win 8. It was pathetic). This allows the older user to work normally on new versions as they used to work on older versions.
If you sequentially press ALT, D and P on the keyboard, Excel will open to create a pivot table wizard.
0010
Select the appropriate option. The selected option in the above screenshot will lead us to create a pivot table as we created before.
Hit Enter or click Next if you want to check your selected range.
0011
Hit Enter again.
0012
Select a new worksheet or wherever you want your pivot table to Hit Enter. And it's done.
0013
Create Reports Using Pivot Tables.
Now you know how to insert a pivot table. Let's gear up to make reports using pivot tables in minutes.
We have the data for the stationary order.
0014
The column fields are:

Order Date: Date of Order (Obviously)

Region: The region of order in the country

Customer: Name of customer (what else can it be)

Item: Ordered Item

Unit: Number of units of an item ordered

Unit Cost: Per unit cost

Total: Total cost of the order (unit*unit cost).
0015
Pivot Areas: These 4 areas are used to show your data in a mannered way.
0016
FILTERS: Put fields here from which you would like to apply filters in your report.

COLUMNS: Put the fields here that you want in your columns in the report: (It's better to show than explain)
0017
ROWS: Drag fields that you want to show raw wise as in the above image, I have shown Region in ROWS.

VALUES: Select a field to get Count, Sum, Average, Percentage (and many more) etc. you will want to see.

Now using the above information, we have prepared this quick pivot report that shows from which region how many orders are placed for each item.
0018
Now that you understand your data and Pivot fields (after all you are smart), let’s answer some questions related to this data using the pivot table quickly.
Q1. How many Orders are there?

The pivot table is initially blank, as shown in the image below.

You need to select fields (column names) in appropriate areas to see the summary or details of that field.
Now, to answer the above question, I will select Item (select any column, just make sure that it does not have blank cell in between) in value fields.
0019
Select Item from the field list and drag it into the Value field.
0020
We have our answer. Pivot tables tells me that there are a total of 43 orders. This is correct.
Pro Tip: You should check your data if its correct or not. If this number doesn’t match with data then it means either you have selected some incorrect range or that field has blank cells.
Info: The value field by default counts the number of entries in a column if it contains text and sums if the field only contains values. You can change this in value field settings. How? We will see you later in this Pivot Table Tutorial.
Now that I have selected OrderDate in the Valuesarea, it shows 42. This means OrderDate has a blank cell since we know that the total number of orders is 43.
0021
Identify Irregular Data using Pivot Tables and Clean It.
Pivot Table can help you find incorrect information in the data.

Most of the time our data is prepared by data entry operators or by users which are usually irregular and need some cleaning to prepare accurate report and analysis.

You should always clean and prepare your data in a mannered way, before you prepare any reports. But sometimes only after preparing the report do we get to know that our data has some irregularity. Come, I’ll show you how…
Q2: Tell Number of Orders from each Region
Now to answer this question:
Select Region and drag it to Rows Area and Item to Values Area.
0022
We will get a Region-Wise divided data. We can answer from which region how many orders have come.

There are a total of 4 regions in our data according to the pivot table. But wait, Notice that Central and Centrle region. We know that Centrle should be Central. There is an irregularity. We need to go to our data and do data cleaning.

To clean data in the region field we filter out the incorrect region name (Centrle) and correct it (Central).

Now get back to your pivot data.

Right click anywhere on the pivot table and click on Refresh.
0023
Your report has now been updated.
INFO: No matter what changes you make in your source data, the pivot table will keep working on old data until you refresh it. Excel creates a pivot cache and a pivot table runs on that cache. When refreshed, the old cache is changed with fresh data.
0024
Now, you can see that there are only 3 regions in fact.
Pivot Report Formatting with Categorized Rows.
0025
Sometimes you will need reports like the above image. This makes it easy to see your data in a structured way. You can easily tell from which region how many items are ordered. Let’s see how you can do this.
Move Region and Item to ROWS area. Make sure the region is at the top and items at the bottom as shown in the image.
0026
Drag Item for Value Area.
0027
As a result you will get this report.
0028
It will do. But sometimes your boss wants to report in tabular form without subtotals. To do this we need to format our Pivot Table.
Remove Subtotals From the Pivot Table
Follow these steps:

    1. Click anywhere on  your pivot table.
    2. Go to the Design Tab.

0029

    1. Click on the Subtotals menu.

0030

  1. Click on  Do Not Show Subtotals.

0031
You can see that there are no subtotals now.

Fine. But it is still not in tabular form. Regions and Items are shown in one single column. Show them separately.
Make a Pivot Table Tabular
Now to show Regions and Itemsin different columns, follow these steps:

    1. Click anywhere on the Pivot Table
    2. Go to the Design Tab
    3. Click on Report Layout.

0032

  1. Click on the Show for the Tabular Form option. Finally you will have this sophisticated view of your report.

0033
Now we know the total number of orders placed for each item from each region. It is shown in the Count of Items columns.
Please change the column name to Orders.
0034
It looks better now.

Q3: How many units of each item are ordered?

To answer this question we need a sum of Units. To do so, just move the Units field to Values. It will automatically sum up the number of units for each item. If a column in the Pivot Table contains values only, the Pivot Table by default shows the sum of those values. But it can be changed from a value field setting. How? I’ll show you the latter.
0035
Pivot Table Value Field Settings
Q4: The average price of each item?
In our sample data the price of one item is different for different orders. For example, see Binders.
0040
I want to know the average cost of each item in Pivot Table. To find this out, drag Unit Cost to Value Field. It will show Sum of Unit Cost.
0041
We don’t want Sum of Unit Cost, we want Average of Unit Cost. To do so...

  1. Right Click anywhere on the sum of the Unit Cost column on the Pivot Table
  2. Click Value Field Settings
  3. Based on the available Options, select Average and hit OK.

0036
Finally, you will have this Pivot Report:
0042
Pivot Table Calculated Fields
One of the most useful functions of the Pivot Table is its Calculated Fields. Calculated fields are fields that are obtained by some operations on available columns.

Let's understand how to insert Calculated Fields in Pivot Table with one example:
Based on our data, we prepared this report.
0043
Here, we have Sum of Units and Total Cost. I just moved the total column to the Values field and then renamed it to Total Cost. Now I want to know the average price of a unit of each item for each region. And that would be:

Average Price = Total Cost / Total Units

Let's insert a field into the pivot table that shows the average price of each item region wise:

Follow these steps to insert a calculated field into the Pivot Table

    1. Click anywhere on the Pivot Table and go to Analyze Tab 

0044

    1. Click on Fields, Items, & Sets in calculation group.

0045

    1. Click on Calculated Fields.

You will see this input box for your calculating field:
0047

    1. In the name input box write the Average Cost or anything you like, excel won't mind. In the formula input box, write and hit OK.
=Total / Units

You can write this manually from the keyboard or you can double click the field names listed in the Fields area to perform operations.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube