Rearranging the PivotTable Fields listĪs shown in Figure 4-4, a small gear-wheel icon appears near the top of the PivotTable Fields list. Note that you can dock the PivotTable Fields list on either the right side or the left side of the screen. Eventually, Excel gets the hint and redocks it. Pretend that you are trying to remove the floating Fields list completely from 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. Drag to the left to enable the pane to float anywhere in your Excel window.Īfter you have undocked the PivotTable Fields list, you might find that it is difficult to redock it on either side of the screen. Hover over the green PivotTable Fields heading in the pane, and the mouse pointer changes to a four-headed arrow. The PivotTable Fields list starts out docked on the right side of the Excel window. Docking and undocking the PivotTable Fields list After you hover as shown in Figure 4-3, you can directly access the same customer drop-down menu shown in Figure 4-1.įIGURE 4-3 Hover over the field in the top of the Fields list to directly access the sorting and filtering settings for that field. 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. Those visible drop-down menus do not contain the sorting and filtering options. 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. This should be enough to convince you to abandon the Compact layout. If you are making five changes to the Customer field, you are reselecting Customer over and over and over and over and over.
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 means the drop-down menu always offers sorting and filtering options for Region.
Both fields are crammed into column A, with the silly heading Row Labels. When you leave the pivot table in the Compact form, there are not separate headings for Region and Customer.
Each of these separate drop-downs offers great settings for sorting and filtering. In Figure 4-1, a Region drop-down menu appears in A3. Although there are many good reasons for this, one is illustrated in Figures 4-1 and 4-2.įIGURE 4-2 In Compact form, one single drop-down menu tries to control sorting and filtering for all the row fields. I use Pivot Table Defaults to make sure my pivot tables start in Tabular layout instead of Compact layout. 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.Īs you’ve seen in these pages, I rarely use the Compact form for a pivot table. The entry points for sorting and filtering are spread throughout the Excel interface. Then, a detailed look at sorting, filtering, and grouping a pivot table.
Grouping will be covered last in this chapter.įirst, a quick overview of the PivotTable Fields. 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. Removing the feature was a good move, as the feature proved hard to predict. With Excel 2019, Microsoft has reversed the auto date grouping added to Excel 2016. Group and create hierarchies in a pivot table