Microsoft Excel 2019 Pivot Table Data Crunching

Microsoft Excel 2019 Pivot Table Data Crunching

by Bill Jelen, Michael Alexander
Released January 2019
Publisher(s): Microsoft Press
ISBN: 9781509308200

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Renowned Excel experts Bill Jelen (MrExcel) and Michael Alexander help you crunch data from any source with Excel 2019 pivot tables.

 

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you've never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365. Drawing on more than 45 combined years of Excel experience, Bill Jelen and Michael Alexander offer practical “recipes” for solving real business problems, help you avoid common mistakes, and present tips and tricks you'll find nowhere else.

 

By reading this book, you will:

• Master easy, powerful ways to create, customize, change, and control pivot tables

• Control all future pivot tables using new pivot table defaults

• Transform huge data sets into clear summary reports

• Instantly highlight your most profitable customers, products, or regions

• Use Power Query to quickly import, clean, shape, and analyze disparate data sources

• Build geographical pivot tables with 3D Map

• Construct and share state-of-the-art dynamic dashboards

• Revamp analyses on the fly by dragging and dropping fields

• Build dynamic self-service reporting systems

• Share your pivot tables with colleagues

• Create data mashups using the full Power Pivot capabilities of Excel 2019 and Excel in Office 365

• Automate pivot tables with macros and VBA

• Save time by adapting reports with GetPivotData

• Discover today's most useful pivot table tips and shortcuts

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Dedication
  5. Contents at a Glance
  6. Contents
  7. Acknowledgments
  8. About the Authors
  9. Introduction
  10. Chapter 1 Pivot table fundamentals
    1. Why you should use a pivot table
    2. When to use a pivot table
    3. Anatomy of a pivot table
      1. Values area
      2. Rows area
      3. Columns area
      4. Filters area
    4. Pivot tables behind the scenes
    5. Pivot table backward compatibility
      1. A word about compatibility
    6. Next steps
  11. Chapter 2 Creating a basic pivot table
    1. Ensuring that data is in a Tabular layout
    2. Avoiding storing data in section headings
    3. Avoiding repeating groups as columns
    4. Eliminating gaps and blank cells in the data source
    5. Applying appropriate type formatting to fields
    6. Summary of good data source design
    7. How to create a basic pivot table
      1. Adding fields to a report
      2. Fundamentals of laying out a pivot table report
      3. Adding layers to a pivot table
      4. Rearranging a pivot table
      5. Creating a report filter
    8. Understanding the Recommended PivotTable and the Ideas features
    9. Using slicers
      1. Creating a standard slicer
      2. Creating a Timeline slicer
    10. Keeping up with changes in the data source
      1. Dealing with changes made to the existing data source
      2. Dealing with an expanded data source range due to the addition of rows or columns
    11. Sharing the pivot cache
    12. Side effects of sharing a pivot cache
    13. Saving time with new PivotTable tools
      1. Deferring layout updates
      2. Starting over with one click
      3. Relocating a pivot table
    14. Next steps
  12. Chapter 3 Customizing a pivot table
    1. Making common cosmetic changes
      1. Applying a table style to restore gridlines
      2. Changing the number format to add thousands separators
      3. Replacing blanks with zeros
      4. Changing a field name
    2. Making report layout changes
      1. Using the Compact layout
      2. Using the Outline layout
      3. Using the traditional Tabular layout
      4. Controlling blank lines, grand totals, and other settings
    3. Customizing a pivot table’s appearance with styles and themes
      1. Customizing a style
      2. Modifying styles with document themes
    4. Changing summary calculations
      1. The Excel team fixed the Count Of Revenue bug
    5. Changing the calculation in a value field
      1. Showing percentage of total
      2. Using % Of to compare one line to another line
      3. Showing rank
      4. Tracking running total and percentage of running total
      5. Displaying a change from a previous field
      6. Tracking the percentage of a parent item
      7. Tracking relative importance with the Index option
    6. Adding and removing subtotals
      1. Suppressing subtotals with many row fields
      2. Adding multiple subtotals for one field
    7. Formatting one cell is new in Office 365
    8. Next steps
  13. Chapter 4 Grouping, sorting, and filtering pivot data
    1. Using the PivotTable Fields list
      1. Docking and undocking the PivotTable Fields list
      2. Rearranging the PivotTable Fields list
      3. Using the Areas section drop-downs
    2. Sorting in a pivot table
      1. Sorting customers into high-to-low sequence based on revenue
      2. Using a manual sort sequence
      3. Using a custom list for sorting
    3. Filtering a pivot table: an overview
    4. Using filters for row and column fields
      1. Filtering using the check boxes
      2. Filtering using the search box
      3. Filtering using the Label Filters option
      4. Filtering a Label column using information in a Values column
      5. Creating a top-five report using the Top 10 filter
      6. Filtering using the Date filters in the Label drop-down menu
    5. Filtering using the Filters area
      1. Adding fields to the Filters area
      2. Choosing one item from a filter
      3. Choosing multiple items from a filter
      4. Replicating a pivot table report for each item in a filter
      5. Filtering using slicers and timelines
      6. Using timelines to filter by date
      7. Driving multiple pivot tables from one set of slicers
    6. Grouping and creating hierarchies in a pivot table
      1. Grouping numeric fields
      2. Grouping date fields manually
      3. Including years when grouping by months
      4. Grouping date fields by week
      5. AutoGrouping pivot table dates
      6. Understanding how Excel decides what to AutoGroup
      7. Using AutoGroup
      8. Creating an easy year-over-year report
    7. Creating hierarchies
    8. Next steps
  14. Chapter 5 Performing calculations in pivot tables
    1. Introducing calculated fields and calculated items
      1. Method 1: Manually add a calculated field to the data source
      2. Method 2: Use a formula outside a pivot table to create a calculated field
      3. Method 3: Insert a calculated field directly into a pivot table
    2. Creating a calculated field
    3. Creating a calculated item
    4. Understanding the rules and shortcomings of pivot table calculations
      1. Remembering the order of operator precedence
      2. Using cell references and named ranges
      3. Using worksheet functions
      4. Using constants
      5. Referencing totals
      6. Rules specific to calculated fields
      7. Rules specific to calculated items
    5. Managing and maintaining pivot table calculations
      1. Editing and deleting pivot table calculations
      2. Changing the solve order of calculated items
      3. Documenting formulas
    6. Next steps
  15. Chapter 6 Using pivot charts and other visualizations
    1. What is a pivot chart…really?
    2. Creating a pivot chart
      1. Understanding pivot field buttons
      2. Creating a Pivot Chart from Scratch
    3. Keeping pivot chart rules in mind
      1. Changes in the underlying pivot table affect a pivot chart
      2. Placement of data fields in a pivot table might not be best suited for a pivot chart
      3. A few formatting limitations still exist in Excel 2019
    4. Examining alternatives to using pivot charts
      1. Method 1: Turn the pivot table into hard values
      2. Method 2: Delete the underlying pivot table
      3. Method 3: Distribute a picture of the pivot chart
      4. Method 4: Use cells linked back to the pivot table as the source data for the chart
    5. Using conditional formatting with pivot tables
      1. An example of using conditional formatting
      2. Preprogrammed scenarios for condition levels
    6. Creating custom conditional formatting rules
      1. Using Power BI Custom Visuals in Excel
    7. Next steps
  16. Chapter 7 Analyzing disparate data sources with pivot tables
    1. Using the Data Model
      1. Building out your first Data Model
      2. Managing relationships in the Data Model
      3. Adding a new table to the Data Model
      4. Limitations of the Data Model
    2. Building a pivot table using external data sources
      1. Building a pivot table with Microsoft Access data
    3. Building a pivot table with SQL Server data
    4. Leveraging Power Query to extract and transform data
      1. Power Query basics
      2. Understanding applied steps
      3. Refreshing Power Query data
      4. Managing existing queries
      5. Understanding column-level actions
      6. Understanding table actions
      7. Power Query connection types
      8. One more Power Query example
    5. Next steps
  17. Chapter 8 Sharing dashboards with Power BI
    1. Getting started with Power BI Desktop
      1. Preparing data in Excel
      2. Importing data to Power BI
      3. Getting oriented to Power BI
      4. Preparing data in Power BI
      5. Defining synonyms in Power BI Desktop
    2. Building an interactive report with Power BI Desktop
      1. Building your first visualization
      2. Building your second visualization
      3. Cross-filtering charts
      4. Creating a drill-down hierarchy
      5. Importing a custom visualization
    3. Publishing to Power BI
      1. Designing for the mobile phone
      2. Publishing to a workspace
      3. Using artificial intelligence for Insights
      4. Using Q&A to query data
    4. Designing a workbook as an interactive web page
    5. Sharing a link to a web workbook
    6. Next steps
  18. Chapter 9 Using cube formulas with the Data Model or OLAP data
    1. Converting your pivot table to cube formulas
    2. Introduction to OLAP
    3. Connecting to an OLAP cube
    4. Understanding the structure of an OLAP cube
    5. Understanding the limitations of OLAP pivot tables
    6. Creating an offline cube
    7. Breaking out of the pivot table mold with cube functions
      1. Exploring cube functions
    8. Adding calculations to OLAP pivot tables
      1. Creating calculated measures
      2. Creating calculated members
      3. Managing OLAP calculations
      4. Performing what-if analysis with OLAP data
    9. Next steps
  19. Chapter 10 Unlocking features with the Data Model and Power Pivot
    1. Replacing VLOOKUP with the Data Model
    2. Unlocking hidden features with the Data Model
      1. Counting Distinct in a pivot table
      2. Including filtered items in totals
      3. Creating median in a pivot table using DAX measures
      4. Reporting text in the Values area
    3. Processing big data with Power Query
      1. Adding a new column using Power Query
      2. Power Query is like the Macro Recorder but better
      3. Avoiding the Excel grid by loading to the Data Model
      4. Adding a linked table
      5. Defining a relationship between two tables
      6. Adding calculated columns in the Power Pivot grid
      7. Sorting one column by another column
      8. Creating a pivot table from the Data Model
    4. Using advanced Power Pivot techniques
      1. Handling complicated relationships
      2. Using time intelligence
    5. Overcoming limitations of the Data Model
      1. Enjoying other benefits of Power Pivot
      2. Learning more
    6. Next steps
  20. Chapter 11 Analyzing geographic data with 3D Map
    1. Analyzing geographic data with 3D Map
      1. Preparing data for 3D Map
      2. Geocoding data
      3. Building a column chart in 3D Map
      4. Navigating through the map
      5. Labeling individual points
      6. Building pie or bubble charts on a map
      7. Using heat maps and region maps
      8. Exploring 3D Map settings
      9. Fine-tuning 3D Map
      10. Combining two data sets
      11. Animating data over time
      12. Building a tour
      13. Creating a video from 3D Map
    2. Next steps
  21. Chapter 12 Enhancing pivot table reports with macros
    1. Using macros with pivot table reports
    2. Recording a macro
    3. Creating a user interface with form controls
    4. Altering a recorded macro to add functionality
      1. Inserting a scrollbar form control
    5. Creating a macro using Power Query
    6. Next steps
  22. Chapter 13 Using VBA to create pivot tables
    1. Enabling VBA in your copy of Excel
    2. Using a file format that enables macros
    3. Visual Basic Editor
    4. Visual Basic tools
    5. The macro recorder
    6. Understanding object-oriented code
    7. Learning tricks of the trade
      1. Writing code to handle a data range of any size
      2. Using super-variables: Object variables
      3. Using With and End With to shorten code
    8. Understanding versions
    9. Building a pivot table in Excel VBA
      1. Adding fields to the data area
      2. Formatting the pivot table
    10. Dealing with limitations of pivot tables
      1. Filling blank cells in the data area
      2. Filling blank cells in the row area
      3. Preventing errors from inserting or deleting cells
      4. Controlling totals
      5. Converting a pivot table to values
    11. Pivot table 201: Creating a report showing revenue by category
      1. Ensuring that Tabular layout is utilized
      2. Rolling daily dates up to years
      3. Eliminating blank cells
      4. Controlling the sort order with AutoSort
      5. Changing the default number format
      6. Suppressing subtotals for multiple row fields
      7. Copying a finished pivot table as values to a new workbook
      8. Handling final formatting
      9. Adding subtotals to get page breaks
      10. Putting it all together
    12. Calculating with a pivot table
      1. Addressing issues with two or more data fields
      2. Using calculations other than Sum
      3. Using calculated data fields
      4. Using calculated items
      5. Calculating groups
      6. Using Show Values As to perform other calculations
    13. Using advanced pivot table techniques
      1. Using AutoShow to produce executive overviews
      2. Using ShowDetail to filter a Recordset
      3. Creating reports for each region or model
      4. Manually filtering two or more items in a pivot field
      5. Using the conceptual filters
      6. Using the search filter
      7. Setting up slicers to filter a pivot table
    14. Using the Data Model in Excel 2019
      1. Adding both tables to the Data Model
      2. Creating a relationship between the two tables
      3. Defining the pivot cache and building the pivot table
      4. Adding model fields to the pivot table
      5. Adding numeric fields to the Values area
      6. Putting it all together
    15. Next steps
  23. Chapter 14 Advanced pivot table tips and techniques
    1. Tip 1: Force pivot tables to refresh automatically
    2. Tip 2: Refresh all pivot tables in a workbook at the same time
    3. Tip 3: Sort data items in a unique order, not ascending or descending
    4. Tip 4: Using (or prevent using) a custom list for sorting your pivot table
    5. Tip 5: Use pivot table defaults to change the behavior of all future pivot tables
    6. Tip 6: Turn pivot tables into hard data
    7. Tip 7: Fill the empty cells left by row fields
      1. Option 1: Implement the Repeat All Item Labels feature
      2. Option 2: Use Excel’s Go To Special functionality
    8. Tip 8: Add a rank number field to a pivot table
    9. Tip 9: Reduce the size of pivot table reports
    10. Delete the source data worksheet
    11. Tip 10: Create an automatically expanding data range
    12. Tip 11: Compare tables using a pivot table
    13. Tip 12: AutoFilter a pivot table
    14. Tip 13: Force two number formats in a pivot table
    15. Tip 14: Format individual values in a pivot table
    16. Tip 15: Format sections of a pivot table
    17. Tip 16: Create a frequency distribution with a pivot table
    18. Tip 17: Use a pivot table to explode a data set to different tabs
    19. Tip 18: Apply restrictions on pivot tables and pivot fields
      1. Pivot table restrictions
      2. Pivot field restrictions
    20. Tip 19: Use a pivot table to explode a data set to different workbooks
    21. Tip 20: Use percentage change from previous for year-over-year
    22. Tip 21: Do a two-way VLOOKUP with Power Query
    23. Next steps
  24. Chapter 15 Dr. Jekyll and Mr. GetPivotData
    1. Avoiding the evil GetPivotData problem
      1. Preventing GetPivotData by typing the formula
      2. Simply turning off GetPivotData
      3. Speculating on why Microsoft forced GetPivotData on us
    2. Using GetPivotData to solve pivot table annoyances
      1. Building an ugly pivot table
      2. Building the shell report
      3. Using GetPivotData to populate the shell report
      4. Updating the report in future months
    3. Conclusion
  25. Index

Product information

  • Title: Microsoft Excel 2019 Pivot Table Data Crunching
  • Author(s): Bill Jelen, Michael Alexander
  • Release date: January 2019
  • Publisher(s): Microsoft Press
  • ISBN: 9781509308200