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

Beginning PivotTables in Excel 2007 explains what Pivot Tables are, how you can benefit from using them, how to create them and modify them, and how to use their enhanced features. Using a Pivot Table in Microsoft Office Excel 2007 is a quick and exciting way to slice and dice a large amount of data. Carefully explains the benefits of using Pivot Tables for fast data analysis Provides a step-by-step approach to those new to Pivot Tables Offers tips and tricks that cannot be found elsewhere What you’ll learn Summarize thousands of records in a Pivot Table with a few clicks of the mouse. Quickly change the Pivot Table layout to view a different summary of the data. Filter a Pivot Table to focus on one region or the top 10 products. Add colors or icons to highlight the high or low results. Use calculations to enhance the Pivot Table summaries. Create visual impact with a pivot chart. Who is this book for? This book is for Excel users who want to learn about Pivot Tables. If you work with financial data, logistics records, sales orders, customer service reports, web site statistics, resource tracking, event planning, or any other set of records, a Pivot Table can help you review, analyze, monitor, and report on the data. When your reporting requirements change, you can make minor adjustments to the Pivot Table, instead of starting a worksheet summary from scratch. About the Apress Beginning Series The Beginning series from Apress is the right choice to get the information you need to land that crucial entry-level job. These books will teach you a standard and important technology from the ground up because they are explicitly designed to take you from à ̄¿Â1⁄2novice to professional.à ̄¿Â1⁄2 Youll start your journey by seeing what you need to knowbut without needless theory and filler. Youll build your skill set by learning how to put together real-world projects step by step. So whether your goal is your next career challenge or a new learning opportunity, the Beginning series from Apress will take you thereit is your trusted guide through unfamiliar territory! Related Titles from Apress Excel PivotTables Recipe Book: A Problem-Solution Approach Beginning Excel What-If Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and Solver Beginning SharePoint with Excel: From Novice to Professional Excel 2007: Beyond the Manual Excel as Your Database Definitive Guide to Excel VBA, Second Edition 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 __Beginning PivotTables in Excel 2007__ explains what Pivot Tables are, how you can benefit from using them, how to create them and modify them, and how to use their enhanced features. Using a Pivot Table in Microsoft Office Excel 2007 is a quick and exciting way to slice and dice a large amount of data. * Carefully explains the benefits of using Pivot Tables for fast data analysis * Provides a step-by-step approach to those new to Pivot Tables * Offers tips and tricks that cannot be found elsewhere ## What you’ll learn * Summarize thousands of records in a Pivot Table with a few clicks of the mouse. * Quickly change the Pivot Table layout to view a different summary of the data. * Filter a Pivot Table to focus on one region or the top 10 products. * Add colors or icons to highlight the high or low results. * Use calculations to enhance the Pivot Table summaries. * Create visual impact with a pivot chart. ## Who is this book for? This book is for Excel users who want to learn about Pivot Tables. If you work with financial data, logistics records, sales orders, customer service reports, web site statistics, resource tracking, event planning, or any other set of records, a Pivot Table can help you review, analyze, monitor, and report on the data. When your reporting requirements change, you can make minor adjustments to the Pivot Table, instead of starting a worksheet summary from scratch. ## About the Apress Beginning Series The Beginning series from Apress is the right choice to get the information you need to land that crucial entry-level job. These books will teach you a standard and important technology from the ground up because they are explicitly designed to take you from à ̄¿Â1⁄2novice to professional.à ̄¿Â1⁄2 Youll start your journey by seeing what you need to knowbut without needless theory and filler. Youll build your skill set by learning how to put together real-world projects step by step. So whether your goal is your next career challenge or a new learning opportunity, the Beginning series from Apress will take you thereit is your trusted guide through unfamiliar territory! ## Related Titles from Apress * Excel PivotTables Recipe Book: A Problem-Solution Approach * Beginning Excel What-If Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and Solver * Beginning SharePoint with Excel: From Novice to Professional * Excel 2007: Beyond the Manual * Excel as Your Database * Definitive Guide to Excel VBA, Second Edition

Beginning PivotTables in Excel 2007 explains what PivotTables are, how you can benefit from using them, how to create them and modify them, and how to use their enhanced features. Using a Pivot Table in Microsoft Office Excel 2007 is a quick and exciting way to slice and dice a large amount of data.

• Carefully explains the benefits of using Pivot Tables for fast data analysis
• Provides a step-by-step approach to those new to Pivot Tables
• Offers tips and tricks that cannot be found elsewhere

What you’ll learn

• Summarize thousands of records in a PivotTable with a few clicks of the mouse
• Quickly change the PivotTable layout to view a different summary of the data
• Filter a PivotTable to focus on one region or the top 10 products
• Add colors or icons to highlight the high or low results
• Use calculations to enhance the PivotTable summaries
• Create visual impact with a pivot chart

Who this book is for

This book is for Excel users who want to learn about PivotTables. If you work with financial data, logistics records, sales orders, customer service reports, web site statistics, resource tracking, event planning, or any other set of records, a PivotTable can help you review, analyze, monitor, and report on the data. When your reporting requirements change, you can make minor adjustments to the PivotTable, instead of starting a worksheet summary from scratch.

Using a PivotTable in Microsoft Office Excel 2007 is a quick and exciting way to slice and dice a large amount of data. This book explains what PivotTables are, how you can benefit from using them, how to create them and modify them, and how to use their enhanced features. It is for experienced Excel users who want to learn how to use Excel 2007 Pivot Tables for fast data analysis. The author is an internationally recognized Excel authority. Her web site on Excel, contextures.com, has over 175,000 visitors a month.