Beginning PivotTables in Excel 2007: From Novice to Professional (Beginning from Novice to Professional)

Beginning Pivot Tables in Excel 2007......Page 1
Contents at a Glance......Page 5
Contents......Page 7
About the Author......Page 15
About the Technical Reviewer......Page 17
Acknowledgments......Page 19
How This Book Is Structured......Page 21
Contacting the Author......Page 23
What Is a Pivot Table?......Page 25
Understanding the Benefits of Using Pivot Tables......Page 29
Opening the Sample File......Page 30
Entering Similar Data in Each Column......Page 31
Removing Repeated Columns......Page 32
Creating an Excel Table......Page 33
Headings Are Automatically Created for New Columns......Page 35
Deleting Rows and Columns......Page 36
Table Is Automatically Named......Page 37
Data Is Easily Sorted......Page 38
Data Is Easily Filtered......Page 40
Saving the File......Page 41
Summary......Page 42
Exploring an Insurance Policy Example......Page 43
Creating the PivotTable Layout......Page 45
Adding Fields to the Pivot Table Layout......Page 47
Changing the Pivot Table Layout......Page 48
Adding More Fields to the Pivot Table......Page 49
Moving Fields in the Pivot Table Layout......Page 50
Charting the Data in a Pivot Table......Page 52
Summary......Page 53
Clearing a Pivot Table......Page 55
Adding Fields to Specific Areas of the Pivot Table......Page 56
Adding a Report Filter......Page 57
Changing the Filter......Page 59
Filtering for Multiple Items......Page 60
Removing a Report Filter......Page 61
Changing the Source Data......Page 62
Viewing New Data in the Pivot Table......Page 64
Changing the Summary Function......Page 65
Applying a PivotTable Style......Page 66
Deleting a Pivot Table......Page 67
Summary......Page 68
Exploring a Work Orders Example......Page 69
Using the Summary Functions......Page 70
Showing Multiple Value Fields......Page 73
Changing the Value Field Headings......Page 74
Showing Multiple Summaries for One Value Field......Page 75
Changing the Order of the Value Fields......Page 76
Changing the Position of the Value Fields......Page 77
Showing or Hiding Grand Totals......Page 78
Creating Subtotals......Page 81
Showing or Hiding Subtotals......Page 82
Showing Subtotals Above or Below Items......Page 83
Changing the Function for a Subtotal......Page 84
Creating Additional Subtotals......Page 85
Grouping Numbers......Page 86
Ungrouping Items......Page 88
Grouping Dates......Page 89
Grouping Selected Items......Page 90
Summary......Page 92
Applying Outline Form Layout......Page 93
Applying Tabular Form Layout......Page 94
Applying Compact Form Layout......Page 95
Using a Pivot Table Style......Page 96
Applying Banded Rows to the Pivot Table......Page 97
Removing Row Header Formatting......Page 98
Removing a Pivot Table Style......Page 99
Creating a Pivot Table Style......Page 100
Applying a Custom Pivot Table Style......Page 102
Modifying a Custom PivotTable Style......Page 103
Deleting a Custom Pivot Table Style......Page 104
Viewing the Current Theme......Page 105
Viewing the Theme Colors......Page 106
Viewing the Theme Effects......Page 107
Applying a Theme......Page 108
Summary......Page 109
Adding Report Filters......Page 111
Adding a Report Filter......Page 113
Adding Multiple Report Filters......Page 114
Changing the Order of Report Filters......Page 115
Arranging the Report Filters Horizontally......Page 116
Arranging the Report Filters Vertically......Page 118
Clearing All Filters......Page 119
Using Commands to Move Labels......Page 120
Moving Labels by Typing......Page 121
Sorting the Labels with a Ribbon Command......Page 122
Sorting Labels with a Context Menu......Page 123
Sorting from Smallest to Largest......Page 124
Sorting from Largest to Smallest......Page 125
Sorting from Left to Right......Page 126
Sorting Automatically When the Pivot Table Changes......Page 127
Preventing Automatic Sorting......Page 128
Restoring Automatic Sorting......Page 129
Creating a Custom List......Page 131
Sorting Without Using a Custom List......Page 132
Filtering for Begins With......Page 133
Viewing Filter and Sort Information......Page 135
Filtering Values......Page 136
Filtering Values for Row Fields......Page 137
Filtering for a Date Range......Page 138
Clearing the Filters......Page 139
Filtering for a Dynamic Date Range......Page 140
Applying a Manual Filter......Page 141
Including New Items in a Manual Filter......Page 142
Filtering by Selection......Page 143
Filtering for the Top Items......Page 144
Filtering for the Bottom Percent......Page 145
Summary......Page 146
Creating a Pivot Table from a Text File......Page 147
Importing the Text File......Page 148
Modifying the Connection......Page 152
Changing the Security Settings......Page 154
Creating the Pivot Table......Page 156
Connecting to the Access Query......Page 158
Modifying the Connection to the Access Query......Page 161
Using an Existing Connection to Create a Pivot Table......Page 165
Understanding OLAP Cubes......Page 166
Connecting to an OLAP Cube......Page 168
Modifying the Connection to the OLAP Cube......Page 169
Summary......Page 173
Updating an Excel Table......Page 175
Changing an Excel Data Source......Page 178
Refreshing All Pivot Tables in a Workbook......Page 181
Retaining Deleted Items......Page 182
Step 2: Create a New Connection and a Temporary Pivot Table......Page 184
Step 3: Change the Existing Pivot Table So It Uses the New Connection......Page 185
Step 4: Remove the Temporary Pivot Table and the Old Connection......Page 186
Refreshing the Pivot Table Regularly......Page 187
Saving Source Data with the File......Page 188
Summary......Page 189
Creating Calculations in a Pivot Table......Page 191
Creating Custom Calculations......Page 192
Using Difference From......Page 193
Using % Of......Page 195
Using % Difference From......Page 197
Using Running Total In......Page 199
Using % of Row......Page 200
Using % of Column......Page 201
Using % of Total......Page 202
Using Index......Page 203
Creating Formulas......Page 205
Creating a Calculated Field......Page 206
Editing a Calculated Field......Page 209
Creating a Complex Calculated Field......Page 210
Using Calculated Fields in Formulas......Page 213
Understanding a Calculated Field......Page 214
Creating a Calculated Item......Page 215
Editing a Calculated Item......Page 219
Creating a List of Formulas......Page 221
Changing the Solve Order......Page 222
Summary......Page 225
Using a Two-Color Scale......Page 227
Applying a Three-Color Scale......Page 230
Using an Icon Set......Page 231
Using Data Bars......Page 233
Formatting Top 10 Items......Page 236
Formatting Cells Between Two Values......Page 238
Formatting Labels in a Date Period......Page 240
Editing a Rule for Data Bars......Page 242
Changing the Order of Rules......Page 246
Changing the Pivot Table Layout......Page 249
Deleting a Rule......Page 251
Setting Format Options......Page 252
Controlling Column Width......Page 253
Hiding Buttons and Labels......Page 254
Summary......Page 256
Creating a Default Pivot Chart......Page 257
Exploring the Pivot Chart......Page 259
Using the PivotChart Filter Pane......Page 260
Moving Fields in the Pivot Chart......Page 262
Changing the Pivot Chart Layout......Page 263
Changing the Chart Style......Page 264
Changing the Chart Type......Page 266
Creating a Line Pivot Chart......Page 269
Formatting a Series......Page 271
Adding a Chart Title......Page 272
Changing the Pivot Chart Legend......Page 273
Moving a Pivot Chart......Page 274
Adding Data Labels to a Series......Page 275
Adding a Trend Line......Page 277
Creating a Variable Chart Title......Page 278
Summary......Page 279
Using the Show Details Feature......Page 281
Extracting Records with the Show Details Feature......Page 282
Formatting the Extracted Records......Page 284
Changing the Default Table Style......Page 285
Using Show Report Filter Pages......Page 286
Printing a Pivot Table......Page 288
Changing the Print Options......Page 290
Using the GetPivotData Function......Page 293
Using Cell References in GetPivotData Formulas......Page 295
Turning Off the Generate GetPivotData Feature......Page 296
Summary......Page 297
Saving a File......Page 299
Converting a File from an Earlier Version......Page 300
Turning On the ScreenTips Feature......Page 301
Adjusting Security Settings......Page 302
Index......Page 305