Introduction to Pivot Tables – Part 3

728x90

Welcome to this final blog post in a three-part series, introducing Pivot Tables. If you haven’t already, I would suggest that you check out parts one and two first.

In this final blog post, we’ll look at the Pivot Design tab, the Pivot Table Analyze tab, sorting data, and formatting Pivot Tables and have a quick look at formatting Slicers.

By the end, you’ll be able to make your Pivot Tables look more professional.

Sorting

It is possible to sort a Pivot Table in a similar to sorting a normal worksheet of data. You select the field by which you want to sort and, then, on the Data tab in the Ribbon, click the AZ↓ or the ZA↓ sort buttons as shown in the image below.

AZ Buttons - Windows

AZ Buttons – Windows

AZ Buttons - Mac

AZ Buttons – Mac

These buttons do more than sort alphabetically; they can also sort numerically, by date order, etc.

Let’s take a look at a quick example. Using the Pivot Table in the image below, we can sort the rows alphabetically by the SalesPerson name in column A.

Pivot Table Unsorted

First, click on any of the SalesPersons names in the Pivot Table and, then, click on AZ↓.

Sorted PivotTable

When we’ve finished, the Pivot Table will look like the image above; note the rows are now sorted alphabetically by SalesPerson’s name.

We can even do this when we have more than one field in the Rows section of the PivotTable menu.

Let’s take a look at another example. This time, we will have both the SalesPerson and Month fields in the Rows section of the PivotTable menu. The Rows are ordered first by Month, then by SalesPerson name.Pivot Table - Month then SalesPerson

Now click on any Month field in the PivotTable and click theAZ↓.When you do this, the PivotTable should look like the image below.

PivotTable Month AZ

Notice that the Months rows are now sorted in month order, but no change to the SalesPerson rows. If we wanted to sort the SalesPerson field we would have to click on the SalesPerson field first, then click how we want it sorting. Let’s going ahead and sort the SalesPerson field in alphabetical order. First, click a SalesPerson’s name, then clicking AZ↓.

PivotTable SalesPerson AZ

PivotTable Analyze Tab

The “PivotTable Analyze” tab is enabled when you click in a Pivot Table. The “PivotTable Analyze” tab gives you access to several commands specific to PivotTables. Most of these commands can also be accessed by right-clicking on a Pivot Table as well.

PivotTable Analyze Tab

The following are just some of the commands that can be found on the “PivotTable Analyze” tab.

PivotTable Name

You can give a Pivot Table a meaningful name using the “PivotTable Name” textbox. This can be helpful when you want to reference the Pivot Table from elsewhere in a workbook.

PivotTable Name Textbox

Options

The “Options” button opens up a “PivotTable Options” pop-up menu. The Windows and Mac editions of Excel use menus that look a little different, so I will focus on a few useful options that are common to both editions.

Options Button - Mac

Options Button – Mac

Options Button - Windows

Options Button – Windows

First, the option to populate an empty cell in a PivotTable with a value. This can be useful when working with numbers. Rather than having a blank cell, you could populate it with a zero, for instance. To access this option, click the “Layout & Format” tab on the Windows PivotTable Menu or the “Display” tab on the Mac PivotTable Menu.

Empty Cells As - Windows

Empty Cells As – Windows

Empty Cells As - Mac

Empty Cells As – Mac

The second useful option is the ability to refresh the PivotTable whenever the workbook is opened. This is useful if the workbook is updated with new data on a regular basis. To access this option, click the “Data” tab on both the Windows and Mac editions of the PivotTable Menu.

Refresh Data - Windows

Refresh Data - Mac

Refresh Data – Mac

Finally, the ability to “Preserve cell formatting on update.” PivotTables can be quite annoying as they don’t always keep the formatting you have taken the time to apply. By selecting this option, the formatting is preserved after the PivotTable is updated. To access this option, click the “Layout & Format” tab on Windows or the “Layout” tab on the Mac.

Preserve Cell Formatting - Windows

Preserve Cell Formatting – Windows

Preserve Cell Formatting - Mac

Expand Field and Collapse Field

These two options, “Expand Field” and “Collapse Field,” enable you to expand or collapse rows or columns when you have two or more fields in either the Rows or Columns section of the PivotTable builder menu.

Expand and Collapse Butttons - Windows

Expand and Collapse Butttons – Windows

Expand and Collapse Butttons - Mac

To explain this a little better, let’s take a look at the Pivot Table below. Notice how we have the rows broken down by Month, Devices, and SalesPerson.

PovitTable Month - Device - SalesPerson

If we click on any of the fields in column A and then click the “Collapse Field” button, the Salesperson rows are collapsed.

SalePerson Rows Collapsed

If we click the “Collapse Field” button again, the Device rows are collapsed.

Devices Rows Collapsed

The reverse happens when you click the “Expand Field” command.

Refresh

The “Refresh” button is useful because the PivotTable doesn’t automatically update when you add new data to the Pivot Table data sources. For instance, in our example, if we found that some of the values used in the “Original Data” worksheet were incorrect and updated them, the Pivot Table wouldn’t automatically be updated.

Refresh Button

For example, if we go back to the worksheet with the source data and rename Joan to Gill, our Pivot Table isn’t updated.

Source Data Before

Source Data Before Update

Source Data After Update

Source Data After Update

This is the PivotTable after the source data has been updated.

Pivot Table Still Showing Joan

No change, so, we need to click the “Refresh” button to update the data.

Pivot Table Updated

Pivot Table Updated

Change Data Source

The “Change Data Source” button has previously been discussed in part two of this series and therefore won’t be covered here.

Clear, Select, and Move PivotTable

We’ll deal with the Clear, Select, and Move PivotTable buttons in one section.

Clear - Select - Move PivotTable Buttons

Clear

Two main commands can be accessed using the Clear button, “Clear All” and “Clear Filters.”

Clear Options

Clear All

The “Clear All” button effectively removes all fields from all sections of the PivotTable builder menu. It doesn’t delete the PivotTable from the worksheet; it’s still there, it’s just empty. See the before and after images below.

Before Clear All Button Pressed

Before Clear All Button Is Pressed

And after the Clear All button is pressed.

After Clear All Button Is Pressed

After Clear All Button Is Pressed

Clear Filters

The Clear Filters command pretty much does what it says on the tin. If you have any filters applied to the Pivot Table, it resets your filters. The same action is applied to Slicers. Remember that a Slicer is another method of filtering your Pivot Table.

The image below shows an example Pivot Table with months filtered to only show values for June.

Pivot Table Filtered Just June V2

Click the “Clear Filters” button.

Clear Filters

The filter is removed and values for July and August are displayed.

PivotTable No Filter

Select

Next, we’ll look at the Select button. Four commands can be accessed here: “Labels & Values,” “Values,” “Labels,” and “Entire PivotTable.”

Select Button

The four commands allow you to make changes to large sections of the Pivot Table at once. As an example, we’ll make some formatting changes to the Pivot Table below with the help of these commands.

Select Pivot Table

First, click on the PivotTable, then click the “Select” button, then click the “Entire PivotTable” button. When you’ve done this, the entire Pivot Table should be highlighted like the image below.

Select Entire Pivot Table

Notice that the entire Pivot Table has been selected. Now, with the entire Pivot Table still selected, we want to turn on the option to select sections of the Pivot Table. To do this, go back to the “Select” button and click the “Enable Selection” button.

Enable Selection

Then, a tick will appear next to the “Enable Selection” button. The buttons “Labels”, “Values,” and “Labels & Value” should now be available.

Tick and Buttons

 

OK, let’s make some changes to our PivotTable. Click the “Select” button and, then, the “Labels” button. When this is done, only the labels should be highlighted on the Pivot Table.

Lables Highlighted

Now, go to the “Home” tab on the ribbon, and click the underline button.

PivotTable Underlined

When you’ve done this, all the labels in the highlighted sections of the Pivot Table shown in the image above will be underlined. Just as easily, we could have made changes to the font or made the text bold.

Next, we’re going to make change the values the Pivot Table. If you’ve clicked outside the Pivot Table, and it’s no longer highlighted, click on the Pivot Table again; go back the “PivotTable Analyze” tab; click on the “Select” button; click on the “Entire Selection” button and, then, the “Values” button. If the Pivot Table is still highlighted, go back the “PivotTable Analyze” tab; click on the “Select” button and, then, the “Values” button. The PivotTable should now look like the image below.

Values Highlighted

This time, we’re going to change the formatting of the values to Currency and remove all the numbers after the decimal points. To do this, go to the home tab and select “Currency” from the “Number Format” drop-down menu and select Currency

Select Currency

Click “Decrease Decimal” button on Home tab.

Decrease Decimal V2

 

You might need to click the “Decrease Decimal” button a couple of times. The Pivot Table should now look like the image below.

Formatted as Currency

Finally, we’re going to make a change to the entire Pivot Table. Again, if you’ve clicked outside the Pivot Table and it’s no longer highlighted, click on the Pivot Table, go back to the “PivotTable Analyze” tab, click the “Select” button, and click the “Entire Selection” button. If the Pivot Table is still highlighted, just go back the “PivotTable Analyze” tab, click the “Select” button, and then the “Labels & Values” button.

Clicking the “Entire Selection” and “Labels & Values” achieves the same result. The Pivot Table should now look like the image below.

Labels and Values

 

This time, we’re going to change the Font. To do this, we go to the “Home” tab and click the “Font” drop-down menu and select another font, “Arial,” in this instance.

Select Arial font

Once we’ve done, the entire Pivot Table should now be using the Arial font like the image below.

Formated Pivot Table

Move PivotTable

The “Move PivotTable” button is another fairly straight-forward command.

Move PivotTable Button

Click on your Pivot Table, click the “Move PivotTable” button, and a menu will pop up called “Move PivotTable” like the images below.

Move Pivot Table

From this menu, you have the option to move the Pivot Table to a new worksheet or a different location on the current worksheet. If you choose “New worksheet” and click OK, a new worksheet is created, and the Pivot Table is moved to this new worksheet. If you choose “Existing worksheet,” click in the “Location:” text-box and select the cell where you want the top left-hand corner of the Pivot Table to be located, then click OK.

Field List, +/- Buttons, and Field Headers

Field List

If you click the “Field List” button, this toggles the display of the “PivotTable Builder” menu on and off.

Field List

+/- Buttons

If you click the “+/- Buttons” button, this will toggle the display of the icons (in the images below) that allow you to expand and collapse the rows and columns directly in the Pivot Table.

+- Buttons

Clicking the “+/- Buttons” button, toggles the icons highlited in the image below on and off.

+- icons

If the icons aren’t displayed, it is still possible to expand and collapse the rows and columns by using the “Expand Field” and “Collapse Field” buttons on the ribbon mentioned earlier in this post.

Field Headers

The “Field Headers” button toggles are between showing and hiding the Pivot Table field headers.

Field Headers button

The image below if a Pivot Table with the row and column headers turned off.

Row Headers Hidden

Personally, I prefer having them turned off. It does remove the filters from the rows and column headers, but this can be resolved by using slicers, which look much better.

Pivot Table Design Tab

The “Design” tab, like the “PivotTable Analyze” tab, is displayed when you click on a Pivot Table. The “Design” is all about how the Pivot Table looks and is formatted.

Design Tab

PivotTable Styles

The “PivotTable Styles” window allows you to select from numerous different color combinations and shading for your Pivot Table. Initially, the “PivotTable Styles” window only shows one row of styles like the image below.

Pivot Table Styles

However, if you click the down arrow, as shown in the image below, the window expands, and you can access several more styles.

Expanded Pivot Table Styles Window V2

To change the Pivot Table style, click anywhere in the PivotTable, select the “Design” table, and choose a style from the “PivotTable Styles” window as shown in the images below.

Pivot Table Style

PivotTable Style Options

Row Headers

The “Row Headers” tick-box allows you to toggle highlighting the headers of the fields that have been dragged to the Rows section of the “PivotTable Builder” menu. The effects of enabling this feature can be slightly different depending on what Report Layout form you are using. See the image below for an examples of a Pivot Tables with the row headers turned off, using the “Compact Form” report layout.

Row Headers Turned On

See the image below for an examples of  a Pivot Tables with the row headers turned on using the “Compact Form” report layout.

Row Headers Turned Off

It’s worthwhile to play with toggling the Row Headers on and off with the different Report Layout forms (Compact, Outline and Tabular).

Column Headers

The “Column Headers” tick-box allows you to toggle highlighting the headers for the fields that have been dragged to the Columns section of the “PivotTable Builder” menu. See the image below for examples of a Pivot Table with the column headers turned on.

Column Headers On

See the image below for examples of a Pivot Table with the column headers turned off.

Column Headers Off

Banded Rows

The “Banded Rows” tick-box toggles between having borders above and below each row. See the image below for “Banded Rows” turned on.

Banded Rows On

And with “Banded Rows” turned off.

Banded Rows Off

Banded Columns

The “Banded Columns” tick-box toggles between having borders to the left-hand and right-hand side of each column. See the image below for “Banded Columns” turned on.

Banded Columns On

See the image below for “Banded Columns” turned off.

Banded Columns Off

Subtotals

The “Subtotals” button provides you with four options:

  • Don’t Show Subtotals,
  • Show All Subtotals at the Bottom of Group,
  • Show All Subtotals at the Top of Group,
  • Include Filtered Items in Totals.

There may be instances when you don’t want to see subtotals. If so, then the “Don’t show Subtotals” option is for you. See the example below.

Don't Show Subtotals

Personally, I like to see subtotals at the bottom, so “Show All Subtotals at the Bottom of Group” is my favorite.

Show Subtotals at Bottom

The default view is the “Show All Subtotals at the Top of Group” with subtotals on the same row next to the Field name at the top.

Show Subtotals at Top

We are not going to look at the final option because this requires you to be working with OLAP data sources, which is beyond the scope of this blog post. However, if you would like to learn more about what an OLAP data source is, then please click here.

Grand Totals

The “Grand Totals” button works in much the same way as the “Subtotals” button, but this time, you can toggle between Grand Totals being shown for Rows, Columns, Both, or none at all.

The following image is with the Grand Totals turned off for both Rows and Columns.

Grand Totals Row and Column Off

This image shows the Grand Totals turned on only for Rows.

Grand Totals Row On

This image shows the Grand Totals turned on only for Columns.

Grand Totals Column On

And finally, this image shows the Grand Totals turned on for both Rows and Columns.

Grand Totals Row and Column On

Report Layout

The “Report Layout” button provides you with three different options for the Pivot Table layout, referred to as the “Report Layout.”

The Compact form is the most compact layout for the PivotTable. This helps prevent the data from expanding across the screen and should reduce scrolling. The benefits of this layout are perhaps more obvious when compared to the other two options.

The Outline form adds a new column for every field in the Rows section of the “PivotTable Builder” menu. The images below highlight the difference between the Compact Layout and the Outline Layout. First the “Compact Form.”

Compact Form Pivot Table

And now the “Outline Form.”

Outline Form Pivot Table

Finally, the Tabular Form is similar to the Outline form, but this time, the different field labels for the rows aren’t in their own rows. See the image for and example of the Tabular Form.

Tabular Form Pivot Table

In the Tabular Form you can’t have the subtotals at the top because the labels are on the same row. Therefore, if you choose to show the subtotals, they will always be at the bottom.

You also have the option to toggle between repeating items labels on the “Report Layout” button. This option is available on both the Outline Form and the Tabular Form. The image below shows “Repeat All Item Labels” being used on the Tabular Form.

Repeat Labels

Blank Rows

The “Blank Rows” command allows you to toggle between having a blank row between fields as shown in the images below. In the image below, a blank row has been inserted after each Month section by clicking the “Insert Blank Line After Each Item” button.

Blank Line button

This can be useful to separate sections of the PivotTable, but it also makes the PivotTable larger.

Other Useful Formatting Tips

Change Field Names and Headings

To change field names and headings, you can either click on the field or heading in the Pivot Table and type the new name directly, then click enter.

Change Field Name

Or, you can choose the field by clicking on the field in the Pivot Table and then entering the new field name in the “Active Field:” text box.

Change Field Name Active Field Textbox

Note that the field name will change in the “PivotTable Builder” menu as well.

PivotTable Builder Menu Field Names Updated

One final note is that when you rename a numeric field, it changes its data type to text. This will result in the renamed field being sorted separately from numeric values or from being grouped with numeric items.

Format Cells/Data Type

To format the data type of cell/range of cells in your Pivot Table, click on the cell/range of cells in the PivotTable you are interested in, right click, and click Format Cells.

Right Click Format Cells

From here, you can format the cell/range of cells and data type in the same way you would when normally formatting cells. For more info on formatting cells, see my previous post by clicking here.

Keep in mind that if you change the Data Type, you want to keep it compatible with the source data. So, for instance, changing the date format might make sense, but changing a number to text doesn’t.

Slicer Formatting

When you connect Slicers to your Pivot Tables, and you click on a Slicer, a new tab appears on the Ribbon called “Slicer,” as shown in the image below.

Slicer Tab

The Slicer tab predominantly deals with formatting the Slicer in the same way the Pivot Table “Design” tab predominantly deals with formatting a Pivot Table. From the Slicer tab, you can format the Slicer colors, its size and alignment, and the number of columns of buttons that are displayed in the Slicer. You can also access the “Slicer Setting” and “Report Connections,” which aren’t formatting options.

We’re not going to go into a great deal of detail for the Report Connections button other than to say it enables you to define which Pivot Table a Slicer is connected to. It is possible to connect a Slicer to more than one PivotTable, which is really more of an advanced topic.

Slicer Settings

When you click on the “Slicer Setting” button, the “Slicer Settings” menu appears.

Slicer Settings Menu - Windows

Slicer Settings Menu – Windows

Slicer Settings Menu - Mac

Slicer Settings Menu – Mac

From this menu, it is possible to change the Name of the Slicer, the Caption displayed on the Slicer, or even choose to display no caption at all by removing the tick from the “Display header” tick-box.

Let’s take a look at a couple of examples. Let’s start by renaming the “Devices” slicer. First, we need to click the device slicers to select it.

Device Slice

 

Once we’ve selected the Devices slicer, the “Slicer” tab should appear on the ribbon. Click the “Slicer” tab, then click the “Slicer Setting” button. When the Slicer Settings Menu appears, click in the “Caption:” text-box and type “Equipment”

Caption Textbox

Once we’ve done this, we click the OK button, and the slicer caption is updated to equipment as shown in the image below.

Slicer Caption Updated

We could have chosen to remove the slicer heading all together. To do this, we would go back to the the “Slicer Setting” menu and un-tick the “Display header” tick-box.

Slicer Display Headers

Again, click the OK Button and the slicer heading is removed.

Slicer No Caption Displayed

Most of the other options on the “Slicer Settings” menu are fairly self-explanatory with the exception of the “Use Custom Lists when sorting” tick box.

Use Custom Lists when sorting

Custom lists are used to sort in a order that don’t conform to the normal A to Z or 1,2,3 format. One obvious example of this is dates, such as days of the week—Monday, Tuesday, Wednesday and so on—or months of the year—January, February, March, etc.

It’s possible to add your own custom lists; to learn how to do this, click here for Windows or here for Mac.

I would suggest leaving this tick-box ticked, so if Excel identifies that the items to be displayed match one of these custom lists, it will display them appropriately.

Slicer Styles

The “Slicer Style” windows works in much the same way as the “Pivot Table Style” window. Select a slicer, then choose a style from the “Slicer Style” window. The follow example demonstrates this.

Select the Months slicer.

Month Slicer

Then, click on the “Slicer Style” window and choose a style. In this example, we’ll choose one that matches our Pivot Table.

Slicer Style

Once this is done, the Monthsslicer will look like the image above.

Arrange

The Arrange section of the Slicer tab has several commands, but I’m only going to discuss the two that I believe are the most useful in this blog post: the “Snap to Grid” and “Snap to Shape” commands. Both of these can be found by clicking the “Alignment Options” button.

Alignment Options

When the “Snap to Grid” command is enabled, you can move and resize a slicer very precisely. Whenever you move or resize a slicer, it will “snap” to the nearest grid point. The most obvious grid points are the corner of the cells, but there also other grid points in-between that aren’t always so obvious.

“Snap to Shape” works in a similar way. But, rather than aligning to the grid, the slicer will align to other objects on the worksheet such as the Pivot Table.

Buttons

The Buttons section of the Slicer tab perhaps unsurprisingly deals with how buttons are displayed inside a Slicer. The height and width textboxes can be used to change the height and width of the buttons inside the Slicer. It is useful when you have limited space on your worksheet.

Height and Width textboxes

In my opinion, the most useful option in the Buttons section is the ability to have multiple columns of buttons inside a Slicer. If a Slicer has lots of buttons to display, you can display them in multiple columns.

Let’s take a look at an example. If we select the Month slicer and, then, on the Button section of the Slicer tab, click in the Columns textbox, type 3, and press enter, the slicer will look like the image below.

Columns textbox

Once this is done, we can resize the Slicer to look like the image below.

Resized Slicer

Size

The size section of the Slicer tab enables you to adjust the height and width of a slicer.

Slicer height and width

This is useful if you have multiple slicers, and you want them to appear uniform.

One final note on the Slicer tab: if you select multiply slicers at the same time, you can apply formatting simultaneously to all slicers that have been selected.

Wrap-up

As you can see, there are lots of possibilities when it comes to quickly formatting the look and feel of your Pivot Table and Slicers. You can match corporate color schemes closely or make them look a little less boring. The ability to change column and row headings, as well as sort and format cell data, allows you to make your data more readable and add context.

That’s it for this blog post. I hope this series of posts has demystified Pivot Tables a little and given you the confidence to jump in and have a go at creating your own Pivot Tables.

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.

If you liked this post, please share it with your friends on Facebook or Twitter.

For other tools and resources for learning more about Excel, check out my resources page.

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