Microsoft Excel 2019 Pivot Table Data Crunching by Alexander, Michael -- Read -- Imperial Library of Trantor

Index

Cover Page Title Page Copyright Page Dedication Contents at a Glance Contents Acknowledgments About the Authors Introduction Chapter 1 Pivot table fundamentals

Why you should use a pivot table When to use a pivot table Anatomy of a pivot table

Values area Rows area Columns area Filters area

Pivot tables behind the scenes Pivot table backward compatibility

A word about compatibility

Next steps

Chapter 2 Creating a basic pivot table

Ensuring that data is in a Tabular layout Avoiding storing data in section headings Avoiding repeating groups as columns Eliminating gaps and blank cells in the data source Applying appropriate type formatting to fields Summary of good data source design How to create a basic pivot table

Adding fields to a report Fundamentals of laying out a pivot table report Adding layers to a pivot table Rearranging a pivot table Creating a report filter

Understanding the Recommended PivotTable and the Ideas features Using slicers

Creating a standard slicer Creating a Timeline slicer

Keeping up with changes in the data source

Dealing with changes made to the existing data source Dealing with an expanded data source range due to the addition of rows or columns

Sharing the pivot cache Side effects of sharing a pivot cache Saving time with new PivotTable tools

Deferring layout updates Starting over with one click Relocating a pivot table

Next steps

Chapter 3 Customizing a pivot table

Making common cosmetic changes

Applying a table style to restore gridlines Changing the number format to add thousands separators Replacing blanks with zeros Changing a field name

Making report layout changes

Using the Compact layout Using the Outline layout Using the traditional Tabular layout Controlling blank lines, grand totals, and other settings

Customizing a pivot table’s appearance with styles and themes

Customizing a style Modifying styles with document themes

Changing summary calculations

The Excel team fixed the Count Of Revenue bug

Changing the calculation in a value field

Showing percentage of total Using % Of to compare one line to another line Showing rank Tracking running total and percentage of running total Displaying a change from a previous field Tracking the percentage of a parent item Tracking relative importance with the Index option

Adding and removing subtotals

Suppressing subtotals with many row fields Adding multiple subtotals for one field

Formatting one cell is new in Office 365 Next steps

Chapter 4 Grouping, sorting, and filtering pivot data

Using the PivotTable Fields list

Docking and undocking the PivotTable Fields list Rearranging the PivotTable Fields list Using the Areas section drop-downs

Sorting in a pivot table

Sorting customers into high-to-low sequence based on revenue Using a manual sort sequence Using a custom list for sorting

Filtering a pivot table: an overview Using filters for row and column fields

Filtering using the check boxes Filtering using the search box Filtering using the Label Filters option Filtering a Label column using information in a Values column Creating a top-five report using the Top 10 filter Filtering using the Date filters in the Label drop-down menu

Filtering using the Filters area

Adding fields to the Filters area Choosing one item from a filter Choosing multiple items from a filter Replicating a pivot table report for each item in a filter Filtering using slicers and timelines Using timelines to filter by date Driving multiple pivot tables from one set of slicers

Grouping and creating hierarchies in a pivot table

Grouping numeric fields Grouping date fields manually Including years when grouping by months Grouping date fields by week AutoGrouping pivot table dates Understanding how Excel decides what to AutoGroup Using AutoGroup Creating an easy year-over-year report

Creating hierarchies Next steps

Chapter 5 Performing calculations in pivot tables

Introducing calculated fields and calculated items

Method 1: Manually add a calculated field to the data source Method 2: Use a formula outside a pivot table to create a calculated field Method 3: Insert a calculated field directly into a pivot table

Creating a calculated field Creating a calculated item Understanding the rules and shortcomings of pivot table calculations

Remembering the order of operator precedence Using cell references and named ranges Using worksheet functions Using constants Referencing totals Rules specific to calculated fields Rules specific to calculated items

Managing and maintaining pivot table calculations

Editing and deleting pivot table calculations Changing the solve order of calculated items Documenting formulas

Next steps

Chapter 6 Using pivot charts and other visualizations

What is a pivot chart…really? Creating a pivot chart

Understanding pivot field buttons Creating a Pivot Chart from Scratch

Keeping pivot chart rules in mind

Changes in the underlying pivot table affect a pivot chart Placement of data fields in a pivot table might not be best suited for a pivot chart A few formatting limitations still exist in Excel 2019

Examining alternatives to using pivot charts

Method 1: Turn the pivot table into hard values Method 2: Delete the underlying pivot table Method 3: Distribute a picture of the pivot chart Method 4: Use cells linked back to the pivot table as the source data for the chart

Using conditional formatting with pivot tables

An example of using conditional formatting Preprogrammed scenarios for condition levels

Creating custom conditional formatting rules

Using Power BI Custom Visuals in Excel

Next steps

Chapter 7 Analyzing disparate data sources with pivot tables

Using the Data Model

Building out your first Data Model Managing relationships in the Data Model Adding a new table to the Data Model Limitations of the Data Model

Building a pivot table using external data sources

Building a pivot table with Microsoft Access data

Building a pivot table with SQL Server data Leveraging Power Query to extract and transform data

Power Query basics Understanding applied steps Refreshing Power Query data Managing existing queries Understanding column-level actions Understanding table actions Power Query connection types One more Power Query example

Next steps

Chapter 8 Sharing dashboards with Power BI

Getting started with Power BI Desktop

Preparing data in Excel Importing data to Power BI Getting oriented to Power BI Preparing data in Power BI Defining synonyms in Power BI Desktop

Building an interactive report with Power BI Desktop

Building your first visualization Building your second visualization Cross-filtering charts Creating a drill-down hierarchy Importing a custom visualization

Publishing to Power BI

Designing for the mobile phone Publishing to a workspace Using artificial intelligence for Insights Using Q&A to query data

Designing a workbook as an interactive web page Sharing a link to a web workbook Next steps

Chapter 9 Using cube formulas with the Data Model or OLAP data

Converting your pivot table to cube formulas Introduction to OLAP Connecting to an OLAP cube Understanding the structure of an OLAP cube Understanding the limitations of OLAP pivot tables Creating an offline cube Breaking out of the pivot table mold with cube functions

Exploring cube functions

Adding calculations to OLAP pivot tables

Creating calculated measures Creating calculated members Managing OLAP calculations Performing what-if analysis with OLAP data

Next steps

Chapter 10 Unlocking features with the Data Model and Power Pivot

Replacing VLOOKUP with the Data Model Unlocking hidden features with the Data Model

Counting Distinct in a pivot table Including filtered items in totals Creating median in a pivot table using DAX measures Reporting text in the Values area

Processing big data with Power Query

Adding a new column using Power Query Power Query is like the Macro Recorder but better Avoiding the Excel grid by loading to the Data Model Adding a linked table Defining a relationship between two tables Adding calculated columns in the Power Pivot grid Sorting one column by another column Creating a pivot table from the Data Model

Using advanced Power Pivot techniques

Handling complicated relationships Using time intelligence

Overcoming limitations of the Data Model

Enjoying other benefits of Power Pivot Learning more

Next steps

Chapter 11 Analyzing geographic data with 3D Map

Analyzing geographic data with 3D Map

Preparing data for 3D Map Geocoding data Building a column chart in 3D Map Navigating through the map Labeling individual points Building pie or bubble charts on a map Using heat maps and region maps Exploring 3D Map settings Fine-tuning 3D Map Combining two data sets Animating data over time Building a tour Creating a video from 3D Map

Next steps

Chapter 12 Enhancing pivot table reports with macros

Using macros with pivot table reports Recording a macro Creating a user interface with form controls Altering a recorded macro to add functionality

Inserting a scrollbar form control

Creating a macro using Power Query Next steps

Chapter 13 Using VBA to create pivot tables

Enabling VBA in your copy of Excel Using a file format that enables macros Visual Basic Editor Visual Basic tools The macro recorder Understanding object-oriented code Learning tricks of the trade

Writing code to handle a data range of any size Using super-variables: Object variables Using With and End With to shorten code

Understanding versions Building a pivot table in Excel VBA

Adding fields to the data area Formatting the pivot table

Dealing with limitations of pivot tables

Filling blank cells in the data area Filling blank cells in the row area Preventing errors from inserting or deleting cells Controlling totals Converting a pivot table to values

Pivot table 201: Creating a report showing revenue by category

Ensuring that Tabular layout is utilized Rolling daily dates up to years Eliminating blank cells Controlling the sort order with AutoSort Changing the default number format Suppressing subtotals for multiple row fields Copying a finished pivot table as values to a new workbook Handling final formatting Adding subtotals to get page breaks Putting it all together

Calculating with a pivot table

Addressing issues with two or more data fields Using calculations other than Sum Using calculated data fields Using calculated items Calculating groups Using Show Values As to perform other calculations

Using advanced pivot table techniques

Using AutoShow to produce executive overviews Using ShowDetail to filter a Recordset Creating reports for each region or model Manually filtering two or more items in a pivot field Using the conceptual filters Using the search filter Setting up slicers to filter a pivot table

Using the Data Model in Excel 2019

Adding both tables to the Data Model Creating a relationship between the two tables Defining the pivot cache and building the pivot table Adding model fields to the pivot table Adding numeric fields to the Values area Putting it all together

Next steps

Chapter 14 Advanced pivot table tips and techniques

Tip 1: Force pivot tables to refresh automatically Tip 2: Refresh all pivot tables in a workbook at the same time Tip 3: Sort data items in a unique order, not ascending or descending Tip 4: Using (or prevent using) a custom list for sorting your pivot table Tip 5: Use pivot table defaults to change the behavior of all future pivot tables Tip 6: Turn pivot tables into hard data Tip 7: Fill the empty cells left by row fields

Option 1: Implement the Repeat All Item Labels feature Option 2: Use Excel’s Go To Special functionality

Tip 8: Add a rank number field to a pivot table Tip 9: Reduce the size of pivot table reports Delete the source data worksheet Tip 10: Create an automatically expanding data range Tip 11: Compare tables using a pivot table Tip 12: AutoFilter a pivot table Tip 13: Force two number formats in a pivot table Tip 14: Format individual values in a pivot table Tip 15: Format sections of a pivot table Tip 16: Create a frequency distribution with a pivot table Tip 17: Use a pivot table to explode a data set to different tabs Tip 18: Apply restrictions on pivot tables and pivot fields

Pivot table restrictions Pivot field restrictions

Tip 19: Use a pivot table to explode a data set to different workbooks Tip 20: Use percentage change from previous for year-over-year Tip 21: Do a two-way VLOOKUP with Power Query Next steps

Chapter 15 Dr. Jekyll and Mr. GetPivotData

Avoiding the evil GetPivotData problem

Preventing GetPivotData by typing the formula Simply turning off GetPivotData Speculating on why Microsoft forced GetPivotData on us

Using GetPivotData to solve pivot table annoyances

Building an ugly pivot table Building the shell report Using GetPivotData to populate the shell report Updating the report in future months

Conclusion

Index

← Prev
Back
Next →

← Prev
Back
Next →