Introduction to Pivot Tables – Part 2

Hi and welcome to this second blog post, of a three-part series, introducing Excel Pivot Tables. In the first blog post, I introduced you to the basics of making a Pivot Table. Hopefully, the first blog post had shed some light on what Pivot Tables are all about and given you some confidence to have a go at making your own. If you haven’t read the first blog post in this series, then I suggest you do first by clicking here.

In this second blog post, we’ll take a look at what you can do when you have multiple fields in one area of the Pivot Table Builder menu, filtering and some of the different ways you can calculate and summarize your data. These techniques will enable you to manipulate your data and extract information quickly.

Multiple Fields in the Same Area

Using the spreadsheet that we had created in the first blog post, we are going to look at what happens when you have two fields, in the same area, in the Pivot Table Builder menu.

Rows and Columns

Rows

Let’s start with the Rows and Columns areas. The Pivot Table, in the first blog post, had the following fields in the following areas of the Pivot Table Builder menu.

AreaField
FiltersMonth
ColumnsDevices
RowSalesperson
ValuesValue

Resulting in a PivotTable like the image below.

Pivot Table Example

This time, we are going to drag the Month field into the Row area of the Pivot Table Builder menu. It is important the Month field sits below the Salesperson field in the Row area of Pivot Table Builder menu, like the image below.

Pivot Table Builder menu - Rows

The PivotTable Builder menu should now have the following fields in the following areas.

AreaField
Filters
ColumnsDevices
RowSalesperson, Month
ValuesValue

The resulting Pivot Table should look like the image below. The rows are broken down by Salesperson first, with a summary of the sales made by the Salesperson for all months. Then under each Salesperson is a further breakdown of the sales that Salesperson did by month. This second breakdown is repeated for each Salesperson.

Pivot Table Salesperson Month Breakdown

Now to revisit my earlier comment about placing the Month field under the Salesperson field in the Row area of the Pivot Table Builder menu. It’s not that you can’t do this, you can, but the result will be different. If you drag the Salesperson field below the Month field, you get an initial breakdown by Month, with summarized values of sales for that month. Then there will a secondary breakdown of sales by salesperson for the month. Again, this secondary breakdown is repeated for each month. See the image below.

Pivot Table Month - Salesperson Breakdown

See how quick that was to get two different views of the same data.

You might have noticed that when you dragged the Salesperson field to the Rows area, the filter drop-down disappeared. Well, it’s still there, but now it is part of the Pivot Table. If you click on the areas highlighted in the image below, you will find the filters for both the rows (Month and Salesperson) and columns (Device).

Row and Column Filters

Column

Similar to the rows example above, drag the Salesperson field to the Columns area below the Devices field as shown in the image below.

Pivot Table Builder menu - Columns

The PivotTable Builder menu should now have the following fields in the following areas:

AreaField
Filters
ColumnsDevices, Salesperson
RowMonth
ValuesValue

This time, the second level breakdown is applied to the columns, not the rows. You now have an initial breakdown by device, then a secondary breakdown by the salesperson. The sales are summarized in a column to the right of each device breakdown, as shown in the image below.

/Users/lee/OneDrive/Documents/Business/Excelitch/Images/Blog Posts/P014/Pivot Table Device - Salesperson Column Breakdown.png

Personally, I prefer to use rows wherever possible. I think it looks cleaner and easier to read.

One final note on having multiple fields in the Rows or Columns areas. You will notice from the image below (and the previous examples), a little symbol next to each of the initial fields.

Pivot Table - Expand Collapse Secondary Breakdown

By clicking this symbol, it allows you to collapse and expand the secondary breakdown of fields as shown in the image below.

Pivot Table Collapsed

Filters

For filters, we are going to revisit our Original Data worksheet, add some new data to column E, and add a header called Units Sold. This column will represent the number of units sold by a Salesperson in a specific month for a specific device to achieve the Value of Sales. When the column has been added to the Original Data worksheet, it should look like the image below.

/Users/lee/OneDrive/Documents/Business/Excelitch/Images/Blog Posts/P014/Units Sold Column.png

Now we need to add this new column to our Pivot Table. Click anywhere in the PivotTable, but don’t make any changes yet. You might have already noticed that when you created a PivotTable, two new tabs appeared on the Ribbon, “PivotTable Analyze” and “Design.” We’re going to cover the “Design” tab in the final blog post in this series. So, for now, click the “PivotTable Analyze” tab. The “PivotTable Analyze” tab provides various features for working with your Pivot Table. This is where we will add the new column to our Pivot Table. Start by clicking the “Change Data Source” button as shown in the image below.

Pivot Table Change Data Source

 

After you have clicked the “Change Data Source” button, a menu called “Change PivotTable Data Source” should appear.

Change Pivot Table Data Source

Click in the Location text box (highlighted in the image above) and change the cell reference from ‘Original Data’!$A$1:$D$49 to ‘Original Data’!$A$1:$E$49. This will add the new column to the list of available fields in the Pivot Table. Now, drag the fields to the PivotTable Builder menu areas, so they match the table below.

AreaField
FiltersDevices
ColumnsSalesperson, Units Sold
RowMonth
ValuesValue

Once this is done, your Pivot Table should look like the image below. This time, the Pivot Table has two filters allowing you to filter by the Units Sold field, Device field, or both.

Pivot Table - Two Filters Devices and Units Sold

Values

This time, we are going to have two fields in the Values area of the PivotTable Builder menu. I think the Values area trips the most people up when using Pivot Tables. So for this example, we’re going to stick to something straight forward. We’ll start by using the table of data that we have just modified in the previous section with the new Units Sold field. Drag the Units Sold field below the Value field in the Values area of the PivotTable Builder menu, place the Salesperson field in the Rows area and the Month field in the Columns area like the image below.

Pivot Table Device - Value and Units Sold Values

When you’ve done this, the PivotTable should now have a column under each month for the Sum of Value and a new column for the Sum of Units Sold. From this Pivot Table, we can see the value of sales for each sales person and the number of units they sold for each month.

Pivot Table - Sum 0f Value and Sum of Units Sold

Filtering and Slicing

We’ve already covered a lot of the filtering functionality for a Pivot Table above and in the previous post. However, there is an additional filter option worth mentioning.

Slicers

One of the more recent features added to Excel are “Slicers.” Slicers work in a similar manner to filters. However, instead of a using a drop-down list to select and deselect the items, you have a graphical tool called a slicer.

To add Slicers to your Pivot Table, click in the Pivot Table, then click the PivotTable Analyze tab on the ribbon, and finally, click the “Insert Slicer” button as shown in the image below.

Insert Slicer button

Once you’ve clicked the “Insert Slicer” button, you should see a pop-up menu called “Insert Slicer.” From this menu, you can select from the available Slicers. Using our previous example, we can select Slicers for Sales Person and Month fields like the image below.

Insert Slicer menu

Once you click the OK button on the “Insert Slicer” pop-up menu, two Slicer controls should appear on your worksheet. You can move the Slicer to an appropriate place. When you click the buttons on the Slicer control, the Pivot Table will alter in the same way you select items from a filter drop-down menu. Using our previous example, select “Joan” from the Sales Person Slicer and “July” from the Month Slicer. The Pivot Table will now look like the image below.

Pivot Table Slicers July and Joan

Note: to select multiple items in a Slicer, press and hold the Control key on Windows or the Command key on the Mac and select the items you want. To clear the selection, click the icon, highlighted in the images below.

Clear Slicer symbol

Summarizing your Data

By default, the Pivot Table has been summarizing the data, using the SUM function. However, you can summarize the data, using other functions, described in the table below.

FunctionDescription
SumThe sum of the values. This is the default function for numeric values.
CountThe number of values. The Count summary function works the same as the COUNTA worksheet function. Count is the default function for values other than numbers.
AverageThe average of the values (arithmetic mean).
MaxDisplays the largest value.
MinDisplays the smallest value.
ProductThe product of the values.
Count NumsThe number of values that are numbers. The Count Nums summary function works the same as the COUNT worksheet function. Ignores non-numeric values
StDevAn estimate of the standard deviation of a population, where the sample is a subset of the entire population.
StDevpThe standard deviation of a population, where the population is all of the values to be summarized.
VarAn estimate of the variance of a population, where the sample is a subset of the entire population.
VarpThe variance of a population, where the population is all of the values to be summarized.

The type of summary function you choose depends largely on the type of data you have. To access these different summary functions, select the field you’re interested in and select Field Settings. This can be done by clicking the PivotTable Analyze tab as shown in the image below.

Field Settings Button - Windows

Field Settings Button – Windows

Field Settings Button - Mac

Field Settings Button – Mac

This can also be done by clicking on the field in the PivotTable itself, right clicking, from the pop-up menu that appears, and selecting “Field Setting”. Either way, you should see a menu called “PivotTable Field” on the Mac, or “Field Setting” on Windows. The menu should look like the images below for Mac and Windows.

Field Settings Menu - Windows

Field Settings Menu – Windows

Pivot Table Field Menu - Mac

Pivot Table Field Menu – Mac

Let’s take a look at a few different options that might make sense when working with the data that has been used in the example Pivot Tables so far. 

AVERAGE

The Average function returns the Average value for values in the range. This can apply to Rows and Columns as shown in the image below. Using our example, we click on the PivotTable, then right click and choose “Field Setting” from the pop-up menu.

When the “PivotTable Field” menu on the Mac, or “Field Setting” menu on Windows appears, make sure that the source field is “Value”, then select the Average from the Summarize Text box, and click OK.

Select Average Function

I also suggest removing any numbers after the decimal point, otherwise, you’ll end up with long, ugly numbers. If you’re not sure how to do this, click here.

Change Decimal Places

When this is done, the Pivot Table should look like the image below. Now, the row and column showing the summary info are displaying the average value rather than the sum of all values. Annoyingly, as shown in the image above, Excel doesn’t update the summary row and column headings, so we have to go in and edit these manually. Once these headings have been changed, our Pivot Table should look like the image below.

Pivot Table - Average

Please note that the Average function calculates the arithmetic mean. If you’re not familiar with the arithmetic mean is, click this  link for more information

MIN

The MIN function returns the minimum value from a range. This can apply to rows and columns as shown in the image below. Using our example again, open the “PivotTable Field” menu on the Mac, or “Field Setting” menu on Windows. Make sure that the source field is “Value,” select “Min” from the Summarize Text box, then click OK.

Select Min Function

Once this is done, the Pivot Table should look like the image below. Now, the rows and columns showing the summary info are displaying the Minimum value. Again, annoyingly, as shown in the image above, Excel doesn’t update the summary rows and columns headings; go in and edit these manually. Once these headings have been changed, our Pivot Table should look like the image below.

Pivot Table Min Function

MAX

Finally, the MAX function. The MAX function returns the maximum value from a range of values, and, no surprise, this can be applied to rows and columns as well. Using our example again, open the “PivotTable Field” menu on the Mac, or “Field Setting” menu on Windows. Make sure that the source field is “Value,” select “Max” from the Summarize Text box, then click OK.

Select Max Function

Once this is done, the Pivot Table should look like the image below. Now, the row and column showing the summary info are displaying the maximum value. Don’t forget to update the summary row and column headings. Once these headings have been changed, our Pivot Table should look like the image below.

Pivot Table Max Value

That’s it for this blog post. I hope you found it useful and have learned something. In the next blog post, we’ll take a look a closer look at the “PivotTable Design” and “PivotTable Analyze” tabs, and learn how to format our Pivot Tables and Slicers so they look more professional.

If you want to learn more about Pivot Tables, I recommend myexcelonline.com Xtreme Pivot Course. This is the most complete course on Pivot Tables I have found on the Internet. Click here to learn more.

To get a copy of the example spreadsheet used in this blog post, sign-up for our newsletter below.

728x90

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit