Excel Pivot Table Master

Introduction to Creating Pivot Tables Preview 00:55

Creating a Pivot Table with our Sample Data Preview 06:15

In this lesson, we start getting familiar with the data that we'll be using in the course. We create a simple Pivot Table to put some context around what we'll be learning.

Download the workbook for this course to follow along and build your Pivot Tables.

Using Multiple Fields to Analyze Your Data Preview 02:22

You can use any field that you have in your data set to analyze your data. Pivot Tables even allow you to place more than one field in any of the areas. This gives you tremendous flexibility when it comes to answering questions in different ways.

Formatting Values Preview 04:12

When you place values into your Pivot Table, they won't always be formatted how you want. In this lesson, we explain how to format values in a Pivot Table. It's a little bit different than what you might be used to.

Losing Your Field List Preview 01:39

Whoops, where's my stuff? A question I've asked myself many times. It's easy to close your field list and it can be a bit tricky to get it back. Once you know how it works, it's easy.

Summarizing Your Data Preview 03:20

Ok, now for the power! In just a few simple clicks, you can totally change the way your data is summarized. You can use the same piece of data to answer different questions using Pivot Tables. 

Summarizing with Blank Cells Preview 03:02

Sometimes you get blank cells within your data sets. In this lesson we see how those can impact our results.

Section Challenge Preview 00:43

Section Challenge - Answer Preview 06:54

Section Quiz

Test your knowledge in this quiz.

Once you finish this quiz, you're ready for the first practice video. You can head down to it at any time. It's the one about Pens and Teaching.

If you want to keep chugging along, we keep diving deeper into Pivot Tables in the next section.

Using Binary Field Values Preview 06:47

How to use VLOOKUP Preview 06:47

VLOOKUP for an Approximate Match Preview 03:49

Concatenate Preview 04:38

How to use the IF Function Preview 03:52

How to use a Multi-Conditional IF Function Preview 05:35

Sometimes you'll want to look at more than one scenario. Using multiple scenarios in an IF function is easy.

Section Introduction Preview 00:35

Show Values As - % of Grand Total Preview 02:47

In this lesson we learn how to use a Pivot Table to manipulate the way our information is shown within the Pivot Table.

Show Values As - % of Row and Column Totals Preview 03:08

We continue to explore different ways to manipulate our value fields.

Show Values As - % of Parent Row Total Preview 03:41

Whoa, there are a lot of ways to show values in a Pivot Table. We continue to look at some useful methods.

Show Values As - % of Parent Total Preview 03:02

Our final lesson in looking at different ways to display how our values fields are represented.

Sorting a Pivot Table Preview 03:31

There are a number of ways to sort a pivot table. We explore some useful methods.

Expand and Collapse Preview 02:14

Learning how to expand and collapse pivot tables can save you time and energy.

Applying Filters Preview 05:00

Applying filters to your table allows you to easily access the information you need.

Subtotals and Grand Totals Preview 07:10

We explore how to manage subtotals and grand totals within our Pivot Tables.

Multiple Values Preview 02:42

Sometimes we may want to add a value twice. In this lesson we explore how to do it and the additional options that are made available.

Section Challenge Preview 00:30

Section Challenge - Answer Preview 02:02

Here we look at how to answer the challenge questions.

Section Quiz

Test your understanding of this section.

Section Introduction Preview 00:34

Grouping Dates Preview 03:50

Grouping dates in Excel allows you to easily analyse time relevant information.

Group Dates by Day of Week Preview 03:06

In this lesson we explore how to group our date relevant information by weekday, which is not a built in feature.

Formulas used

1. Weekday - returns a value 1 - 7 representing the day of the week for a particular date
   • = weekday ([date])
2. Text - formats a value as text in a specific format
   • = text ([value], [format])
3. The compiled formulas looks like this: = text(weekday ([date]), "dddd")
   • "dddd" represents the formatting for a full day of week string. e.g. Tuesday

Grouping Numbers Preview 02:41

Learn how to group numbers in pivot tables to summarize continuous value fields from your data.

Grouping Manually Preview 05:04

Customize how your information is grouped by using manual grouping.

Understanding Pivot Cache Preview 02:13

Understanding pivot cache will help you get around some common problems associated with grouping data in your Pivot Tables.

Section Challenge Preview 01:07

Section Challenge - Answer Preview 09:13

Section Quiz

Test your knowledge of Pivot Table grouping.

Section Introduction Preview 00:55

Design Layout Options Preview 06:31

Here we have a look at our design options for our layouts. There are some fairly tricky concepts in this video, but we cover it in a bit more detail later on, so don't worry if it doesn't all make sense just yet.

Hot tip - there may be a future challenge lurking in this video.

Pivot Table Styles Preview 04:33

Let's make our Pivot Tables look amazing! Well, I'm sure you can make them prettier than mine at least.

Pivot Table Options Preview 14:02

Sometimes we need to turn off certain settings on or Pivot Tables, or turn some back on. In this lesson we look at some common settings we might want to change.

Field Settings Preview 01:50

We explore some settings that we can apply to our fields.

Section Challenge Preview 00:57

Section Challenge - Answer Preview 07:59

Section Quiz

Test your knowledge of styles and layouts within Pivot Tables.

Section Introduction Preview 00:51

Introduction to Calculated Fields Preview 05:59

Here we have an initial look at how we can answer questions that aren't already available in our source data.

Dynamic Data Sets Preview 05:46

We can make our data range a bit more dynamic by defining our data set as a table. Here we see how.

Avoiding Duplicate Field Names Preview 00:59

Avoiding duplicate field names is easy, but not obvious at first.

Creating a Calculated Field in a Pivot Table Preview 05:54

Now that we understand how to extend our source data to answer questions, we leverage the functionality available in our Pivot Table to create a calculated field.

Handling Error Values Preview 03:06

Once in a while we'll encounter an error value within our Pivot Table. Sometimes this is okay and Pivot Tables allow us to hide them easily.

Calculated Items Preview 06:20

In this lesson we cover calculated items. They can be a bit tricky to get you're head around at first, but sometimes they're useful depending on your situation.

Section Challenge Preview 00:49

Section Challenge - Answer Preview 02:45

Section Quiz

Test your knowledge of calculated fields.

Section Introduction Preview 01:07

Pivot Table Slicers Preview 04:18

Slicers allow you to easily analyze your information in a visual manner.

Multiple Slicers Preview 03:03

You can easily control multiple Pivot Tables with a single slicer. Let's learn how.

Timelines Preview 02:40

You can also use timelines to analyze your data visually. The work great for date fields.

Slicer and Timeline Settings Preview 05:30

There are some slicer and timeline settings that it's good to be aware of. Here we go through the key ones.

Section Challenge Preview 00:26

Section Challenge - Answer Preview 06:56

Section Quiz

Test your slicer and timeline knowledge.

Section Introduction Preview 01:02

In this section, we go into conditional formatting and Pivot Charts. If you're not familiar with conditional formatting in Excel or you're not comfortable with normal charts, it might be a good idea to get familiar with those concepts outside of Pivot Tables first.

Conditional Formatting Preview 05:46

Conditional formatting is an awesome way to quickly convey information in Pivot Tables. Let's have a look.

Creating a Pivot Chart Preview 01:40

We can create a Pivot Chart that displays the information from our Pivot Table through a chart or graph.

Pivot Chart Fields Preview 01:58

Pivot Chart Buttons Preview 01:45

We get some additional options with our Pivot Charts compared to normal charts.

Section Challenge Preview 00:22

Section Challenge - Answer Preview 01:55

Section Quiz

Test your knowledge of data visualization in Pivot Tables.

Selling Pens and Teaching - Easy to Medium Preview 10:07

This is a great practice video to start with. We start with some basic Pivot Table skills and introduce some cool techniques that you can use to analyze data effectively.

Email Marketing - Pretty Tricky Preview 06:06

In this practice content, we look at some email marketing sample data. This will be a very real world example and will require you to use some other essential Excel skills. 

1. VLOOKUP - bringing back a value from a range in Excel based on a value in your source data is really helpful. If you don't know how to use VLOOKUP, it's super powerful and you can learn it at https://youtu.be/N1GR-uDcYKA
2. IF - using logic to enrich your source data is extremely valuable. I use the IF function all the time to do this with my data. If you don't know how to use the IF function, you can learn it at https://youtu.be/3fhMIXeAR0c
3. Multi-conditional IF - Sometimes you need to consider more than two outcomes when using conditional logic. You can use a multi-conditional IF statement to do that. If you don't know how or want a refresher, you can learn it at https://youtu.be/o4DIzBMjXuA

Wow, this is like a whole mini course inside here. I know it can be a bit overwhelming, but combining your new Pivot Table skills with these essential functions will supercharge your analysis skills.

Email Marketing Practice - Answer Preview 15:35

Bicycles - Pretty Tricky Preview 09:11

Bicycles Practice - Answer Part 1 Preview 16:00

This is part 1 of our 2 part Bike Shop answer video. It's pretty intense, so hang in there :)

Bicycles Practice - Answer Part 2 Preview 14:24

We continue our in-depth practice answer in this video. If you are able to answer these questions, you've mastered Pivot Tables!

Using External Data Preview 02:13

Sometimes it makes sense to use external data on your Pivot Table. Here we explore where you can do that. Every work situation is different and your ability to access different sources will depend on your infrastructure, so I just highlight a simple example here.

GETPIVOTDATA Preview 10:24

GETPIVOTDATA is a really cool feature within Pivot Tables. It isn't well understood among many users, so we go into a detailed lesson on this so that you can see how it works and a few ways that you might try to apply it.