Pivot Tables – Table of Content

Pivot Tables from Data… to Dashboard
Table Of Contents

 


Part One The Basics – 7

Chapter One – The Basics of Pivot Tables  – 8

 

What is a Pivot Table?  – 8

What are the requirements for creating a Pivot Table? – 8

How to create a Pivot Table? – 9

Back to the Source Data – 15

Properties of Pivot Tables – 17

Show/Hide PivotTable Fields list – 17

Dealing with Headers – 18

Changing the Report Layout – 19

Manage Subtotals – 19

Manage Grand Totals – 20

Formatting Pivot tables – 23

Formatting Values – 23

Formatting by using Styles  – 24

Conditional Formatting in Pivot Tables  – 25

Basic Calculations in Pivot Tables  – 25

Changing the Summary Function  – 25

Show Details (The Drill Down feature)  – 27

Show Values as  – 30

Calculated Field  – 32

Sorting in Pivot Tables  – 34

Filtering in Pivot Tables  – 36

Filtering Fields used in the Pivot Table  – 36

Filtering Fields other than those in use in the Pivot Table  – 40

Using Slicers  – 42

What is a Slicer?  – 42

Inserting Slicers  – 42

Using Slicers  – 43

Formatting Slicers  – 45

Advanced Slicer functionality  – 47

Inserting a Timeline  – 48

Inserting a Timeline  – 48

Modifying the Timeline options  – 49

Creating Pivot Charts  – 51

Inserting a Pivot Chart  – 51

Hiding the Fields buttons  – 52

Changing the Chart Title  – 53

Dealing with the Legend  – 54

Horizontal Gridlines  – 56

Managing Data Series  – 56

Adding Data Labels  – 58

Part Two Methods To Create  – 61

Chapter Two- Methods to Create Pivot Tables  – 62

 

Using a List as Source Data  – 64

Using a Table as Source Data  – 64

Recommended Pivot Tables  – 65

Create Pivot Table with Quick Analysis Tool  – 66

Analyze Data (Formerly known as Ideas)  – 67

Creating Pivot Table from External Source  – 68

Create a Pivot Table from a Query  – 71

Consolidate Multiple Worksheets with Pivot Table  – 73

Creating A Pivot table from Another Pivot table  – 77

Create a Pivot Table from a Data Model  – 79

Creating a Pivot Table from Power BI  – 81

More ways to Create Pivot Tables  – 85

Part Three Pivot Tables Properties  – 86

Chapter Three- Advanced Layout and Options  – 87

Show Items with No data  – 87

Value for Empty Cells  – 89

Automatically Refresh when Opening  – 91

Prevent Changing Column Width on Update  – 92

Changing Pivot Table Layout  – 94

Repeat All Item Labels  – 97

Collapse/Expand buttons  – 98

Insert Blank Row  – 100

Chapter Four – Reporting with Pivot tables  – 101

Show Details- Drill Down Feature  – 101

Show Report Filter Pages  – 102

Practical Project with “Show Report Filter Pages”  – 103

Part Four Analysis with Pivot Tables  – 108

Chapter Five- Advanced Sorting and Filtering  – 109

 

Sorting Based on Values in Another Field  – 109

Create a Custom Sort  – 111

Label and Value Filters simultaneously  – 114

Top N Values Filter  – 117

AutoFilter by Cell Below (Access Style)  – 119

Slicer Basics – 121

Slicer Multi-select  – 121

Modifying Slicer Settings  – 122

Custom Slicer Style  – 125

Report Connections  – 128

Slicer drop List  – 130

Searchable Slicer  – 133

Chapter Six – Advanced Calculations in Pivot Tables 134

Show Values as…  – 134

Percentage of Column Total  – 135

Running Total  – 137

Rank  – 138

Multiple Subtotals  – 140

Cube Function: Why and How?  – 141

Quick Explore  – 144

Frequency and a cash problem  – 148

Creating a Calculated Field  – 150

GetPivotData  – 152

What is it?  – 152

Turn it On and OFF  – 153

Chapter Seven- Grouping In Pivot Tables  – 155

Grouping Dates  – 155

Date Problem  – 157

Cache Problem  – 159

Grouping Dates by Week  – 162

Grouping Numbers  – 162

Grouping Text  – 163

Calendar Fiscal Years  – 166

Fiscal Year Starts in October  – 169

Fiscal Year Starts in November  – 171

Chapter Eight – Conditional Formatting in Pivot Tables. – 175

Using Color Scales  – 176

Using the Top/Bottom Rule  – 183

Using Data bars and Icon sets on the same range  – 185

Chapter Nine – Pivot Tables and Other Applications  – 195

Share as a Static Report  – 195

Interactive Pivot Table in Microsoft Word  – 197

Interactive Pivot Table in PowerPoint   – 201

Interactive Pivot Table in Slide Show Mode  – 203

Part Five Automation – 208

Chapter Ten – Pivot Tables with Macros and VBA  – 209

Creating the Pivot Table Cache  – 210

Managing Pivot Table Fields  – 211

Running the CreatePivot Subroutine  – 212

Changing the Summary Function  – 213

Update the Pivot Table Automatically  – 214

A Macro To Restructure A Pivot Table  – 215

Create a More Dynamic Pivot Table  – 216

Show/Hide Grand Totals  – 217

Hide a Field  – 217

Chapter Eleven – Dynamic Arrays vs Pivot Tables  – 218

History of Dynamic Array Functions  – 218

Three DA functions for Pivot Table-Like Functionality  – 219

Can a Single Dynamic Array Function Replace a Pivot Table?  – 222

Create the First LAMBDA function  – 223

Create the Second LAMBDA function  – 223

Create the Third LAMBDA function  – 224

Creating the Main LAMBDA Function  – 225

Part Six Tips & Tricks  – 229

Chapter Twelve- Pivot Tables Tips and Tricks  – 230

Quick Access to Value Field Settings  – 230

Custom Grouping of Dates by week  – 230

Reverse the Show Details report  – 231

Floating cell Formatting  – 231

Year over Year change  – 232

Smart Custom Formatting for YOY difference  – 233

Compare Lists using Pivot Tables  – 234

Flattened Pivot table  – 237

Part Seven Creating Dashboards  – 242

Chapter Thirteen – Dashboard with a Click  – 243

Introduction  – 243

Are you in a rush for a meeting? Just one Click.  – 244

Chapter Fourteen – From Data to Dashboard  – 250

Different Applications can be Used  – 251

The Structure of a Dashboard  – 252

Preparing the Data for Analysis – 253

Sending the Data to Power Pivot.  – 254

Creating Measures  – 256

Creating the Pivot Tables in the Preparation Sheet  – 258

Creating Charts  – 261

Creating Progress Doughnut Charts  – 262

Moving the different Charts to The Dashboard  – 272

Revenue by Product Bar Chart  – 272

Revenue by Manager Bar Chart  – 273

Revenue by Year Line Chart  – 276

Revenue by Province Funnel Chart  – 279

Creating Cards  – 281

Creating a Slicer  – 283

Linking the Category Slicer to All Pivot tables and Charts.  – 283

Personalizing the Dashboard  – 284

Chapter Fifteen: Pivot Tables Graphic Shortcuts  – 286

A Dull List of Pivot Tables Shortcuts  – 287

Graphical Pivot Tables Shortcuts  – 288

Create Pivot Table  – 288

Selecting/ Deselecting Pivot Table  – 291

The Pivot Table Field List  – 292

Refreshing Pivot Tables  – 294

Pivot Table Items  – 295

Hide Pivot Table Items  – 295

Open Field Header Menu – 296

Open Calculated Field Dialog Box  – 296

Open Calculated Item Dialog Box  – 297

Creating Pivot Charts  – 298

Test Your Knowledge  – 299

Chapter Sixteen: Pivot Tables in Excel Online  – 300

Creating Pivot Tables in Excel Online  – 301

We Do Have Some Differences  – 302

Last Words  – 306

Other Books By The Author  – 307

Download The Exercise Files  – 308

 

Share This Post
Have your say!
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.

Thanks for Downloading Your Gift

We have sent your quick reference guide for creating Dashboard.

Please check your inbox to receive Your Gift.

Happy Learning !!

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.