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.