Microsoft Excel Pivot Tables: Step by Step Beginners Guide

✍ Scribed by Sheikh, Ahmed M.Sc.

• Year: 2019
• Tongue: English
• Leaves: 62
• Category: Library

✦ Synopsis

Want to learn quick and easy about Excel PivotTable?
Want to learn how to put an interactive Dashboard together?
Want to learn where to find a database that you can use at any point in time?
Did we hook you yet?

In this book learn the basics of what a PivotTable is and how to create an interactive Dashboard. Go from ho-hum to off the charts. You can even learn the different types of charts that Excel offers and when to utilize them, how to create them and adjust them.

Have a side project and need fictional data, no problem. We can teach you a quick and easy way to create your own data chart that takes minutes to set up and you can use over and over as much as you like, none of it will exist except for in your computer.

Plus, as an added bonus, learn where to get data from across the internet and where to look locally.

Added into this are pictures that assist you along your way, created just for this book and not found in any other text.

✦ Table of Contents

Contents
Unit 1 – PivotTable Fundamentals......................................................................................................................1
What is a PivotTable? ........................................................................................................................................................1
When and why use a PivotTable? .....................................................................................................................................1
Anatomy of a PivotTable ...................................................................................................................................................2
Limitations of PivotTables.................................................................................................................................................4
Unit 2 – Creating Basic PivotTables ...................................................................................................................5
Preparing your data ...........................................................................................................................................................5
Tabular Layout................................................................................................................................................................5
Removing Section Headings............................................................................................................................................5
Removing Repeated Column Groups...............................................................................................................................6
Eliminating Gaps ............................................................................................................................................................6
Type Formatting..............................................................................................................................................................6
Creating PivotTables..........................................................................................................................................................7
Adding Fields..................................................................................................................................................................7
Adding Layers.................................................................................................................................................................9
Altering Structure............................................................................................................................................................9
Report Filters................................................................................................................................................................12
Generating Multiple Reports from One PivotTable: .....................................................................................................12
Add Multiple Report Filters ..........................................................................................................................................13
Show Report Filters in rows or columns.......................................................................................................................13
Managing Changes in Your Source Data .......................................................................................................................14
Dynamic Named Ranges ...............................................................................................................................................14
Dealing with Blank Cells...............................................................................................................................................14
Subtotals............................................................................................................................................................................16
Suppress Subtotals when you have many row fields......................................................................................................16
Adding Multiple Subtotals for one field.........................................................................................................................16
Using Running Total Options........................................................................................................................................17
PivotTable Tools ...........................................................................................................................................................19
Unit 3 – PivotTable Views.................................................................................................................................. 22
Conditional formatting.....................................................................................................................................................22
Sorting, Filtering and Re-ordering..................................................................................................................................23
Slicers............................................................................................................................................................................25
Saving Custom Views.......................................................................................................................................................33
Unit 4 – PivotTable Calculations....................................................................................................................... 34
Calculated Fields and Items.............................................................................................................................................34
Managing & maintaining PivotTable calculations.........................................................................................................37
Editing and Deleting Your PivotTable Calculations.....................................................................................................37
Changing the solve order of your calculated items.......................................................................................................37
Documenting Your Formulas........................................................................................................................................37
Unit 5 – PivotCharts........................................................................................................................................... 38
What is a PivotChart?......................................................................................................................................................38
Creating a PivotChart......................................................................................................................................................38
PivotChart Rules ..............................................................................................................................................................41
Changes in the underlying PivotTable affect your PivotChart......................................................................................41
Some formatting limitations still exist in Excel 2007.....................................................................................................41
PivotChart Alternatives...................................................................................................................................................42
Unit 6 – Working with Data Sources ................................................................................................................ 43
Consolidating multiple sheets or ranges of data.............................................................................................................43
Using external data sources (Excel) ................................................................................................................................46
Using external data sources (Access)...............................................................................................................................49
Unit 7 – Automating PivotTables with Macros................................................................................................ 52
Introducing Macros..........................................................................................................................................................52
Recording PivotTable macros .........................................................................................................................................52
The Power Pivot add-in....................................................................................................................................................55
Unit 8 – Summary............................................................................................................................................... 56
Frequent PivotTable Questions and Answers................................................................................................................56
I keep getting the error “The PivotTable field name is not valid” ................................................................................56
When I refreshed my PivotTable, my data disappeared ................................................................................................56
My PivotTable always uses Count instead of Sum ........................................................................................................56
My PivotTable constantly adjusts the columns in my workbook to autofit the headings...............................................56
The Defer Layout option locked me out of other functionality such as sorting, filtering, group. ..................................56
Older versions of Excel do not open my PivotTable properly .......................................................................................56
When I try to group a field I get an error message........................................................................................................56
My PivotTable shows the same data item twice ............................................................................................................56
Deleted data items still show up in the filter area .........................................................................................................56
I refreshed my PivotTable and now my calculated fields are displayed as error values...............................................57
How do I make my PivotTable refresh automatically?..................................................................................................57
How do I refresh all PivotTables in a workbook at the same time? ..............................................................................57
How can I sort data items in a unique order that is not ascending or descending? ......................................................57
How do I turn my PivotTable into hard data?...............................................................................................................57
Is there an easy way to fill the empty cells left by row fields.........................................................................................57
Is there an easy way to fill the empty cells left by row fields in many columns.............................................................57
Why does my PivotChart exclude months for certain data items?.................................................................................57
How do I add a rank number (i.e. 1-10 Sales figures) to my PivotTable?.....................................................................57
How do I hide calculation errors in my PivotTable? ....................................................................................................58
How can I reduce the size of my PivotTable reports? ...................................................................................................58
How can I easily create a separate PivotTable for each market? .................................................................................58
How do I avoid the need to constantly redefine my PivotTable’s data range?..............................................................58
How Do I Move the PivotTable?...................................................................................................................................58

✦ Subjects

Microsoft Excel Pivot Tables, Beginners Guide Step by Step