Excel Pivot Table Master Course
4.8 (48 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
354 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel Pivot Table Master Course to your Wishlist.

Add to Wishlist

Excel Pivot Table Master Course

Learn everything you need to know about Excel Pivot Tables with a highly rated instructor!
4.8 (48 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
354 students enrolled
Created by Matt Jackman
Last updated 7/2017
English
Current price: $10 Original price: $125 Discount: 92% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 5.5 hours on-demand video
  • 11 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Analyze large sets of information quickly and easily using Excel Pivot Tables
  • Use their skills to understand their business information better
  • Create more valuable insights for their company
View Curriculum
Requirements
  • Confidently write formulas
  • Copy and paste cells
  • Navigate Excel and the Excel Ribbon
Description

Pivot Tables are the most powerful tool in Excel to analyze large sets of information. If you've never used Pivot Tables before, don't worry. This course takes you from the very beginning of Pivot Tables and slowly builds on your skills and knowledge by breaking up new skills into short but comprehensive videos.

By the end of the course, you'll be confident using Pivot Tables on the data that you use every day.

Who will benefit from this course?

If you use large sets of information within Excel, then you'll definitely benefit from learning Pivot Tables. You might get this data from other departments in your company or maintain it on your own, but if you ever need to analyse that information or understand it more thoroughly, Pivot Table will be essential.

What skills do you need?

Pivot Tables are an intermediate topic in Excel so before you take this course you should be comfortable with the following tasks in Excel. 

  1. Copying and pasting information
  2. Creating formulas in Excel
  3. Navigating between sheets and using the Ribbon

Why this course?

This course stands out in a few key areas. Firstly, we actually show you how to ensure your data is in the right format to start using Pivot Tables. This is one of the most critical elements of Pivot Tables, but it's something that most courses leave out. Without the correct structure, you'll struggle to effectively analyse your data.

Additionally, this course uses the same sample data throughout the majority of the course. So instead of showing you a new skill on data that you're not familiar with, we build your skills on data that you've used throughout the course. This way you can focus on learning new skills without being bogged down with trying to understand the new data set.

The course does have practice activities that use different data sets to test your skills and expose you to some other data. In those videos, we spend time ensuring you understand the data set and pose specific questions for you to answer. We then walk through the practice answers together to ensure you get the most out of them.

Conclusion

Pivot Tables have the power to change the way you use Excel and give you skills that will set you apart from your colleagues. With Excel being a critical tool in almost every business, these skills will go a long way to boosting your career.

Who is the target audience?
  • Somebody that uses Excel on a regular basis
  • Has a desire to analyse data in Excel
  • Works with large sets of information or maintains information on their own
Students Who Viewed This Course Also Viewed
Curriculum For This Course
77 Lectures
05:37:31
+
Preparing Your Data
5 Lectures 25:25

Most people try to analyse information using formulas, but this can be quite complex and difficult to manage. In this lesson, we look at a common way that people summarise information in Excel using formulas and compare the process with using Pivot Tables.

Preview 05:10

In this lesson, we go walk through how we prepared our sales data from the previous video.

Preview 04:25

Here, we have a look at how to fix data that is already summarized so that we can analyze it using Pivot Tables.

Preview 07:23

Here we use Power Query to fix our data. It takes a little bit to get your head around it, but it can be super useful if you're managing data like this on a regular basis.

Preview 07:30
+
Creating Pivot Tables
9 Lectures 29:22

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.

Creating a Pivot Table with our Sample Data
06:15

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.

Using Multiple Fields to Analyze Your Data
02:22

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.

Formatting Values
04:12

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.

Losing Your Field List
01:39

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 Your Data
03:20

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

Summarizing with Blank Cells
03:02

Section Challenge
00:43

Section Challenge - Answer
06:54

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.

Section Quiz
3 questions
+
Helpful Pivot Table Formulas
5 Lectures 26:50
Using Binary Field Values
06:47

How to use VLOOKUP
06:47

VLOOKUP for an Approximate Match
03:49

How to use the IF Function
03:52

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

How to use a Multi-Conditional IF Function
05:35
+
Analyzing Your Data
12 Lectures 36:22
Section Introduction
00:35

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 Grand Total
02:47

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

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

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 Row Total
03:41

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

Show Values As - % of Parent Total
03:02

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

Sorting a Pivot Table
03:31

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

Expand and Collapse
02:14

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

Applying Filters
05:00

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

Subtotals and Grand Totals
07:10

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.

Multiple Values
02:42

Section Challenge
00:30

Here we look at how to answer the challenge questions.

Section Challenge - Answer
02:02

Test your understanding of this section.

Section Quiz
5 questions
+
Grouping Your Information
8 Lectures 27:48
Section Introduction
00:34

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

Grouping Dates
03:50

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
Group Dates by Day of Week
03:06

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

Grouping Numbers
02:41

Customize how your information is grouped by using manual grouping.

Grouping Manually
05:04

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

Understanding Pivot Cache
02:13

Section Challenge
01:07

Section Challenge - Answer
09:13

Test your knowledge of Pivot Table grouping.

Section Quiz
4 questions
+
Pivot Table Styles
7 Lectures 36:47
Section Introduction
00:55

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.

Design Layout Options
06:31

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

Pivot Table Styles
04:33

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.

Pivot Table Options
14:02

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

Field Settings
01:50

Section Challenge
00:57

Section Challenge - Answer
07:59

Test your knowledge of styles and layouts within Pivot Tables.

Section Quiz
4 questions
+
Calculated Fields and Items
9 Lectures 32:29
Section Introduction
00:51

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

Introduction to Calculated Fields
05:59

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

Dynamic Data Sets
05:46

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

Avoiding Duplicate Field Names
00:59

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.

Creating a Calculated Field in a Pivot Table
05:54

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.

Handling Error Values
03:06

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.

Calculated Items
06:20

Section Challenge
00:49

Section Challenge - Answer
02:45

Test your knowledge of calculated fields.

Section Quiz
5 questions
+
Slicing and Dicing Your Data
7 Lectures 24:00
Section Introduction
01:07

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

Pivot Table Slicers
04:18

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

Multiple Slicers
03:03

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

Timelines
02:40

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

Slicer and Timeline Settings
05:30

Section Challenge
00:26

Section Challenge - Answer
06:56

Test your slicer and timeline knowledge.

Section Quiz
4 questions
+
Data Visualization
7 Lectures 14:28

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.

Section Introduction
01:02

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

Conditional Formatting
05:46

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

Creating a Pivot Chart
01:40

Pivot Chart Fields
01:58

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

Pivot Chart Buttons
01:45

Section Challenge
00:22

Section Challenge - Answer
01:55

Test your knowledge of data visualization in Pivot Tables.

Section Quiz
5 questions
+
Practice Makes Perfect - Test your skills
6 Lectures 01:11:23

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.

Selling Pens and Teaching - Easy to Medium
10:07

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 - Pretty Tricky
06:06

Email Marketing Practice - Answer
15:35

Bicycles - Pretty Tricky
09:11

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

Bicycles Practice - Answer Part 1
16:00

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

Bicycles Practice - Answer Part 2
14:24
1 More Section
About the Instructor
Matt Jackman
4.7 Average rating
376 Reviews
4,056 Students
2 Courses
Business Intelligence Professional - Excel Power User

Hi, I'm Matt. 

I've been using Excel for over 15 years and learned a ton along the way. I've used Excel in some of Australia's largest companies developing Business Intelligence solutions that drive meaningful business results. Throughout my journey, the thing that has always energized me the most is seeing people's eyes light up when I show them something new for the first time.

Now, I'm the lead instructor at exceltutes.com, as project dedicated to teaching people how to use Excel. It's a project I'm very passionate about and I'm excited to share the experience with the Udemy community.

I hope I have the opportunity to show you something new for the first time and give you skills that will allow you to shine.