Now that we know how to create a Pivot table, Let us look into few advanced aspects of it. Let us find out the Total Sales Amount for the Sales data done by each region in terms of percentage of Total Amount.

Looking at the data, it is clear that we don’t have a Total Sales Column ready in the table. So, we need to find the Total Sales Value by using the Units and Unit Cost columns. For that we need to Insert Calculated Field into the Pivot Table. After inserting a Pivot table, click on Calculated Field from Fields, Items & Sets in Analyze tab.

In the Name section, give Name for the Column in our case, it is Total Sales Amount and then in formula box, select the columns using which the value can be found. This can be done using Insert Field option. In our case, it should be =Sum(Camping,Fitness,Soccer,Baseball,Fishing,Football,Tennis). So, after clicking on Add, the Total Sales Amount column is added in Fields section. Now, our Pivot Table has the required Total Sales Amount field. In a similar way, Calculated Items can also be inserted into the table.

So, now this Total sales Amount should be displayed in terms of percentage of Grand Total. This can be done by navigating to Value Field Settings and selecting Show Values as section.

The show value as drop down has many options. So, depending on the selection. Base Fields can be selected further. In our case, we select % of Grand Total.

So, the resultant values of Pivot Table are displayed as Percentages of Grand total.

Now, by taking a very simple example, let us see the option of inserting a Calculated Item in the Pivot Table. Let us say, Midwest Region has 10% discount while Northeast has 20% and South has 30% discount. So, let us calculate the Discount amount for each region.

In the resultant table, the Discount Amounts are displayed for each region.

All these options and selections are in Microsoft Excel 2013. In 2010, Calculated Fields and Calculated Items and can be selected from Fields, Items & Sets tab in Options ribbon.

Pivot Table with data from Multiple Sheets

Well, we now know how to create a Pivot table with data from a single sheet. What if data from different sheets in a workbook are to be summarised into a single table? Let us look into the process of creating a Pivot table with data from Multiple Sheets.

Let us find out the Total Sales Price done by each Sales Rep from the following data. After looking at the data, it is apparent that the entire data we need is not in single sheet.

It is better to give a name to tables so that any uncertainty can be avoided. After selecting Pivot table option from Insert tab and giving the range or Table name, we check ADD THIS DATA TO DATA MODEL selection.

Now, another sheet is opened with empty Pivot Table and Field List pane. In order to establish a connection between the two tables, Open Relationships from Analyze tab.

After selecting New from the Manage Relationships window, edit the Relationship by entering the table details and the column that is common in both tables. First, select the table that contains the field which we need to find out first. Then in the Column section we select the column that is common in both the tables. In a similar way, we enter details for the second table.

After entering table details in Edit Relationship, and clicking on Ok, Manage Relationships window is displayed again. We can see that LookUp is applied on the tables.

Now, select All from Pivot Field List pane and select the fields that are required. In our case, we select Total from Table 13 and Rep from Table 3.

Our Pivot table uses fields from both the two tables and gives a clear understanding of the data. Similarly, Pivot Table for data in more than two sheets can be created. But care should be taken that the order of the Relationship should be given carefully.

In Excel 2010, we cannot give the relationship among the tables as in Excel 2013 but still we can consolidate them into single Pivot table in few steps. By entering ALT + D and then P then it will lead us from step1 in following way.

In the step 1, we select Multiple Consolidation Ranges after that step, we select for Fields in Step 2a and then we give the ranges in the Step2b from all the sheets. On entering the ranges, Our Pivot Table will be created in Existing Worksheet or New Worksheet based on our selection in Step3.

Sources:

Book: Microsoft Excel 2013, Data Analysis & Business Modeling, Wayne L Winston

https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576?ui=en-US&rs=en-US&ad=US

Compiled By:

Varun N

Varun is a B.Tech (Mechanical). Currently He is working as Analyst Intern with NikhilGuru Consulting Analytics Service LLP (Nikhil Analytics), Bangalore.