Chapter 4
Grouping, sorting, and filtering pivot data

In this chapter, you will:

• Use the PivotTable Fields list

• Sort in a pivot table

• Filter a pivot table: an overview

• Use filters for row and column fields

• Filter using the Filters area

• Group and create hierarchies in a pivot table

With Excel 2019, Microsoft has reversed the auto date grouping added to Excel 2016. Removing the feature was a good move, as the feature proved hard to predict. For anyone who loved the auto grouping and the Drill-Down and Drill-Up features, you can re-create them, although it requires a few extra steps. Grouping will be covered last in this chapter.

First, a quick overview of the PivotTable Fields. Then, a detailed look at sorting, filtering, and grouping a pivot table.

Using the PivotTable Fields list

The entry points for sorting and filtering are spread throughout the Excel interface. It is worth taking a closer look at the row header drop-downs and the PivotTable Fields list before diving in to sorting and filtering.

As you’ve seen in these pages, I rarely use the Compact form for a pivot table. I use Pivot Table Defaults to make sure my pivot tables start in Tabular layout instead of Compact layout. Although there are many good reasons for this, one is illustrated in Figures 4-1 and 4-2.

FIGURE 4-1 The drop-down menu in B3 for Customer is separate from the drop-down menu for Region.

FIGURE 4-2 In Compact form, one single drop-down menu tries to control sorting and filtering for all the row fields.

In Figure 4-1, a Region drop-down menu appears in A3. A Customer drop-down menu appears in B3. Each of these separate drop-downs offers great settings for sorting and filtering.

When you leave the pivot table in the Compact form, there are not separate headings for Region and Customer. Both fields are crammed into column A, with the silly heading Row Labels. This means the drop-down menu always offers sorting and filtering options for Region. Every time you go back to the A3 drop-down menu with hopes of filtering or sorting the Customer field, you have to reselect Customer from a drop-down at the top of the menu. This is an extra click. If you are making five changes to the Customer field, you are reselecting Customer over and over and over and over and over. This should be enough to convince you to abandon the Compact layout.

If you decide to keep the Compact layout and get frustrated with the consolidated Row Labels drop-down menu, you can directly access the invisible drop-down menu for the correct field by using the PivotTable Fields list, which contains a visible drop-down menu for every field in the areas at the bottom. Those visible drop-down menus do not contain the sorting and filtering options.

The good drop-down menus are actually in the top of the Fields list, but you have to hover over the field to see the drop-down menu appear. After you hover as shown in Figure 4-3, you can directly access the same customer drop-down menu shown in Figure 4-1.

FIGURE 4-3 Hover over the field in the top of the Fields list to directly access the sorting and filtering settings for that field.

Docking and undocking the PivotTable Fields list

The PivotTable Fields list starts out docked on the right side of the Excel window. Hover over the green PivotTable Fields heading in the pane, and the mouse pointer changes to a four-headed arrow. Drag to the left to enable the pane to float anywhere in your Excel window.

After you have undocked the PivotTable Fields list, you might find that it is difficult to redock it on either side of the screen. To redock the Fields list, you must grab the title bar and drag until at least 85% of the Fields list is off the edge of the window. Pretend that you are trying to remove the floating Fields list completely from the screen. Eventually, Excel gets the hint and redocks it. Note that you can dock the PivotTable Fields list on either the right side or the left side of the screen.

Rearranging the PivotTable Fields list

As shown in Figure 4-4, a small gear-wheel icon appears near the top of the PivotTable Fields list. Select this drop-down menu to see its five possible arrangements. Although the default is to have the Fields section at the top of the list and the Areas section at the bottom of the list, four other arrangements are possible. Other options let you control whether the fields in the list appear alphabetically or in the same sequence that they appeared in the original data set.

FIGURE 4-4 Use this drop-down menu to rearrange the PivotTable Fields list.

The final three arrangements offered in the drop-down menu are rather confusing. If someone changes the PivotTable Fields list to show only the Areas section, you cannot see new fields to add to the pivot table.

If you ever encounter a version of the PivotTable Fields list with only the Areas section (see Figure 4-4) or only the Fields section, remember that you can return to a less-confusing view of the data by using the arrangement drop-down menu.

Using the Areas section drop-downs

As shown in Figure 4-5, every field in the Areas section has a visible drop-down arrow. When you select this drop-down arrow, you see four categories of choices:

FIGURE 4-5 Use this drop-down menu to rearrange the fields in your pivot table.

• The first four choices enable you to rearrange the field within the list of fields in that area of the pivot table. You can accomplish this by dragging the field up or down in the area.

• The next four choices enable you to move the field to a new area. You could also accomplish this by dragging the field to a new area.

• The next choice enables you to remove the field from the pivot table. You can also accomplish this by dragging the field outside the Fields list.

• The final choice displays the Field Settings dialog box for the field.

Sorting in a pivot table

Items in the row area and column area of a pivot table are sorted in ascending order by any custom list first. This allows weekday and month names to sort into Monday, Tuesday, Wednesday, … instead of the alphabetical order Friday, Monday, Saturday, …, Wednesday.

If the items do not appear in a custom list, they will be sorted in ascending order. This is fine, but in many situations, you want the customer with the largest revenue to appear at the top of the list. When you sort in descending order using a pivot table, you are setting up a rule that controls how that field is sorted, even after new fields are added to the pivot table.

Tip

Excel 2019 includes four custom lists by default, but you can add your own custom list to control the sort order of future pivot tables. See the section “Using a custom list for sorting,” later in this chapter.

Sorting customers into high-to-low sequence based on revenue

Three pivot tables appear in Figure 4-6. The first pivot table shows the default sort for a pivot table: Customers are arranged alphabetically, starting with Adaept, Calleia, and so on.

Figure 4-6 When you override the default sort, Excel remembers the sort as additional fields are added.

In the second pivot table, the report is sorted in descending sequence by Total Revenue. This pivot table was sorted by selecting cell E3 and choosing the ZA icon in the Data tab of the ribbon. Although that sounds like a regular sort, it is better. When you sort inside a pivot table, Excel sets up a rule that will be used after you make additional changes to the pivot table.

The pivot table in columns G:H shows what happens after you add Sector as a new outer row field. Within each sector, the pivot table continues to sort the data in descending order by revenue. Within Consulting, Surten Excel appears first, with $750K, followed by NetCom, with $614K.

You could remove Customer from the pivot table, do more adjustments, and then add Customer back to the column area, and Excel would remember that the customers should be presented from high to low.

If you could see the entire pivot table in G3:H35 in Figure 4-6, you would notice that the sectors are sorted alphabetically. It might make more sense, though, to put the largest sectors at the top. The following tricks can be used for sorting an outer row field by revenue:

• You can select cell G4 and then use Collapse Field on the Analyze tab to hide the customer detail. When you have only the sectors showing, select H4 and click ZA to sort descending. Excel understands that you want to set up a sort rule for the Sector field.

• You can temporarily remove Customer from the pivot table, sort descending by revenue, and then add Customer back.

• You can use More Sort Options, as described in the following paragraphs.

To sort the Sector field, you should open the drop-down menu for the Sector field. Hover over Sector in the top of the PivotTable Fields list, and click the drop-down arrow that appears (see Figure 4-7). Or, if your pivot table is shown in Tabular layout or Outline layout, you can simply open the drop-down arrow in cell G3.

FIGURE 4-7 For explicit control over sort order, open this drop-down menu.

Inside the drop-down menu, choose More Sort Options to open the Sort (Sector) dialog box. In this dialog box, you can choose to sort the Sector field in Descending order by Total $ (see Figure 4-8).

FIGURE 4-8 Choose to sort Sector based on the Total $ field.

The Sort (Sector) dialog box shown in Figure 4-8 includes a More Options button in the lower left. If you click this button, you arrive at the More Sort Options dialog box, in which you can specify a custom list to be used for the first key sort order. You can also specify that the sorting should be based on a column other than Grand Total.

In Figure 4-10, the pivot table includes Product in the column area. If you wanted to sort the customers based on total gadget revenue instead of total revenue, for example, you could do so with the More Sort Options dialog box. Here are the steps:

1. Open the Customer heading drop-down menu in B4.

2. Choose More Sort Options.

3. In the Sort (Customer) dialog box, choose More Options.

4. In the More Sort Options (Customer) dialog box, choose the Sort By Values In Selected Column option (see Figure 4-9).

5. Click in the reference box and then click cell D5. Note that you cannot click the Gadget heading in D4; you have to choose one of the Gadget value cells.

6. Click OK twice to return to the pivot table.

FIGURE 4-9 Using More Sort Options, you can sort by a specific pivot field item.

If your pivot table has only one field in the Rows area, you can set up the “Sort by Doodads” rule by doing a simple sort using the Data tab. Select any cell in B5:B30 and choose Data, ZA. The pivot table will be sorted with the largest Doodads customers at the top (see Figure 4-10). Note that you cannot sort from the Doodads heading in B4. Sorting from there will sort the product columns by revenue.

FIGURE 4-10 Sort from cell E5 to sort by Doodads.

Using a manual sort sequence

The Sort dialog box offers something called a manual sort. Rather than using the dialog box, you can invoke a manual sort in a surprising way.

Note that the products in Figure 4-10 are in the following order: Doodads, Gadget, Gizmo, and Widget. It appears that the Doodads product line is a minor product line and probably would not fall first in the product list.

Place the cell pointer in cell E4 and type the word Doodads. When you press Enter, Excel figures out that you want to move the Doodads column to be last. All the values for this product line move from column B to column E. The values for the remaining products shift to the left.

One unintended consequence is that the customers re-sort based on the product that moved to column B: Gadget. This is because the “Sort by Doodads” rule was actually a “Sort by whatever is in column B” rule.

In Figure 4-11, note the numbers in row 17 and compare them to the numbers in row 5 in Figure 4-10. The values followed the change in headings.

FIGURE 4-11 Simply type a heading in E4 to rearrange the columns.

This behavior is completely unintuitive. You should never try this behavior with a regular (non–pivot table) data set in Excel. You would never expect Excel to change the data sequence just by moving the labels. Figure 4-11 shows the pivot table after a new column heading has been typed in cell E4.

If you prefer to use the mouse, you can drag and drop the column heading to a new location. Select a column heading. Hover over the edge of the active cell border until the mouse changes to a four-headed arrow. Drag the cell to a new location, as shown in Figure 4-12. When you release the mouse, all the value settings move to the new column.

FIGURE 4-12 Use drag and drop to move a column to a new position.

Caution

After you use a manual sort, any new products you add to the data source are automatically added to the end of the list rather than appearing alphabetically.

Using a custom list for sorting

Another way to permanently change the order of items along a dimension is to set up a custom list. All future pivot tables created on your computer will automatically respect the order of the items in a custom list.

The pivot table at the top of Figure 4-13 includes weekday names. The weekday names were added to the original data set by using =TEXT(F2,"DDD") and copying down. Excel automatically puts Sunday first and Saturday last, even though this is not the alphabetical sequence of these words. This happens because Excel ships with four custom lists to control the days of the week, months of the year, and the three-letter abbreviations for both.

FIGURE 4-13 The weekday names in B4:H4 follow the order specified in the Custom Lists dialog box.

You can define your own custom list to control the sort order of pivot tables. Follow these steps to set up a custom list:

1. In an out-of-the-way section of the worksheet, type the products in their proper sequence. Type one product per cell, going down a column.

2. Select the cells containing the list of regions in the proper sequence.

3. Click the File tab and select Options.

4. Select the Advanced category in the left navigation bar. Scroll down to the General group and click the Edit Custom Lists button. In the Custom Lists dialog box, your selection address is entered in the Import text box, as shown in Figure 4-13.

5. Click Import to bring the products in as a new list.

6. Click OK to close the Custom Lists dialog box, and then click OK to close the Excel Options dialog box.

The custom list is now stored on your computer and is available for all future Excel sessions. All future pivot tables will automatically show the product field in the order specified in the custom list. Figure 4-14 shows a new pivot table created after the custom list was set up.

FIGURE 4-14 After you define a custom list, all future pivot tables will follow the order in the list.

To sort an existing pivot table by the newly defined custom list, follow these steps:

1. Open the Product header drop-down menu and choose More Sort Options.

2. In the Sort (Product) dialog box, choose More Options.

3. In the More Sort Options (Product) dialog box, clear the AutoSort check box.

4. As shown in Figure 4-15, in the More Sort Options (Product) dialog box, open the First Key Sort Order drop-down menu and select the custom list with your product names.

5. Click OK twice.

FIGURE 4-15 Choose to sort by the custom list.

Caution

Items in a custom list will automatically sort to the top of all future pivot tables. If you have a pivot table of people using their first names, people with names like Jan, May, and April will automatically appear before other names. Names that appear in any list, even across several custom lists will sort in the wrong sequence. To turn off this behavior for one pivot table: right-click one cell in the pivot table and choose PivotTable options. On the Totals & Filters tab, unselect Use Custom Lists When Sorting. If you want to turn this off for all pivot tables, change the Pivot Table Defaults using File, Options, Data, Edit Default Layout, PivotTable Options.

Filtering a pivot table: an overview

Excel 2019 provides dozens of ways to filter a pivot table. Figure 4-16 shows some of the filters available. These methods, and the best way to use each one, are discussed in the following sections.

FIGURE 4-16 This figure shows a fraction of the available filtering choices.

There are four ways to filter a pivot table, as shown in Figure 4-16:

• The Date Timeline filter in G4:H10 was introduced in Excel 2013.

• The Market filter in G12:H19 is an example of the slicer introduced in Excel 2010.

• A drop-down menu in B1 offers what were known as page filters in Excel 2003, report filters in Excel 2010, and now simply filters.

• Cell G4 offers the top-secret AutoFilter location.

• Drop-down menus in A4 and B3 lead to even more filters.

• You see the traditional check box filters for each pivot item.

• A Search box filter was introduced in Excel 2010.

• A flyout menu has Label filters.

• Depending on the field type, you might see a Value Filters flyout menu, including the powerful Top 10 filter, which can do Top 10, Bottom 5, Bottom 3%, Top $8 Million, and more.

• Depending on the field type, you might see a Date Filters flyout menu, with 37 virtual filters such as Next Month, Last Year, and Year to Date.

Using filters for row and column fields

If you have a field (or fields) in the row or column area of a pivot table, a drop-down menu with filtering choices appears on the header cell for that field. In Figure 4-16, a Customer drop-down menu appears in A4, and a Product drop-down menu appears in B3. The pivot table in that figure is using Tabular layout. If your pivot tables use Compact layout, you see a drop-down menu on the cell with Row Labels or Column Labels.

If you have multiple row fields, it is just as easy to sort using the invisible drop-down menus that appear when you hover over a field in the top of the PivotTable Fields list.

Filtering using the check boxes

You might have a few annoying products appear in a pivot table. In the present example, the Doodads product line is a specialty product with very little sales. It might be an old legacy product that is out of line, but it still gets an occasional order from the scrap bin. Every company seems to have these orphan sales that no one really wants to see.

The check box filter provides an easy way to hide these items. Open the Product drop-down menu and clear the Doodads check box. The product is hidden from view (see Figure 4-17) .

FIGURE 4-17 Open the Product filter and clear the Doodads check box.

What if you need to clear hundreds of items’ check boxes in order to leave only a few items selected? You can toggle all items off or on by using the Select All check box at the top of the list. You can then select the few items that you want to show in the pivot table.

In Figure 4-18, Select All turned off all customers and then two clicks reselected Excel4Apps and F-Keys Ltd.

FIGURE 4-18 Use Select All to toggle all items off or on.

The check boxes work great in this tiny data set with 26 customers. In real life, with 500 customers in the list, it will not be this easy to filter your data set by using the check boxes.

Filtering using the search box

When you have hundreds of customers, the search box can be a great timesaver. In Figure 4-19, the database includes consultants, trainers, and other companies. If you want to narrow the list to companies with Excel or spreadsheet in their name, you can follow these steps:

FIGURE 4-19 Select the results of the first search.

1. Open the Customer drop-down menu.

2. Type Excel in the search box (see Figure 4-19).

3. By default, Select All Search Results is selected. Click OK.

4. Open the Customer drop-down menu again.

5. Type spreadsheet in the search box.

6. Choose Add Current Selection to Filter, as shown in Figure 4-20. Click OK.

FIGURE 4-20 For the second search, add these results to the existing filter.

You now have all customers with either Excel or spreadsheet in the name.

Caution

Filters applied with the search box are single-use filters. If you add more data to the underlying data and refresh the pivot table, this filter will not be reevaluated.

If you need to reapply the filter, it would be better to use the Label filters as discussed in the following section. Label filters would work to find every customer with Excel in the name. It would not work to find “Excel or spreadsheet.”

Filtering using the Label Filters option

The search box isn’t perfect. What if you want to find all the Lotus 1-2-3 consultants and turn those off? There is no Select Everything Except These Results choice. Nor is there a Toggle All Filter Choices choice. However, the Label Filters option enables you to handle queries such as “select all customers that do not contain ‘Lotus.’”

Text fields offer a flyout menu called Label Filters. To filter out all of the Insurance customers, you can apply a Does Not Contain filter (see Figure 4-21). In the next dialog box, you can specify that you want customers that do not contain Excel, Exc, or Exc* (see Figure 4-22).

FIGURE 4-21 Choose Label Filters, Does Not Contain.

Figure 4-22 Specify to exclude customers containing Excel.

Note that label filters are not additive. You can only apply one label filter at a time. If you take the data in Figure 4-21 and apply a new label filter of between D and Fzzz, some Excel customers that were filtered out in Figure 4-22 come back, as shown in Figure 4-23.

FIGURE 4-23 Note that a second label filter does not get added to the previous filter. Excel is back in.

Filtering a Label column using information in a Values column

The Value Filters flyout menu enables you to filter customers based on information in the Values columns. Perhaps you want to see customers who had between $20,000 and $30,000 of revenue. You can use the Customer heading drop-down menu to control this. Here’s how:

1. Open the Customer drop-down menu.

2. Choose Label Filters.

3. Choose Between (see Figure 4-24).

   

   Figure 4-24 Value Filters for the Customer column will look at values in the Revenue field.

4. Type the values 20000 and 30000, as shown in Figure 4-25.

   

   FIGURE 4-25 Choose customers between 20000 and 30000, inclusive.

5. Click OK.

The results are inclusive; if a customer had exactly $20,000 or exactly $30,000, they are returned along with the customers between $20,000 and $30,000.

Note

Choosing a Value filter clears out any previous Label filters.

Creating a top-five report using the Top 10 filter

One of the more interesting value filters is the Top 10 filter. If you are sending a report to the VP of Sales, she is not going to want to see hundreds of pages of customers. One short summary with the top customers is almost more than her attention span can handle. Here’s how to create it:

1. Go to the Customer drop-down menu and choose Value Filters, Top 10.

2. In the Top 10 Filter dialog box, which enables you to choose Top or Bottom, leave the setting at the default of Top.

3. In the second field, enter any number of customers: 10, 5, 7, 12, or something else.

4. In the third drop-down menu on the dialog box, select from Items, Percent, and Sum. You could ask for the top 10 items. You could ask for the top 80% of revenue (which the theory says should be 20% of the customers). Or you could ask for enough customers to reach a sum of $5 million (see Figure 4-26).

FIGURE 4-26 Create a report of the top five customers.

The $1,758,220 total shown in cell B9 in Figure 4-26 is the revenue of only the visible customers. It does not include the revenue for the remaining customers. You might want to show the grand total of all customers at the bottom of the list. You have a few options:

• A setting on the Design tab, under the Subtotals drop-down menu, enables you to include values from filtered items in the totals. This option is available only for OLAP data sets and data sets where you choose Add This Data To The Data Model when creating the pivot table.

Note

See Chapter 10, “Unlocking features with the Data Model and Power Pivot,” for more information on working with Power Pivot.

• You can remove the grand total from the pivot table in Figure 4-27 and build another one-row pivot table just below this data set. Hide the heading row from the second pivot table, and you will appear to have the true grand total at the bottom of the pivot table.

  

  FIGURE 4-27 You are taking advantage of a hole in the fabric of Excel to apply a regular AutoFilter to a pivot table.

• If you select the blank cell to the right of the last heading (C3 in Figure 4-26), you can turn on the filter on the Data tab. This filter is not designed for pivot tables and is usually grayed out. After you’ve added the regular filters, open the drop-down menu in B3. Choose Top 10 Filter and ask for the top six items, as shown in Figure 4-27. This returns the top five customers and the grand total from the data set.

Caution

Be aware that this method is taking advantage of a bug in Excel. Normally, the Filter found on the Data tab is not allowed in a pivot table. If you use this method and later refresh the pivot table, the Excel team will not update the filter for you. As far as they know, the option to filter is grayed out when you are in a pivot table.

Filtering using the Date filters in the Label drop-down menu

If your label field contains all dates, Excel replaces the Label Filter flyout with a Date Filters flyout. These filters offer many virtual filters, such as Next Week, This Month, Last Quarter, and so on (see Figure 4-28).

FIGURE 4-28 The Date Filters menu offers various virtual date periods.

If you choose Equals, Before, After, or Between, you can specify a date or a range of dates.

Options for the current, past, or next day, week, month, quarter, or year occupy 15 options. Combined with Year To Date, these options change day after day. You can pivot a list of projects by due date and always see the projects that are due in the next week by using this option. When you open the workbook on another day, the report recalculates.

Tip

A week runs from Sunday through Saturday. If you select Next Week, the report always shows a period from the next Sunday through the following Saturday.

When you select All Dates In The Period, a new flyout menu offers options such as Each Month and Each Quarter.

Caution

If your date field contains dates and times, the Date Filters might not work as expected. You might ask for dates equal to 4/15/2022, and Excel will say that no records are found. The problem is that 6:00 p.m. on 4/15/2022 is stored internally as 44666.75, with the “.75” representing the 18 hours elapsed in the day between midnight and 6:00 p.m. If you want to return all records that happened at any point on April 15, select the Whole Days check box in the Date Filter dialog box.

Filtering using the Filters area

Pivot table veterans remember the old Page area section of a pivot table. This area has been renamed the Filters area and still operates basically the same as in legacy versions of Excel. Microsoft did add the capability to select multiple items from the Filters area. Although the Filters area is not as showy as slicers, it is still useful when you need to replicate your pivot table for every customer.

Adding fields to the Filters area

The pivot table in Figure 4-29 is a perfect ad-hoc reporting tool to give to a high-level executive. He can use the drop-down menus in B1:B4 and E1:E4 to find revenue quickly for any combination of sector, region, market, rep, customer, product, date, or weekday. This is a typical use of filters.

FIGURE 4-29 With multiple fields in the Filters area, this pivot table can answer many ad-hoc queries.

To set up the report, drag Revenue and Cost to the Values area and then drag as many fields as desired to the Filters area.

If you add many fields to the Filters area, you might want to use one of the obscure pivot table options settings. Click Options on the Analyze tab. On the Layout & Format tab of the PivotTable Options dialog box, change Report Filter Fields per Column from 0 to a positive number. Excel rearranges the Filter fields into multiple columns. Figure 4-29 shows the filters with four fields per column. You can also change Down, Then Over to Over, Then Down to rearrange the sequence of the Filter fields.

Choosing one item from a filter

To filter the pivot table, click any drop-down menu in the Filters area of the pivot table. The drop-down menu always starts with (All) but then lists the complete unique set of items available in that field.

Choosing multiple items from a filter

At the bottom of the Filters drop-down menu is a check box labeled Select Multiple Items. If you select it, Excel adds a check box next to each item in the drop-down menu. This enables you to select multiple items from the list.

In Figure 4-30, the pivot table is filtered to show revenue from multiple sectors, but it is impossible to tell which sectors are included.

FIGURE 4-30 You can select multiple items, but after the Filter drop-down menu closes, you cannot tell which items were selected.

Tip

Selecting multiple items from the filter leads to a situation where the person reading the report will not know which items are included. Slicers solve this problem.

Replicating a pivot table report for each item in a filter

Although slicers are now the darlings of the pivot table report, the good old-fashioned report filter can still do one trick that slicers cannot do. Say you have created a report that you would like to share with the industry managers. You have a report showing customers with revenue and profit. You would like each industry manager to see only the customers in their area of responsibility.

Follow these steps to quickly replicate the pivot table:

1. Make sure the formatting in the pivot table looks good before you start. You are about to make several copies of the pivot table, and you don’t want to format each worksheet in the workbook, so double-check the number formatting and headings now.

2. Add the Sector field to the Filters area. Leave the Sector filter set to (All).

3. Select one cell in the pivot table so that you can see the Analyze tab in the ribbon.

4. Find the Options button in the left side of the Analyze tab. Next to the Options tab is a drop-down menu. Don’t click the big Options button. Instead, open the drop-down menu (see Figure 4-31).

   

   FIGURE 4-31 Click the tiny drop-down arrow next to the Options button.

5. Choose Show Report Filter Pages. In the Show Report Filter Pages dialog box, you see a list of all the fields in the report area. Because this pivot table has only the Sector field, this is the only choice (see Figure 4-32).

   

   FIGURE 4-32 Select the field by which to replicate the report.

6. Click OK and stand back.

Excel inserts a new worksheet for every item in the Sector field. On the first new worksheet, Excel chooses the first sector as the filter value for that sheet. Excel renames the worksheet to match the sector. Figure 4-33 shows the new Consulting worksheet, with neighboring tabs that contain Museums, Retail, Training, and Utilities.

FIGURE 4-33 Excel quickly adds one page per sector.

Tip

If the underlying data changes, you can refresh all of the Sector worksheets by using Refresh on one Sector pivot table. After you refresh the Consulting worksheet, all the pivot tables refresh.

Filtering using slicers and timelines

Slicers are graphical versions of the Report Filter fields. Rather than hiding the items selected in the filter drop-down menu behind a heading such as (Multiple Items), the slicer provides a large array of buttons that show at a glance which items are included or excluded.

To add slicers, click the Insert Slicer icon on the Analyze tab. Excel displays the Insert Slicers dialog box. Choose all the fields for which you want to create graphical filters, as shown in Figure 4-34.

FIGURE 4-34 Choose fields for slicers.

Initially, Excel chooses one-column slicers of similar color in a cascade arrangement (see Figure 4-35). However, you can change these settings by selecting a slicer and using the Slicer Tools Options tab in the ribbon.

FIGURE 4-35 The slicers appear with one column each.

You can add more columns to a slicer. If you have to show 50 two-letter state abbreviations, that will look much better as 5 rows of 10 columns than as 50 rows of 1 column. Click the slicer to get access to the Slicer Tools Analyze tab. Use the Columns spin button to increase the number of columns in the slicer. Use the resize handles in the slicer to make the slicer wider or shorter. To add visual interest, choose a different color from the Slicer Styles gallery for each field.

After formatting the slicers, arrange them in a blank section of the worksheet, as shown in Figure 4-36.

FIGURE 4-36 After formatting, your slicers might fit on a single screen.

Three colors might appear in a slicer. The dark color indicates items that are selected. Gray boxes often mean the item has no records because of other slicers. White boxes indicate items that are not selected.

Note that you can control the heading for the slicer and the order of items in the slicer by using the Slicer Settings icon on the Slicer Tools Options tab of the ribbon. Just as you can define a new pivot table style, you can also right-click an existing slicer style and choose Duplicate. You can change the font, colors, and so on.

A new icon debuted in Excel 2016, in the top bar of the slicer. The icon appears as three check marks. When you select this icon, you can select multiple items from the slicer without having to hold down the Ctrl key.

Using timelines to filter by date

After slicers were introduced in Excel 2010, there was some feedback that using slicers was not an ideal way to deal with date fields. You might end up adding some fields to your original data set to show (perhaps) a decade and then use the group feature for year, quarter, and month. You would end up with a whole bunch of slicers all trying to select a time period, as shown in Figure 4-37.

FIGURE 4-37 Four different slicers are necessary to filter by date.

For Excel 2013, Microsoft introduced a new kind of filter called a Timeline slicer. To use one, select one cell in your pivot table and choose Insert Timeline from the Analyze tab. Timeline slicers can only apply to fields that contain dates. Excel gives you a list of date fields to choose from, although in most cases, there is only one date field from which to choose.

Figure 4-38 shows a Timeline slicer. Perhaps the best part of a Timeline slicer is the drop-down menu that lets you repurpose the timeline for days, months, quarters, or years. This works even if you have not grouped your daily dates up to months, quarters, or years.

FIGURE 4-38 A single Timeline slicer can filter your pivot table by month, quarter, year, or day.

Driving multiple pivot tables from one set of slicers

Chapter 12, “Enhancing pivot table reports with macros,” includes a tiny macro that lets you drive two pivot tables with one set of filters. This has historically been difficult to do unless you used a macro.

Now, one set of slicers or timelines can be used to drive multiple pivot tables or pivot charts. In Figure 4-39, the Market slicer is driving three elements. It drives the pivot table in the top left with revenue by sector and product. It drives two pivot tables created for the top-right and lower-left charts.

FIGURE 4-39 Three pivot elements controlled by the same slicer.

Note

For more information about how to create pivot charts, refer to Chapter 6, “Using pivot charts and other visualizations.”

The following steps show you how to create three pivot tables that are tied to a single slicer:

1. Create your first pivot table.

2. Select a cell in the first pivot table. Choose Insert Slicer. Choose one or more fields to be used as a slicer. Alternatively, insert a Timeline slicer for a date field.

3. Select the entire pivot table.

4. Copy with Ctrl+C or the Copy command.

5. Select a new blank area of the worksheet.

6. Paste. Excel creates a second pivot table that shares the pivot cache with the first pivot table. In order for one slicer to run multiple pivot tables, they must share the same pivot cache.

7. Change the fields in the second pivot table to show some other interesting analysis.

8. Repeat steps 3–7 to create a third copy of the pivot table.

The preceding steps require you to create the slicer after you create the first pivot table but before you make copies of the pivot table.

If you already have several existing pivot tables and need to hook them up to the same slicer, follow these steps:

1. Click the slicer to select it. When the slicer is selected, the Slicer Tools Design tab of the ribbon appears.

2. Select the Slicer Tools Design tab and choose Report Connections. Excel displays the Report Connections (Market) dialog box. Initially, only the first pivot table is selected.

3. As shown in Figure 4-40, choose the other pivot tables in the dialog box and click OK.

   

   FIGURE 4-40 Choose to hook this slicer up to the other pivot tables.

4. If you created multiple slicers and/or timelines, repeat steps 1 through 3 for the other slicers.

The result is a dashboard in which all of the pivot tables and pivot charts update in response to selections made in the slicer (see Figure 4-41).

FIGURE 4-41 All of the pivot charts and pivot tables update when you choose from the slicer.

Tip

The worksheet in Figure 4-41 would be a perfect worksheet to publish to SharePoint or to your OneDrive. You can share the workbook with coworkers and allow them to interact with the slicers. They won’t need to worry about the underlying data or enter any numbers; they can just click on the slicer to see the reports update.

Grouping and creating hierarchies in a pivot table

Pivot tables have the ability to do roll-ups in memory. You can roll daily dates up to weeks, months, quarters, or years. Time can roll up to minutes or hours. Numbers can be grouped into equal-size buckets. Text entries can be grouped into territories.

You can use the Power Pivot grid to define a hierarchy so you can quickly drill down on a pivot table or chart.

Grouping numeric fields

The Grouping dialog box for numeric fields enables you to group items into equal ranges. This can be useful for creating frequency distributions. The pivot table in Figure 4-42 is quite the opposite of anything you’ve seen so far in this book. The numeric field—Revenue—is in the Rows area. A text field—Customer—is in the Values area. When you put a text field in the Values area, you get a count of how many records match the criteria. In its present state, this pivot table is not that fascinating; it is telling you that exactly one record in the database has a total revenue of $23,990.

FIGURE 4-42 Nothing interesting here—just lots of order totals that appear exactly one time in the database.

Select one number in column A of the pivot table. Select Group Field from the Analyze tab of the ribbon. Because this field is not a date field, the Grouping dialog box offers fields for Starting At, Ending At, and By. As shown in Figure 4-43, you can choose to show amounts from 0 to 30,000 in groups of 5,000.

FIGURE 4-43 Create a frequency distribution by grouping the order size into $5,000 buckets.

After grouping the order size into buckets, you might want to add additional fields, such as Revenue and % Of Revenue shown as a percentage of the total.

Note

The grouping dialog box requires all groups to be the same size. I have heard questions where people want to group into 0–100K, 200K–500K, but this is not possible using the Grouping feature. You would have to add a new column to the source data in order to create these groupings.

Case Study: Grouping text fields for redistricting

Say that you get a call from the VP of Sales. The Sales Department is secretly considering a massive reorganization of the sales regions. The VP would like to see a report showing revenue after redistricting. You have been around long enough to know that the proposed regions will change several times before the reorganization happens, so you are not willing to change the Region field in your source data quite yet.

First, build a report showing revenue by market. The VP of Sales is proposing eliminating two regional managers and redistricting the country into three super-regions. While holding down the Ctrl key, highlight the five regions that will make up the new West region. Figure 4-44 shows the pivot table before the first group is created.

FIGURE 4-44 Use the Ctrl key to select the noncontiguous cells that make up the new region.

From the Analyze tab, click Group Selection. Excel adds a new field called Market2. The five selected regions are arbitrarily rolled up to a new territory called Group1. In Figure 4.45, the Group1 label in A4 is the first item in the new Market2 virtual field. The other items in the Market2 field includee Atlanta in A10, Charlotte in A12 and so on. The similar items in A11 and A13 are items in the original Market field. Use care while holding down the Ctrl key to select the unbolded markets for the South region: Atlanta in row 11, Charlotte in row 13, Huntsville in row 11, and Louisville in row 23...

FIGURE 4-45 The first super-region is arbitrarily called Group1.

Click Group Selection to group the markets in the proposed Southeast region. Repeat to group the remaining regions into the proposed Northeast region. Figure 4-46 shows what it looks like when you have grouped the markets into new regions. Five things need further adjustment: the names of Group1, Group2, Group3, and Market2, and the lack of subtotals for the outer row field.

FIGURE 4-46 The markets are grouped, but you have to do some cleanup.

Cleaning up the report takes only a few moments:

1. Select cell A4. Type West to replace the arbitrary name Group1.

2. Select cell A10. Type Southeast to replace the arbitrary name Group2.

3. Select A15. Type Northeast to replace Group3.

4. Select any outer heading in A4, A10, or A15. Click Field Settings on the Analyze tab.

5. In the Field Settings dialog box, replace the Custom Name of Market2 with Proposed Region. Also, in the Field Settings dialog box, change the Subtotals setting from None to Automatic.

Figure 4-47 shows the pivot table that results, which is ready for the VP of Sales.

FIGURE 4-47 It is now easy to see that these regions are heavily unbalanced.

You can probably predict that the Sales Department needs to shuffle markets to balance the regions. To go back to the original regions, select any Proposed Region cell in A4, A10, or A15 and choose Ungroup. You can then start over, grouping regions in new combinations.

Grouping date fields manually

Excel provides a straightforward way to group date fields. Select any date cell in your pivot table. On the Analyze tab, click Group Field in the Group option.

When your field contains date information, the date version of the Grouping dialog box appears. By default, the Months option is selected. You have choices to group by Seconds, Minutes, Hours, Days, Months, Quarters, and Years. It is possible—and usually advisable—to select more than one field in the Grouping dialog box. In this case, select Months and Years, as shown in Figure 4-48.

FIGURE 4-48 Business users of Excel usually group by months (or quarters) and years.

There are several interesting points to note about the resulting pivot table. First, notice that the Years field has been added to the PivotTable Fields list. Don’t let this fool you. Your source data is not changed to include the new field. Instead, this field is now part of your pivot cache in memory.

Another interesting point is that, by default, the Years field is automatically added to the same area as the original date field in the pivot table layout, as shown in Figure 4-49. Although this happens automatically, you are free to pivot months and years onto the opposite axis of the report. This is a quick way to create a year-over-year sales report.

FIGURE 4-49 By default, Excel adds the new grouped date field to your pivot table layout.

Including years when grouping by months

Although this point is not immediately obvious, it is important to understand that if you group a date field by month, you also need to include the year in the grouping. If your data set includes January 2024 and January 2025, selecting only months in the Grouping dialog box will result in both January 2024 and January 2025 being combined into a single row called January (see Figure 4-50).

FIGURE 4-50 If you fail to include the Year field in the grouping, the report mixes sales from last January and this January into a single row called January.

Grouping date fields by week

The Grouping dialog box offers choices to group by second, minute, hour, day, month, quarter, and year. It is also possible to group on a weekly or biweekly basis.

The first step is to find either a paper calendar or an electronic calendar, such as the Calendar feature in Outlook, for the year in question. If your data starts on January 1, 2024, it is helpful to know that January 1 is a Monday that year. You need to decide if weeks should start on Sunday or Monday or any other day. For example, you can check the paper or electronic calendar to learn that the nearest starting Sunday is December 31, 2023.

Select any date heading in your pivot table. Then select Group Field from the Analyze tab. In the Grouping dialog box, clear all the By options and select only the Days field. This enables the spin button for Number of Days. To produce a report by week, increase the number of days from 1 to 7.

Next, you need to set up the Starting At date. If you were to accept the default of starting at January 1, 2024, all your weekly periods would run from Monday through Sunday. By checking a calendar before you begin, you know that you want the first group to start on December 31, 2023, to have weeks that run Sunday through Monday. Figure 4-51 shows the settings in the Grouping dialog box and the resulting report.

FIGURE 4-51 Group dates up to weekly periods.

Caution

If you choose to group by week, none of the other grouping options can be selected. You cannot group this or any field by month, quarter, or year. You cannot add calculated items to the pivot table.

AutoGrouping pivot table dates

Excel 2016 introduced an AutoGroup feature for dates. If you dragged a date field to a pivot table, Excel would quickly add date rollups and define a hierarchy for the dates.

The feature was turned on by default, and the only way to turn it off was a change in the Registry.

I love the concept of teaching people that daily dates can easily be rolled up. But for the people who needed to report daily dates, the AutoGroup was inconsistent and confusing. The logic used to choose which rollups would be present would sometimes leave out daily dates from the hierarchy.

Today, Excel 2019 will not automatically AutoGroup. You can choose to allow the AutoGroup if you loved this feature. Go To File, Options, Data, and deselect Disable Automatic Grouping Of Date/Time Columns In Pivot Tables.

Understanding how Excel decides what to AutoGroup

If you have daily dates that include an entire year or that fall in two or more years, Excel 2019 groups the daily dates to include years, quarters, and months. If you need to report by daily dates, you will have to select any date cell, choose Group Field, and add Days. Note that the rules change if your data is in the Data Model. In that case, AutoGroup would include daily dates as well.

If you have daily dates that fall within one calendar year and span more than one month, Excel groups the daily dates to month and includes daily dates.

Caution

If your company is closed on New Year’s Day and you have no sales on January 1, a data set that stretches from January 2 to December 31 will fit the “less than a full year” case and will include months and daily dates.

If your data contains times that do not cross over midnight, you get hours, minutes, and seconds. If the times span more than one day, you get days, hours, minutes, and seconds.

Using AutoGroup

Say that you have a column in your data set with daily dates that span two years. When you add this Date field to the Rows area of your pivot table, you will see rows for each year instead of hundreds of daily dates. If your pivot table is in Tabular layout, you will see extra columns for Quarter and Date that appear to have no data (see Figure 4-52).

FIGURE 4-52 Excel can automatically groups two years’ worth of daily dates up to months, quarters, and years

When you look in the Pivot Table Fields list, you see that the Rows area automatically includes three fields: Years, Quarter, and Date. All three of these are virtual fields created by grouping the daily dates up to months, quarters, and years.

The three fields are added to either the Rows area or the Columns area. However, only the highest level of the date field will be showing. To see the quarters and years, click one cell that contains a year and then click the Expand button in the Analyze tab of the ribbon (see Figure 4-53). To see months, select a cell containing a quarter and click the Expand button again (see Figure 4-54).

FIGURE 4-53 Use Expand Field to show the quarters.

FIGURE 4-54 Expand Field again to show the monthly data.

Creating an easy year-over-year report

You can use date grouping to easily create a year-over-year report. You can either manually group the dates to years or use the AutoGroup.

Follow these steps:

1. Create a pivot table with Years in the Columns area and Months in the Rows area. Drag Revenue to the Values area. By default, the pivot table will offer a Grand Total column, as shown in Figure 4-55.

2. Right-click the Grand Total heading and choose Remove Grand Total.

3. Drag Revenue a second time to the Values area.

4. In the Columns area, drag Years so it is below Values. You will have the pivot table shown in Figure 4-56.

   

   FIGURE 4-55 Group daily dates to months and years. Drag Years to go across the report.

   

   FIGURE 4-56 This year and last year appear twice across the top of the report.

5. Double-click the Sum of Revenue2 heading in cell D4 to display the Value Field Settings dialog box.

6. In the Value Field Settings dialog box, select the Show Values As tab. In the Show Values As drop-down menu, choose % Difference From. In the Base Field list, choose Years. In the Base Item, choose (Previous), as shown in Figure 4-57.

   

   FIGURE 4-57 Change the second Revenue columns to percentage difference from the previous year.

7. Close the Value Field Settings dialog box. Column E will show the percentage change from the first year to the last year. Column D will be blank because the pivot table has no data from 2023 to use to compare to 2024.

8. Hide column D.

9. Select the 2025 heading in E5. Press Ctrl+1 for Format Cells. On the Number tab, choose Custom. Type a format of ;;;"% Change".

You have a report showing year 1 versus year 2 and a percentage change. You can easily remove the Months from column A and insert Region, Market, or Product to see the year-over-year change. Figure 4-58 shows a year-over-year report for Regions.

FIGURE 4-58 Once you have the year-over-year report set up, you can swap any field in to column A.

Creating hierarchies

Back in Figures 4-53 through 4-55, you used the Expand Field to drill in to different date levels. If you build your pivot table using the Data Model, you can use the Diagram View in Power Pivot to create a formal hierarchy. Once you have a hierarchy in your pivot table, you can use the Drill Down and Drill Up buttons that are perpetually grayed out in the ribbon.

Consider the pivot table and pivot chart shown in Figure 4-59. The pivot table is built using the Data Model; when you choose Insert Pivot Table, make sure to choose Add This Data To The Data Model. The pivot table has regions in the Rows area and Products in the Columns area. The pivot chart is showing a stacked column chart.

FIGURE 4-59 A pivot table and a pivot chart without any hierarchy.

Follow these steps to add a hierarchy below Region:

1. Click the Manage icon in the Power Pivot tab of the ribbon.

2. On the Home tab of the Power Pivot for Excel window, click Diagram View.

3. Resize the Range table so you can see all the fields.

4. Click on the top field for the hierarchy: Region. Ctrl+click the remaining members of the hierarchy: Market and Rep.

5. Right-click on Region and choose Create Hierarchy (see Figure 4-60).

   

   FIGURE 4-60 Select Region, Market, and Rep. Right-click and choose Create Hierarchy.

6. Hierarchy1 will appear at the bottom of the table. Right-click the name and choose Rename. Type a meaningful name such as Geography (see Figure 4-61).

   

   FIGURE 4-61 The hierarchy is shown at the bottom of the table.

7. Carefully review the sequence of the hierarchy. In Figure 4-61, Rep is appearing before Market. This is incorrect. Right-click on Market and choose Move Up.

8. Close the Power Pivot window and return to Excel. After a brief pause, the PivotTable Fields list will show the table name (Range), the hierarchy name (Geography), and something called More Fields.

9. Remove Region from the Rows area. Choose the Geography hierarchy from the PivotTable Fields list. As shown in Figure 4-62, you have something very similar to Figure 4-59. However, note that Drill Down is now enabled in the ribbon.

   

   FIGURE 4-62 Plus signs appear next to each region. The Drill Down icon is enabled.

10. Select cell A7 for South. Click on Drill Down. The pivot table and pivot chart will change to show the markets in the South region (see Figure 4-63).

You can keep using Drill Down or Drill Up to travel through the hierarchy.

FIGURE 4-63 After drilling down on the South region, the markets in the South appear. The Drill Up icon is now enabled.

Next steps

In Chapter 5, “Performing calculations in pivot tables,” you’ll learn how to use pivot table formulas to add new virtual fields to a pivot table.