Exploring Data with PivotTables

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

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:

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

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

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

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:

You will receive the following PivotTable:

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

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

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

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

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:

Building a PivotTable on this data results in the following:

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.

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:

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

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:

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)

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

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.

Now, click on Max and press OK.

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

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:

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:

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.

Consider a PivotTable with two groups as shown below:

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:

To remove the blank row, follow these steps:

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

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

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:

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

In this section, we will learn about two formulas:

• GETPIVOTDATA
• Calculated Field

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:

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.

As you can see, the value has remain unchanged!

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:

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:

This results in the following PivotTable:

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:

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.

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.