Microsoft Excel Pivot Table Data Crunching (Office 2021 and Microsoft 365)
- Length: 544 pages
- Edition: 1
- Language: English
- Publisher: Microsoft Press
- Publication Date: 2021-12-28
- ISBN-10: 0137521839
- ISBN-13: 9780137521838
- Sales Rank: #3155082 (See Top 100 Books)
enowned Excel expert Bill Jelen (MrExcel) helps you crunch data from any source with Excel pivot tables.
Use Excel 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 and Excel in Office 365. Drawing on years of Excel experience, Bill Jelen offers practical “recipes” for solving real business problems, helps you avoid common mistakes, and presents 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 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
Cover Page Title Page Copyright Page Dedication Page Contents at a Glance Contents Acknowledgments About the Author Introduction Chapter 1 Pivot table fundamentals Why you should use a pivot table When to use a pivot table Anatomy of a pivot table Pivot tables behind the scenes Pivot table backward compatibility Next steps Chapter 2 Creating a basic pivot table How to create a basic pivot table Understanding the Recommended PivotTable and the Analyze Data features Using slicers to filter your report Keeping up with changes in the data source Sharing the pivot cache or creating a new cache Side effects of sharing a pivot cache Saving time with PivotTable tools Next steps Chapter 3 Customizing a pivot table Making common cosmetic changes Making report layout changes Customizing a pivot table’s appearance with styles and themes Changing summary calculations Changing the calculation in a value field Adding and removing subtotals Formatting one cell is new in Microsoft 365 Next steps Chapter 4 Grouping, sorting, and filtering pivot data Using the PivotTable Fields list Sorting in a pivot table Filtering a pivot table: an overview Using filters for row and column fields Filtering using the Filters area Grouping and creating hierarchies in a pivot table Creating hierarchies Next steps Chapter 5 Performing calculations in pivot tables Introducing calculated fields and calculated items Creating a calculated field Creating a calculated item Understanding the rules and shortcomings of pivot table calculations Managing and maintaining pivot table calculations Next steps Chapter 6 Using pivot charts and other visualizations What is a pivot chart…really? Creating a pivot chart Keeping pivot chart rules in mind Examining alternatives to using pivot charts Using conditional formatting with pivot tables Creating custom conditional formatting rules Next steps Chapter 7 Analyzing disparate data sources with pivot tables Using the Data Model Building a pivot table using external data sources Leveraging Power Query to extract and transform data Next steps Chapter 8 Sharing dashboards with Power BI Getting started with Power BI Desktop Building an interactive report with Power BI Desktop Publishing to Power BI 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 Adding calculations to OLAP pivot tables 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 Processing big data with Power Query Using advanced Power Pivot techniques Overcoming limitations of the Data Model Next steps Chapter 11 Analyzing geographic data with 3D Map Analyzing geographic data with 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 Creating a macro using Power Query Next steps Chapter 13 Using VBA or TypeScript 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 Understanding versions Building a pivot table in Excel VBA Dealing with limitations of pivot tables Pivot table 201: Creating a report showing revenue by category Calculating with a pivot table Using advanced pivot table techniques Using the Data Model in Excel Using TypeScript in Excel Online to create pivot tables 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 Tip 8: Add a rank number field to a pivot table Tip 9: Reduce the size of pivot table reports 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 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 Tip 22: Slicer to control data from two different data sets Tip 23: Format your slicers Next steps Chapter 15 Dr. Jekyll and Mr. GetPivotData Avoiding the evil GetPivotData problem Using GetPivotData to solve pivot table annoyances Next steps Chapter 16 Creating pivot tables in Excel Online How to sign in to Excel Online Creating a pivot table in Excel Online Changing pivot table options in Excel Online Where are the rest of the features? Next steps Chapter 17 Pivoting without a pivot table using dynamic arrays or Power Query Creating cross-tabs using an Advanced Filter and a Data Table Creating a cross-tab report using three dynamic array formulas Creating a cross-tab using Power Query Next steps Chapter 18 Unpivoting in Power Query Data in headings creates bad pivot tables Using Unpivot in Power Query to transform the data Unpivoting from two rows of headings Unpivoting from a delimited cell to new rows Conclusion Afterword Index Code Snippets
Donate to keep this site alive
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.