Table of Contents: PivotTable Mastery

Introduction

Course introduction

Get started here - we'll go over what you'll learn in this course and how to navigate through sections and lessons.

Prerequisites

Overview of prerequisites

In this lesson, we'll briefly review the prerequisites for completing this course. Don't worry - they're minimal!

Rows, columns, and cells

Learning to manipulate rows, columns, and cells will set a solid foundation for the rest of our work in Excel.

Number formats

In Excel, numerical values can be formatted one of many ways - with or without decimals; with preceding currency symbols; as dates, and more. Learning to properly format numbers is a critical step before we dive into PivotTables.

What is a PivotTable?

What is a PivotTable?

In this module, we'll define 'PivotTable' and look at a sample PivotTable while exploring some of its high-level capabilities.

Prepping the data

Preparing the data

Before creating a PivotTable, we'll need to carefully prepare a set of source data to ensure that it's clean and properly formatted. Without clean source data, it's likely that our PivotTable will contain errors!

Data prep exercise

Now that you've learned how to prepare PivotTable data, it's time to try things out for yourself. Can you convert a large spreadsheet of raw data into properly-formatted PivotTable input?

Creating a simple PivotTable

Simple PivotTables

Now that our data is properly formatted, we can move on to the good stuff: creating a simple PivotTable. In this lesson, we'll explore PivotTable basics and show a simple example of a Pivot in action.

Basic PivotTable exercise

Now that you've learned how to prepare PivotTable data, it's time to try things out for yourself. Can you convert a large spreadsheet of raw data into properly-formatted PivotTable input?

Advanced PivotTables

Pivots in two dimensions

One-dimensional PivotTables like the one we built in the last section are just the beginning! In this module, we'll build an even more advanced PivotTable by displaying data across two dimensions.

2D PivotTables exercise

Now that you've learned about two dimensional PivotTables, it's time to prepare one of your own.

Filtering PivotTables

One of PivotTables' most powerful features is the ability to filter by any row or column on-demand. In this lesson, we'll learn how to apply filters to our PivotTables to refine our data.

Different summaries

Until now, we've been using the SUM function to aggregate data without our PivotTables and cut it across multiple dimensions. But Pivots can also aggregate data using other functions - like AVERAGE and COUNT - for even more complex summaries.

Grouping headings

Sometimes, showing a PivotTable row or column for every data point in our set doesn't make sense. For example, when we're working with dates, it makes sense to show data grouped by year or month rather than individual day. Pivots allow us to do just this!

Conclusion

Conclusion

Over the course of the past few hours, we've learned how to prepare data, create a PivotTable, and format with multiple different styles. Let's review!