&> Introduction What You Will Learn from This Book .1What Is New in Excel 2016''s Pivot Tables .2Skills Required to Use This Book .3Invention of the Pivot Table.4Sample Files Used in This Book .6Conventions Used in This Book .6 Referring to Versions .7 Referring to Ribbon Commands.

7 Special Elements .7 1 Pivot Table Fundamentals . 9Defining a Pivot Table .9Why You Should Use a Pivot Table .10 Advantages of Using a Pivot Table .11When to Use a Pivot Table .12Anatomy of a Pivot Table .12 Values Area .

12 Rows Area.13 Columns Area .14 Filters Area .14Pivot Tables Behind the Scenes .14Pivot Table Backward Compatibility .15 A Word About Compatibility .16Next Steps.17 2 Creating a Basic Pivot Table .

19Preparing Data for Pivot Table Reporting .19 Ensuring That Data Is in a Tabular Layout.20 Avoiding Storing Data in Section Headings .20 Avoiding Repeating Groups as Columns .21 Eliminating Gaps and Blank Cells in the Data Source .22 Applying Appropriate Type Formatting to Fields .22 Summary of Good Data Source Design .22How to Create a Basic Pivot Table .

24 Adding Fields to a Report .26 Fundamentals of Laying Out a Pivot Table Report .27 Adding Layers to a Pivot Table .28 Rearranging a Pivot Table .29 Creating a Report Filter.31Understanding the Recommended Pivot Table Feature.31Using Slicers .33 Creating a Standard Slicer .

33 Creating a Timeline Slicer .36Keeping Up with Changes in the Data Source .39 Dealing with Changes Made to the Existing Data Source .39 Dealing with an Expanded Data Source Range Due to the Addition of Rows or Columns .39Sharing the Pivot Cache .40Saving Time with New Pivot Table Tools .41 Deferring Layout Updates .41 Starting Over with One Click .

42 Relocating a Pivot Table .43Next Steps.43 3 Customizing a Pivot Table .45Making Common Cosmetic Changes .46 Applying a Table Style to Restore Gridlines .47 Changing the Number Format to Add Thousands Separators .48 Replacing Blanks with Zeros .49 Changing a Field Name .

51Making Report Layout Changes .52 Using the Compact Layout .52 Using the Outline Layout .54 Using the Traditional Tabular Layout .55 Controlling Blank Lines, Grand Totals, and Other Settings .57Customizing a Pivot Table''s Appearance with Styles and Themes .60 Customizing a Style .61 Modifying Styles with Document Themes .

62Changing Summary Calculations .63 Understanding Why One Blank Cell Causes a Count .63 Using Functions Other Than Count or Sum .65Adding and Removing Subtotals .65 Suppressing Subtotals with Many Row Fields .66 Adding Multiple Subtotals for One Field .67Changing the Calculation in a Value Field .67 Showing Percentage of Total .

70 Using % Of to Compare One Line to Another Line .71 Showing Rank .71 Tracking Running Total and Percentage of Running Total .72 Displaying a Change from a Previous Field .73 Tracking the Percentage of a Parent Item .73 Tracking Relative Importance with the Index Option .74Next Steps.75 4 Grouping, Sorting, and Filtering Pivot Data .

77Automatically Grouping Dates .77 Undoing Automatic Grouping .78 Understanding How Excel 2016 Decides What to Group .78 Grouping Date Fields Manually .79 Including Years When Grouping by Months .80 Grouping Date Fields by Week .81 Grouping Numeric Fields .82Using the PivotTable Fields List .

85 Docking and Undocking the PivotTable Fields List .87 Rearranging the PivotTable Fields List.87 Using the Areas Section Drop-Downs .88Sorting in a Pivot Table .89 Sorting Customers into High-to-Low Sequence Based on Revenue .89 Using a Manual Sort Sequence .92 Using a Custom List for Sorting .93Filtering a Pivot Table: An Overview .

95Using Filters for Row and Column Fields .96 Filtering Using the Check Boxes .96 Filtering Using the Search Box .97 Filtering Using the Label Filters Option .98 Filtering a Label Column Using Information in a Values Column .99 Creating a Top-Five Report Using the Top 10 Filter .101 Filtering Using the Date Filters in the Label Drop-down .103Filtering Using the Filters Area .

104 Adding Fields to the Filters Area .104 Choosing One Item from a Filter .105 Choosing Multiple Items from a Filter .105 Replicating a Pivot Table Report for Each Item in a Filter .105 Filtering Using Slicers and Timelines .107 Using Timelines to Filter by Date .109 Driving Multiple Pivot Tables from One Set of Slicers .110Next Steps.

112 5 Performing Calculations in Pivot Tables .113Introducing Calculated Fields and Calculated Items .113 Method 1: Manually Add a Calculated Field to the Data Source .114 Method 2: Use a Formula Outside a Piv.