Pivot Tables for Excel 2016

Course Overview
Hi everyone. My name is Mike Thomas and welcome to my course Pivot Tables for Excel 2016. I'm an IT trainer with over 27 years' experience. Pivot tables are one of the most powerful tools in Excel's data analysis armory. With just a few clicks of the mouse and no complicated formulas, you can quickly and easily summarize and analyze large amounts of raw data. In this course, you'll learn how to create and manipulate pivot tables and get answers to those important questions on which you base your key business decisions. Some of the major topics that we'll cover include best practice guidelines for setting up your data sources, formatting a pivot table to make it understandable and inviting to read, producing powerful reports, including sorting, filtering, and grouping your data, presenting your data as a chart and an infographic, and how to create a pivot table driven dashboard. By the end of the course, you'll have a solid foundation that you can use to build your own pivot tables and reports. And as long as you can create basic worksheets, understand simple formula, and copy and paste, and apply basic formatting to cells, you'll be able to follow along. So I hope you'll join me on this journey to learn pivot tables with the Pivot Tables for Excel 2016 course at Pluralsight.

Introduction to Pivot Tables
In this module, you'll learn via some practical examples what pivot tables are and how they can be used. You'll learn how to create some basic pivot tables, and you'll learn about data sources, specifically how and why you should base your pivot tables on a table, how to create a pivot table based on an external source, in this case, we'll use an SQL Server database, and how to create a pivot table that's based on multiple tables.

Customizations and Calculations
Now that you've learned how to create a basic pivot table, in this module we'll look at how to customize it. We'll cover changing the values element to display an average and a count instead of a sum. You'll learn how to change the appearance of a pivot table, changing things like colors and number formats. And I'll also give you a tip about using conditional formatting with a pivot table. You'll learn how to change the layout of a pivot table. We'll look at how to display numeric data in different ways, including percentages, running totals, and as a difference. What I mean by that is the difference between January and February, February and March, et cetera, all without creating any formulas. And finally in this module, you'll learn how to create a new column in a pivot table based on a calculation.

Sorting, Grouping, and Filtering
In this module, you'll learn about sorting, filtering, and grouping in a pivot table. By default, pivot tables are sorted based on the values in the row section or the column section. But what if you wanted to sort your report into, say, revenue order? Filtering lets you display only certain items in a pivot table, for example, only those stores where the revenue is over $5000, or the top three bestselling products. There's a few ways to apply filters, including a cool feature called slicers, which is available in Excel 2013 and in Excel 2016 for Windows and Mac, and we'll have a look at that. Grouping allows you to summarize data based on grouped values, so, for example, if you have a list of individual dates, you can group by month or year. Let's start by having a look at sorting.

Pivot Charts and Data Visualizations
In this module, you'll learn how to represent the data in a pivot table as a chart. We'll look at three different types of charts. Pivot charts, which are like regular Excel charts, except they're based on data in a pivot table, sparkline charts, which are small charts that are embedded into a cell and used to display trends, and the people graph, which is available in Excel 2013 and Excel 2016 for Windows, and allows you to create a kind of infographic style chart.

Creating a Basic Pivot Table Driven Dashboard
To finish the course, I'm going to pull together some of the things that I've already covered and provide you with a practical use for pivot tables by creating a dashboard. This is the dashboard that we'll be creating. It has a summary of the key performance indicators, a chart showing revenue per month, a sparkline chart showing you the revenue per month per product, and a chart showing the average customer satisfaction score, comparing it to a target score, and this information is obtained via a customer satisfaction survey that every customer is asked to complete. There's also a slicer to allow me or whoever is using this to filter and look at the data for individual states. Sitting behind the dashboard are a number of pivot tables, so in this example, the pivot tables are a means to an end rather than the end itself. Although, there's nothing to stop anyone who's using the dashboard actually accessing the pivot tables and looking at the summaries that they contain. So let's move on and look at how to create the dashboard.