Excel PivotTables and Dashboard
The step-by-step guide to learn and master Excel PivotTables and dashboard
Copyright 2019 HEIN SMITH - All rights reserved.
The content contained within this book may not be reproduced, duplicated or transmitted without direct written permission from the author or the publisher.
Under no circumstances will any blame or legal responsibility be held against the publisher, or author, for any damages, reparation, or monetary loss due to the information contained within this book, either directly or indirectly.
Legal Notice:
This book is copyright protected. It is only for personal use. You cannot amend, distribute, sell, use, quote or paraphrase any part, or the content within this book, without the consent of the author or publisher.
Disclaimer Notice:
Please note the information contained within this document is for educational and entertainment purposes only. All effort has been executed to present accurate, up to date, reliable, complete information. No warranties of any kind are declared or implied. Readers acknowledge that the author is not engaging in the rendering of legal, financial, medical or professional advice. The content within this book has been derived from various sources. Please consult a licensed professional before attempting any techniques outlined in this book.
By reading this document, the reader agrees that under no circumstances is the author responsible for any losses, direct or indirect, that are incurred as a result of the use of information contained within this document, including, but not limited to, errors, omissions, or inaccuracies.
Table of Contents
Introduction
Welcome to the Excel PivotTables and Dashboard mastery guide! This e-book is intended for freelancers who have been asked to implement analyses and consultants who help inform decision-makers. This book will empower you, as the data person, to separate yourself from others in your field by incorporating additional value to your services through making adaptive reports. This book is also both for the Excel aficionado and the intermediate Excel user who wants to get more functionality out of their Microsoft Excel product. Regardless, the goal is to allow you to stay ahead of the other Excel power users with a sleek resource and reference for your skills.
That being said, foremost, this book is a resource for mastering easy, powerful techniques to create, customize, change, and control PivotTables in Excel. As you encounter analysis problems, you may return to this book to help strategize your approach. This book also features some common troubleshooting issues to get you started on solving technical issues.
To begin, lets discuss a quick definition of PivotTables. PivotTables are a collection of built-in tools that Excel uses to compile more comprehensible reports from spreadsheet data, including data from various Excel files. PivotTable tools filter, sort, rearrange, and calculate data to summarize databases.
These PivotTable features allow you to extract specific information into reports, which can facilitate better explorations of trends in data and predictive features. How you extract and extrapolate this data, of course, depends on the purposes of the report. As with any tool PivotTables ultimately only allows you to visualize data in different ways. How these visualizations are interpreted and the conclusions reached depend on the analytical know-how of the analyst.
Expanding the functionality of Excel depends solely on the version of Excel of your client or employer. This includes PivotTables and the functions used within them. Excel 2019 has added more PivotTable features that may not be backwards compatible to an older perpetual license. This is important for freelancers and consultants because these features may not be viewable on the clients native desktop versions of Excel. This lack of backwards compatibility also makes it difficult to recreate and execute newer features on older versions of Excel. Ergo, it is important when conducting your data dissections and compilations on Excel 2019 or Office 365. Reports are always viewable on Microsofts office live online Excel client.
To begin, let us start with an overview of the purpose and the best uses of this text. The next section will give an exhaustive overview of text features and how to take the best advantage of them.
Overview: Best Use of and Practice
This book is a collection of supplementary information to review before and after the illustrated demonstrations. In addition to being useful for individuals who favor additional instruction and illustration before application, this book is a superior substitute for most online tutorials. Most Excel aficionados became this way through searching for features online. This is a novel and resourceful way to learn; even this book includes the best sources for this purpose as well. However, these online tutorials are scattered and not unified. This book unifies examples and demonstrations.
Note:
Important and noteworthy information will be separated from other texts accordingly. They will be separated from the main text and can occur at any point in the book.
One useful site used to source our demonstration and references is the Microsoft Office Support site. Each demonstration will point to its associated Microsoft Excel exercises for demonstration and practice (Create a PivotTable to analyze worksheet data, n.d.). This site is useful because it features samples and screenshots from several platforms. These platform versions include Windows (PC), Mac (iOS), and the Microsoft Live web-based platform.
One of the limitations of the Microsoft Office Support site, however, is that the demonstrations do not feature more realistic datasets. The majority of these datasets are simple examples, featuring incredibly small dimensions, i.e. 3x4 datasets with nonsense data within. While these examples serve as a way to clearly demonstrate the functions, they do not demonstrate how the function is actually used to solve problems.
Demonstrations with a Large Dataset
This book will also feature demonstrations using this large, 6370x17 dataset for practice:
http://www3.wabash.edu/econometrics/EconometricsBook/Chapters/Ch03PivotTables/ExcelFiles/EastNorthCentralFTWorkers.xls
This public workbook file was originally crafted for an introductory econometrics course. Specifically, the dataset was created for their chapter covering pivottables (Introductory Econometrics Chapter 3: Pivot Tables, n.d.). Econometrics itself involves large demographical datasets. PivotTables require continuous data, i.e. data with no empty rows or columns interspersed in between (Create a PivotTable to analyze worksheet data, n.d.). Therefore, to create this dataset, the census data had to be cleaned and formatted properly for demonstrations. This serves our purpose because it makes a comprehensible yet large enough dataset to demonstrate the power of pivot tables.
For the purpose of our demonstrations, we will be focusing on these specific workbook dataset features:
Raw Data sheet for performing exercises
Named Ranges for large dataset management
Summary of stats generated by Excels Data Analysis add-in
6366 individuals with 17 different measured variables
Q&A sheet with simple contextual exercises
This large dataset will be used to demonstrate features on a more complex table, which is closer to what you might be using these features for in the wild. As an effect, you will also be guided through real world troubleshooting and limitations for these functions.
