Get more out of your data with step-by-step tutorials for the Excel features you need to know Excel is still the most popular tool for organizing and analyzing data, and today's professionals are expected to have a high degree of fluency with it. Complex Excel tools like Pivot Tables, PowerQuery, and PowerPivot can help you manage and report on data the way you need to. Getting Great Results with Excel Pivot Tables, PowerQuery and PowerPivot offers a fresh look at how these tools can help you. Author and Microsoft Certified Trainer Thomas Fragale breaks down the topics into easy-to-use steps and screenshots, so you'll be able to put your advanced Excel skills into practice right away. Using Pivot Tables, PowerQuery, and PowerPivot, you can import, sort, transform, summarize, and present your data, all without having to be a programmer. This book takes the technical jargon out of using these features, so you can do your job more efficiently, bring value to your teams, and advance your career. The plain-English instructions inside will help anyone learn to get quick, meaningful results from your data, without having a degree in computing. Get easy-to-understand walkthroughs for analyzing data and creating dashboards in Microsoft Excel Learn how to organize data in Excel and use advanced features to find patterns and insights Summarize any kind of data faster and easier, leaving you more time for other tasks Turn raw numbers into new knowledge, reports, and charts that tell coworkers and customers what they need to know This book is great for anybody who has tons of raw data and needs to make sense of it. Managers, salespeople, finance professionals, marketers—along with anyone else who works with large amounts of data—will love this quick and easy guide to Pivot Tables, PowerQuery, and PowerPivot.

Table of Contents:
Foreword xxi Introduction xxiii Chapter 1 Preparing the Data for an Excel Pivot Table 1 What Is Data? 2 What the Data Should Look Like 3 Types of Data That Can Be Used in Excel Pivot Tables 6 Using Excel Data 7 Importing Data from External Data Sources into Excel 9 Importing Data from a Text/CSV File 10 Importing Data from an Access Database 13 Importing Data from a Web Page 13 Connecting to an ODBC Database 16 Importing Data from a Different Excel Workbook 17 Refreshing the Data 18 Using Power Query to Clean the Data 20 Using the Queries & Connections Window 21 Analyzing the Table 23 Splitting a Column 24 Merging Columns 25 Changing Data Types 26 Removing Columns and Rows 26 Undoing Steps in Power Query 28 Replacing Values 29 Trimming Spaces from the Beginning and End of the Data in a Column 30 Combining Tables Using the Append Query 31 Combining Tables Using the Merge Query 32 Creating Subtotals in Your Data Using the GroupBy Query 34 Using Power Query to Create Calculations 36 Calculating Age/Years of Service 37 Using the Built-in Date Functions 38 Using the Built-In Time Functions 39 Using a Custom Column for Other Calculations 40 Calculating a Line Total 40 Calculating the Days to Ship 41 Calculating the New Total 41 Changing or Viewing an Existing Custom Column 43 Using a Conditional Column for Calculations 44 Calculating a Reorder 44 Changing or Viewing an Existing Conditional Column 45 Summary 46 Chapter 2 Summarizing and Presenting Data with a Pivot Table 47 What Is a Pivot Table? 47 Making a Pivot Table from Scratch 48 The PivotTable Fields Window 50 Summarizing Data on One Field 53 The Default Calculation in the Values Section 55 Showing the Detail with a Drill Down 55 Creating a Cross Tab or Cross Reference 56 Adding More Layers of Detail to the Pivot Table 57 Creating and Managing Groups 57 Changing the Grouping Order 58 Adding Groupings to Columns 58 Creating Your Own Group 58 Removing Your Own Group 59 Using Recommended PivotTables and Analyzing Data 60 Creating a Pivot Table Using Recommended PivotTables 60 Creating a Pivot Table Using Analyze Data 61 Making the Pivot Table Look Better 62 Formatting Numbers 63 Managing Subtotals 63 Calculating Grand Totals 64 Working with Blank Rows 65 Changing the Layout 65 Applying Styles 67 Replacing Spaces and Other Options 68 Using Conditional Formatting 71 Summary 72 Chapter 3 Using Calculations in Pivot Tables 73 Using Built-In Calculations in Your Pivot Table 73 Adding Calculation Fields to the Pivot Table 74 Changing the Calculation Type of a Field 75 Changing the Order of the Calculations 78 The Calculations in the Summarize Values By Tab 78 The Calculations in the Show Values As Tab 79 Removing Calculations from the Pivot Table 86 Delaying Calculations in a Pivot Table 86 Changing the Way Errors Display in the Pivot Table 87 Creating Custom Calculations 89 Calculated Fields 89 To Change or Delete a Calculated Field 92 Calculated Items 93 To Change or Delete a Calculated Item 95 The Solve Order 96 Documenting Your Calculated Items and Fields 98 Limitations of Calculated Fields and Items 99 Refreshing Values on the Pivot Table 100 Manual Refresh 101 Automatic Refresh 102 Changing the Data Source 103 Using Numbers from the Pivot Table in Other Calculations 105 Enabling the GETPIVOTDATA Function 105 Using the GETPIVOTDATA Function 106 Summary 107 Chapter 4 Sorting and Filtering the Pivot Table 109 Sorting the Pivot Table 110 Sorting by a Column 110 Sorting by a Row 112 Putting the Pivot Table in Ascending Order 113 Creating Your Own Sort Order 113 Creating Your Own Sort Order by Dragging Cells 113 Creating Your Own Sort Order by Using a Custom List 114 Sorting a Pivot Table with Multiple Fields 119 Filtering the Pivot Table 121 Filter by Selection 122 Clearing the Filter 123 Using AutoFilters 123 Label Filters 125 Value Filters 127 Top 10 Filters 129 Date Filters 131 Showing Items with No Data 132 Using the Filters Section 134 Creating New Sheets from the Field in the Filters Section 136 Using Slicers and Timelines 137 Creating a Timeline 140 Updating Multiple Pivot Tables Using Slicers and Timelines 142 Removing a Slicer or a Timeline Window 143 Summary 143 Chapter 5 Making the Pivot Table More Visual with Charts 145 Creating a Chart from a Pivot Table 146 PivotChart Features 148 The Design Tab and the Format Tab for a Chart 151 Managing and Modifying Chart Elements 153 The Chart Title 154 Data Labels 156 Changing the Number Format of the Data Labels 156 Changing the Data Labels to Display Vertically 158 Trendlines 161 Adding a Trendline to a Chart 161 Creating a Forecast from a Trendline 162 Formatting the Numbers of the Y-Axis or X-Axis 164 Creating and Managing a Pie Chart 166 Changing the Data Labels on the Pie Chart to Percents 166 Separating a Section from a Pie Chart 168 Changing the Pie Chart to a Doughnut Chart 168 Creating a Combo Chart 169 Creating and Using Chart Templates 171 Summary 174 Chapter 6 Summarizing Data by Date and Time 175 Summarizing a Pivot Table by the Built-in Date Periods 176 Summarizing a Pivot Table by a Single Period of Time 176 Summarizing a Pivot Table by More Than One Time Period 180 Showing/Hiding the Totals for Each Time Period 180 Expanding/Collapsing Time Periods in the Pivot Table 181 Using Slicers to Filter the Pivot Table by Date and Time 181 Displaying Earliest and Most Recent Dates 184 Summarizing the Pivot Table by Number of Days 186 Summarizing the Pivot Table Using Custom Date Calculations 188 Summarizing the Pivot Table by Week 189 Using the Day of the Week in a Pivot Table 191 Using a Fiscal Period in a Pivot Table 194 Summary 197 Chapter 7 Creating a Pivot Table from Multiple Spreadsheets 199 Creating a Pivot Table from Multiple Ranges Using the PivotTable Wizard 200 Creating a Pivot Table Using the Data Model 205 Adding, Changing, or Deleting Relationships 210 Adding Additional Tables into the Data Model 211 Creating and Managing Sets 213 Changing or Deleting a Set 216 Summary 217 Chapter 8 Improving a Pivot Table with Power Pivot 219 Activating the Power Pivot Add-In 220 Using the Power Pivot Add-In 222 Adding Tables to the Data Model 223 Adding Formatted Excel Tables into the Data Model 224 Adding Tables from Other Sources into the Data Model 224 Joining Tables 227 Changing, Deleting, and Managing the Relationships 229 Creating a Pivot Table from the Power Pivot for Excel Window 230 Adding Calculations to a Pivot Table Using the Data Model 232 Adding a Calculated Column to a Table in the Data Model 232 Adding a Measure into the Data Model 234 Creating a KPI 236 Summary 239 Chapter 9 Pulling It All Together: Creating a Dashboard from Pivot Tables 241 Looking at a Finished Dashboard 242 Creating Your Own Dashboard 243 Adding Charts to Your Dashboard 244 Adding Slicers and Timelines to Your Dashboard 245 Displaying Totals and Percent of Totals 247 Working with Form Controls, Macros, and VBA Code 248 The Developer Tab 249 Adding Command Buttons 250 Saving a Workbook with a Macro 253 Adding a Combo Box 255 Adding Finishing Touches to Your Dashboard 260 Hiding Sheets 261 Hiding the Pivot Table, Gridlines, Column Headings, and the Formula 262 Summary 264 Index 265