Author avatar

Chhaya Wagmi

Exploring Data with PivotTables

Chhaya Wagmi

  • Jan 28, 2020
  • 25 Min read
  • 97 Views
  • Jan 28, 2020
  • 25 Min read
  • 97 Views
Business Professional
Microsoft Excel

Introduction

A PivotTable constructed in Excel becomes a powerful tool to calculate, summarize, and analyze data, letting you see comparisons, patterns, and trends in your data.

For example, say you have a huge amount of transaction data for a shop, and you are asked to make a report along with a dashboard to show the transactions made every year for last twenty years. It would take days to complete the task manually, but with the help of PivotTable and pivot charts, it would take barely a few minutes, as they are able to powerfully analyze large volumes of data in a few clicks and minimize the chance of errors.

In this guide we will cover the following features associated with Pivot tables:

  • Creating a PivotTable
  • Managing Data in PivotTable
  • Formulas in PivotTables
  • Exploring Data with PivotChart

Creating a PivotTable

Before we start learning about the creation of PivotTables, it is important to understand the data format on which they are built. A PivotTable requires tabular data and treats blank spaces separately. A complete blank row is treated as a blank() value, however, if there is at least one data field available in a row, then the rest of the row data field is considered blank and the row is treated just like other rows.

To understand how blank spaces are treated in a pivot table, consider these three cases:

Case 1: No Blank Values

Consider the following table where 24 data rows are available along with four columns.

MonthQuarterShopProfit in USD
Jan1Nikodale Furniture2500
Feb1Nikodale Furniture3888
Mar1Nikodale Furniture5699
Apr2Nikodale Furniture8875
May2Nikodale Furniture6588
Jun2Nikodale Furniture2233
Jul3Nikodale Furniture6630
Aug3Nikodale Furniture1855
Sep3Nikodale Furniture3555
Oct4Nikodale Furniture8795
Nov4Nikodale Furniture1211
Dec4Nikodale Furniture2222
Jan1Samuel Arts6554
Feb1Samuel Arts6899
Mar1Samuel Arts7845
Apr2Samuel Arts9555
May2Samuel Arts7588
Jun2Samuel Arts6558
Jul3Samuel Arts5224
Aug3Samuel Arts6554
Sep3Samuel Arts7558
Oct4Samuel Arts4555
Nov4Samuel Arts3555
Dec4Samuel Arts1555

To create a PivotTable on this data, first arrange the data in the form of a table using the following hierarchy:

Menu bar > Home > Format as Table > Select a table format you like

Imgur

Once you get the data in a tabular form, follow this hierarchy to create a PivotTable in a new worksheet:

Menu bar > Insert > PivotTable > Press OK

Imgur

This opens a new blank worksheet, and you can observe PivotTable Fields on the right side of the sheet. This box by default has two areas, Field Section and stacked Areas Section. The Field Section consists of all the column names, and the Areas Section has four sub-sections: Filters, Columns, Rows and Values. You can drag a column name from the Field Section and drop it into any of the Areas Section.

If you proceed with the following structure:

Imgur

You will receive the following PivotTable:

Sum of Profit in USD Column Labels
Row Labels

Nikodale FurnitureSamuel ArtsGrand Total
1120872129833385
2176962370141397
3120401933631376
412228966521893
Grand Total5405174000128051

As you can observe, there were no missing values in the original table, hence there are no blank values in the PivotTable.

Case 2: Missing Row

Consider a case in which the following row is missing from the table (replaced by blank values):

MonthQuarterShopProfit in USD
Sep3Nikodale Furniture3555

Now, if we try to build a PivotTable on such table, we get the following result:

Sum of Profit in USD Column Labels
Row Labels

Nikodale FurnitureSamuel Arts(blank)Grand Total
1120872129833385
2176962370141397
384851933627821
412228966521893
(blank)
Grand Total5049674000124496

Here, blank() is considered both as a separate row and column.

Case 3: Missing Value

What if only few values are missing rather than a complete record? To understand the PivotTable result in such a case, let's remove the profit in the month of September from Nikodale Furniture:

MonthQuarterShopProfit in USD
Sep3Nikodale Furniture

Building a PivotTable on this data results in the following:

Sum of Profit in USD Column Labels
Row Labels

Nikodale FurnitureSamuel ArtsGrand Total
1120872129833385
2176962370141397
384851933631376
412228966521893
Grand Total5405174000128051

This results in a similar table as in Case 1 with a difference in the value of Quarter 3 profit for Nikoldale Furniture.

These three cases illustrate how missing row(s) or missing value(s) in the data can impact the corresponding PivotTable.

Creating a Recommended Table

When you click on the Insert tab in the Menu bar, you can see the Recommended PivotTable option adjacent to the PivotTable option. Clicking on this option pops up a new dialog box that consists of most of the common PivotTables that can be built using the provided data. For cases in which there are no missing values in the data, the recommended PivotTables are shown below:

Imgur

Managing Data in PivotTables

So far, we have learn to create a PivotTable. Now let's learn about managing its items using this table:

YearQuarterNikodale FurnitureSamuel Arts
2019145482500
2019275483888
2019321545699
2019488758875
2018165884578
2018222334221
2018366306584
2018418551452

First, we build a PivotTable out of this table keeping the following Pivot structure:

  • Rows: Year followed by Quarter
  • Values: Sum of Nikodale Furniture followed by Sum of Samuel Arts

This leads us to the given result:

Row LabelsSum of Nikodale FurnitureSum of Samuel Arts
2018
165884578
222334221
366306584
418551452
2019
145482500
275483888
321545699
488758875
Grand Total4043137797

Subtotal of Group

As you can observe, the PivotTable has two subsections (2018 and 2019) with a final Grand Total. To get the total of each section separately, we can follow these steps:

Menu bar > Design > Subtotal > Choose any option (here, Show all Subtotals at the Bottom of Group)

Row LabelsSum of Nikodale FurnitureSum of Samuel Arts
2018
165884578
222334221
366306584
418551452
2018 Total1730616835
2019
145482500
275483888
321545699
488758875
2019 Total2312520962
Grand Total4043137797

You can also control the display of Grand Total from the same menu.

Controlling Field Settings

You may have observed that when we drop a field into the Values section, it automatically calls the Sum function. However, we can control what function needs to be implemented on a particular value. Let's try to change the following:

  • Nikodale Furniture: From Sum to Maximum value
  • Samuel Arts: From Sum to Average value

To accomplish this change, click on Sum of Nikodale Furniture under the Values section and select the Value Field Settings... option.

Imgur

Now, click on Max and press OK.

Imgur

Perform a similar operation with Samuel Arts, but this time select Average. Once you've done both operations, you will receive the following PivotTable:

Row LabelsMax of Nikodale FurnitureAverage of Samuel Arts
2018
165884578
222334221
366306584
418551452
2018 Total66304208.75
2019
145482500
275483888
321545699
488758875
2019 Total88755240.5
Grand Total88754724.625

Filtering

Just as we can apply a filter on a regular table in Excel, we can also apply a similar filter on a PivotTable. For instance, consider the table above. If you click on any year's quarter cell (1, 2, 3, or 4) and then click on the dropdown button next to the Row Labels column, you'll find an option to select all or specific quarters. Similarly, if you click on a cell with year value 2018 or 2019, the dropdown values change from quarter to year. The images below represent the dropdown box in each case:

Imgur

Imgur

Instead of clicking on a cell to select a field (Quarter or Year), you can also choose them right from the Select Field option available in the dropdown box.

The PivotTable below shows the data for only quarters 1 and 2 in 2018:

Row LabelsMax of Nikodale FurnitureAverage of Samuel Arts
2018
165884578
222334221
2018 Total65884399.5
Grand Total65884399.5

Layout Transformation

In the above PivotTables, you may have observed that there is no separate boundary to distinguish a group from another (here, 2018 data from 2019 data). Plus, the year and quarter appear in a single column. PivotTables incude options under the Design tab named Report Layout and Blank Rows to tackle these issues. Let's learn them step by step.

Adding and Removing Blank Rows in a PivotTable

Consider a PivotTable with two groups as shown below:

Row LabelsSum of Nikodale FurnitureSum of Samuel Arts
2018
165884578
222334221
366306584
418551452
2019
145482500
275483888
321545699
488758875
Grand Total4043137797

Notice that we can improve the visual of this table by adding a blank row below 2019 year row. To achieve this, follow these steps:

Menu bar > Design > Blank Rows > Insert Blank Line After Each Item

This operation results in the following PivotTable:

Row LabelsSum of Nikodale FurnitureSum of Samuel Arts
2018
165884578
222334221
366306584
418551452
2019
145482500
275483888
321545699
488758875
Grand Total4043137797

To remove the blank row, follow these steps:

Menu bar > Design > Blank Rows > Remove Blank Line After Each Item

Changing Table Layout

The Report Layout option under the Design tab has the following options:

Imgur

By default, the PivotTable comes with the first option, Show in Compact Form. Let's use another format, Show in Outline Form, to separate Year from Quarter, which results in the following PivotTable:

YearQuarterSum of Nikodale FurnitureSum of Samuel Arts
20181730616835
165884578
222334221
366306584
418551452
20192312520962
145482500
275483888
321545699
488758875
Grand Total4043137797

Now you can test rest of the options and observe the difference in the table.

Formulas in PivotTables

In this section, we will learn about two formulas:

  • GETPIVOTDATA
  • Calculated Field

GETPIVOTDATA

Sometimes you start with a PivotTable structure, but the structure may change depending upon the requirements. Therefore, a function named GETPIVOTDATA is suggested in scenarios where you want to keep the information regardless of changes in the PivotTable structure.

For instance, consider the following PivotTable:

Row LabelsMax of Nikodale FurnitureAverage of Samuel Arts
2018
165884578
366306584
2018 Total65885581
Grand Total65885581

Here, if you want to keep the maximum profit value ($6630) of Nikodale Furniture in Quarter 3 of year 2018 in a separate cell regardless of changes in the PivotTable structure, then proceed with the following steps:

  • Click on a new cell and write =.
  • Next, click on the cell with value 6630. This will result in the following formula in the new cell, which you have selected: =GETPIVOTDATA("Max of Nikodale Furniture",$A$3,"Year",2018,"Quarter",3).
  • Press Enter, which leaves you with the value 6630 in the cell with the formula.

So far, we are able to retrieve the value using the formula. Now let's change the structure of the table by adding Quarter 2 into the table, which changes the location of the cell with value 6630.

Row LabelsMax of Nikodale FurnitureAverage of Samuel Arts
2018
165884578
222334221
366306584
2018 Total65885127.67
Grand Total65885127.67

As you can see, the value has remain unchanged!

Calculated Field

Just as we can add a new column to an Excel table by connecting them with a formula, in a similar fashion we can add a new field to the PivotTable. Consider the PivotTable below:

Row LabelsMax of Nikodale FurnitureMax of Samuel Arts
2018
165884578
222334221
366306584
418551452
2018 Total66306584
2019
145482500
275483888
321545699
488758875
2019 Total88758875
Grand Total88758875

So to add a new column that represents the difference between columns Max of Nikodale Furniture and Max of Samuel Arts, use the following steps:

Menu bar > Analyze > Fields, Items & Sets > Calculated Fields...

In the Insert Calculated Field dialog box, provide a new column name and suitable formula, as shown in the image below:

Imgur

This results in the following PivotTable:

Row LabelsMax of Nikodale FurnitureMax of Samuel ArtsSum of NikToSam
2018
1658845782010
222334221-1988
36630658446
418551452403
2018 Total66306584471
2019
1454825002048
2754838883660
321545699-3545
4887588750
2019 Total887588752163
Grand Total887588752634

Exploring Data with PivotChart

We can also visualize the data available in a PivotTable using PivotChart, provided under the Insert menu. We build a stacked line chart with markers on the following PivotTable:

Row LabelsMax of Nikodale FurnitureMax of Samuel Arts
2018
165884578
222334221
366306584
418551452
2018 Total66306584
2019
145482500
275483888
321545699
488758875
2019 Total88758875
Grand Total88758875

Imgur

From the above chart, it is easy to infer that the profit of Samuel Arts is always higher than the profit earned by Nikodale Furniture in all the quarters of 2018 and 2019.

Conclusion

In this guide, you have learned about the basics of creating a PivotTable, managing the items within it, and working with functions and charts.

To get more in-depth working knowledge on this subject, you can take the Exploring Data with PivotTables course on PluralSight.

2