book

Microsoft Excel 2019 Pivot Table Data Crunching

Name: Microsoft Excel 2019 Pivot Table Data Crunching
ISBN: 9781509308200

by Bill Jelen, Michael Alexander

January 2019

Beginner to intermediate

513 pages

14h 29m

English

Microsoft Press

Read now

Unlock full access

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 tableWhen to use a pivot tableAnatomy of a pivot tableValues areaRows areaColumns areaFilters areaPivot tables behind the scenesPivot table backward compatibilityA word about compatibilityNext steps

Chapter 2 Creating a basic pivot table
Ensuring that data is in a Tabular layoutAvoiding storing data in section headingsAvoiding repeating groups as columnsEliminating gaps and blank cells in the data sourceApplying appropriate type formatting to fieldsSummary of good data source designHow to create a basic pivot tableAdding fields to a reportFundamentals of laying out a pivot table reportAdding layers to a pivot tableRearranging a pivot tableCreating a report filterUnderstanding the Recommended PivotTable and the Ideas featuresUsing slicersCreating a standard slicerCreating a Timeline slicerKeeping up with changes in the data sourceDealing with changes made to the existing data sourceDealing with an expanded data source range due to the addition of rows or columnsSharing the pivot cacheSide effects of sharing a pivot cacheSaving time with new PivotTable toolsDeferring layout updatesStarting over with one clickRelocating a pivot tableNext steps
Chapter 3 Customizing a pivot table
Making common cosmetic changesApplying a table style to restore gridlinesChanging the number format to add thousands separatorsReplacing blanks with zerosChanging a field nameMaking report layout changesUsing the Compact layoutUsing the Outline layoutUsing the traditional Tabular layoutControlling blank lines, grand totals, and other settingsCustomizing a pivot table’s appearance with styles and themesCustomizing a styleModifying styles with document themesChanging summary calculationsThe Excel team fixed the Count Of Revenue bugChanging the calculation in a value fieldShowing percentage of totalUsing % Of to compare one line to another lineShowing rankTracking running total and percentage of running totalDisplaying a change from a previous fieldTracking the percentage of a parent itemTracking relative importance with the Index optionAdding and removing subtotalsSuppressing subtotals with many row fieldsAdding multiple subtotals for one fieldFormatting one cell is new in Office 365Next steps
Chapter 4 Grouping, sorting, and filtering pivot data
Using the PivotTable Fields listDocking and undocking the PivotTable Fields listRearranging the PivotTable Fields listUsing the Areas section drop-downsSorting in a pivot tableSorting customers into high-to-low sequence based on revenueUsing a manual sort sequenceUsing a custom list for sortingFiltering a pivot table: an overviewUsing filters for row and column fieldsFiltering using the check boxesFiltering using the search boxFiltering using the Label Filters optionFiltering a Label column using information in a Values columnCreating a top-five report using the Top 10 filterFiltering using the Date filters in the Label drop-down menuFiltering using the Filters areaAdding fields to the Filters areaChoosing one item from a filterChoosing multiple items from a filterReplicating a pivot table report for each item in a filterFiltering using slicers and timelinesUsing timelines to filter by dateDriving multiple pivot tables from one set of slicersGrouping and creating hierarchies in a pivot tableGrouping numeric fieldsGrouping date fields manuallyIncluding years when grouping by monthsGrouping date fields by weekAutoGrouping pivot table datesUnderstanding how Excel decides what to AutoGroupUsing AutoGroupCreating an easy year-over-year reportCreating hierarchiesNext steps
Chapter 5 Performing calculations in pivot tables
Introducing calculated fields and calculated itemsMethod 1: Manually add a calculated field to the data sourceMethod 2: Use a formula outside a pivot table to create a calculated fieldMethod 3: Insert a calculated field directly into a pivot tableCreating a calculated fieldCreating a calculated itemUnderstanding the rules and shortcomings of pivot table calculationsRemembering the order of operator precedenceUsing cell references and named rangesUsing worksheet functionsUsing constantsReferencing totalsRules specific to calculated fieldsRules specific to calculated itemsManaging and maintaining pivot table calculationsEditing and deleting pivot table calculationsChanging the solve order of calculated itemsDocumenting formulasNext steps
Chapter 6 Using pivot charts and other visualizations
What is a pivot chart…really?Creating a pivot chartUnderstanding pivot field buttonsCreating a Pivot Chart from ScratchKeeping pivot chart rules in mindChanges in the underlying pivot table affect a pivot chartPlacement of data fields in a pivot table might not be best suited for a pivot chartA few formatting limitations still exist in Excel 2019Examining alternatives to using pivot chartsMethod 1: Turn the pivot table into hard valuesMethod 2: Delete the underlying pivot tableMethod 3: Distribute a picture of the pivot chartMethod 4: Use cells linked back to the pivot table as the source data for the chartUsing conditional formatting with pivot tablesAn example of using conditional formattingPreprogrammed scenarios for condition levelsCreating custom conditional formatting rulesUsing Power BI Custom Visuals in ExcelNext steps
Chapter 7 Analyzing disparate data sources with pivot tables
Using the Data ModelBuilding out your first Data ModelManaging relationships in the Data ModelAdding a new table to the Data ModelLimitations of the Data ModelBuilding a pivot table using external data sourcesBuilding a pivot table with Microsoft Access dataBuilding a pivot table with SQL Server dataLeveraging Power Query to extract and transform dataPower Query basicsUnderstanding applied stepsRefreshing Power Query dataManaging existing queriesUnderstanding column-level actionsUnderstanding table actionsPower Query connection typesOne more Power Query exampleNext steps
Chapter 8 Sharing dashboards with Power BI
Getting started with Power BI DesktopPreparing data in ExcelImporting data to Power BIGetting oriented to Power BIPreparing data in Power BIDefining synonyms in Power BI DesktopBuilding an interactive report with Power BI DesktopBuilding your first visualizationBuilding your second visualizationCross-filtering chartsCreating a drill-down hierarchyImporting a custom visualizationPublishing to Power BIDesigning for the mobile phonePublishing to a workspaceUsing artificial intelligence for InsightsUsing Q&A to query dataDesigning a workbook as an interactive web pageSharing a link to a web workbookNext steps
Chapter 9 Using cube formulas with the Data Model or OLAP data
Converting your pivot table to cube formulasIntroduction to OLAPConnecting to an OLAP cubeUnderstanding the structure of an OLAP cubeUnderstanding the limitations of OLAP pivot tablesCreating an offline cubeBreaking out of the pivot table mold with cube functionsExploring cube functionsAdding calculations to OLAP pivot tablesCreating calculated measuresCreating calculated membersManaging OLAP calculationsPerforming what-if analysis with OLAP dataNext steps
Chapter 10 Unlocking features with the Data Model and Power Pivot
Replacing VLOOKUP with the Data ModelUnlocking hidden features with the Data ModelCounting Distinct in a pivot tableIncluding filtered items in totalsCreating median in a pivot table using DAX measuresReporting text in the Values areaProcessing big data with Power QueryAdding a new column using Power QueryPower Query is like the Macro Recorder but betterAvoiding the Excel grid by loading to the Data ModelAdding a linked tableDefining a relationship between two tablesAdding calculated columns in the Power Pivot gridSorting one column by another columnCreating a pivot table from the Data ModelUsing advanced Power Pivot techniquesHandling complicated relationshipsUsing time intelligenceOvercoming limitations of the Data ModelEnjoying other benefits of Power PivotLearning moreNext steps
Chapter 11 Analyzing geographic data with 3D Map
Analyzing geographic data with 3D MapPreparing data for 3D MapGeocoding dataBuilding a column chart in 3D MapNavigating through the mapLabeling individual pointsBuilding pie or bubble charts on a mapUsing heat maps and region mapsExploring 3D Map settingsFine-tuning 3D MapCombining two data setsAnimating data over timeBuilding a tourCreating a video from 3D MapNext steps
Chapter 12 Enhancing pivot table reports with macros
Using macros with pivot table reportsRecording a macroCreating a user interface with form controlsAltering a recorded macro to add functionalityInserting a scrollbar form controlCreating a macro using Power QueryNext steps
Chapter 13 Using VBA to create pivot tables
Enabling VBA in your copy of ExcelUsing a file format that enables macrosVisual Basic EditorVisual Basic toolsThe macro recorderUnderstanding object-oriented codeLearning tricks of the tradeWriting code to handle a data range of any sizeUsing super-variables: Object variablesUsing With and End With to shorten codeUnderstanding versionsBuilding a pivot table in Excel VBAAdding fields to the data areaFormatting the pivot tableDealing with limitations of pivot tablesFilling blank cells in the data areaFilling blank cells in the row areaPreventing errors from inserting or deleting cellsControlling totalsConverting a pivot table to valuesPivot table 201: Creating a report showing revenue by categoryEnsuring that Tabular layout is utilizedRolling daily dates up to yearsEliminating blank cellsControlling the sort order with AutoSortChanging the default number formatSuppressing subtotals for multiple row fieldsCopying a finished pivot table as values to a new workbookHandling final formattingAdding subtotals to get page breaksPutting it all togetherCalculating with a pivot tableAddressing issues with two or more data fieldsUsing calculations other than SumUsing calculated data fieldsUsing calculated itemsCalculating groupsUsing Show Values As to perform other calculationsUsing advanced pivot table techniquesUsing AutoShow to produce executive overviewsUsing ShowDetail to filter a RecordsetCreating reports for each region or modelManually filtering two or more items in a pivot fieldUsing the conceptual filtersUsing the search filterSetting up slicers to filter a pivot tableUsing the Data Model in Excel 2019Adding both tables to the Data ModelCreating a relationship between the two tablesDefining the pivot cache and building the pivot tableAdding model fields to the pivot tableAdding numeric fields to the Values areaPutting it all togetherNext steps
Chapter 14 Advanced pivot table tips and techniques
Tip 1: Force pivot tables to refresh automaticallyTip 2: Refresh all pivot tables in a workbook at the same timeTip 3: Sort data items in a unique order, not ascending or descendingTip 4: Using (or prevent using) a custom list for sorting your pivot tableTip 5: Use pivot table defaults to change the behavior of all future pivot tablesTip 6: Turn pivot tables into hard dataTip 7: Fill the empty cells left by row fieldsOption 1: Implement the Repeat All Item Labels featureOption 2: Use Excel’s Go To Special functionalityTip 8: Add a rank number field to a pivot tableTip 9: Reduce the size of pivot table reportsDelete the source data worksheetTip 10: Create an automatically expanding data rangeTip 11: Compare tables using a pivot tableTip 12: AutoFilter a pivot tableTip 13: Force two number formats in a pivot tableTip 14: Format individual values in a pivot tableTip 15: Format sections of a pivot tableTip 16: Create a frequency distribution with a pivot tableTip 17: Use a pivot table to explode a data set to different tabsTip 18: Apply restrictions on pivot tables and pivot fieldsPivot table restrictionsPivot field restrictionsTip 19: Use a pivot table to explode a data set to different workbooksTip 20: Use percentage change from previous for year-over-yearTip 21: Do a two-way VLOOKUP with Power QueryNext steps
Chapter 15 Dr. Jekyll and Mr. GetPivotData
Avoiding the evil GetPivotData problemPreventing GetPivotData by typing the formulaSimply turning off GetPivotDataSpeculating on why Microsoft forced GetPivotData on usUsing GetPivotData to solve pivot table annoyancesBuilding an ugly pivot tableBuilding the shell reportUsing GetPivotData to populate the shell reportUpdating the report in future monthsConclusion
Index

Content preview from Microsoft Excel 2019 Pivot Table Data Crunching

Acknowledgments

Thanks to Mike Alexander for being my coauthor on the five previous editions of this book. At Microsoft, thanks to the Excel team for always being willing to answer questions about various features. At MrExcel.com, thanks to an entire community of people who are passionate about Excel. Finally, thanks to my wife, Mary Ellen, for her support during the writing process.

—Bill Jelen

Thanks to Bill Jelen for deciding to coauthor this book with me many editions ago. His knowledge of Excel still blows me away to this day. My deepest thanks to the professionals at Pearson Education for all the hours of work put into bringing this book to life. Thanks also to Bob Umlas, whose technical editing has helped us make numerous improvements ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft Excel 2016 Data Analysis and Business Modeling

Publisher Resources

ISBN: 9781509308200

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft Excel 2019 Pivot Table Data Crunching

by Bill Jelen, Michael Alexander

Acknowledgments

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.