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.
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.
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.
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.
In this lesson, we go walk through how we prepared our sales data from the previous video.
Here, we have a look at how to fix data that is already summarized so that we can analyze it using Pivot Tables.
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.
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.
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.
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.
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.
Sometimes you get blank cells within your data sets. In this lesson we see how those can impact our results.
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.
Sometimes you'll want to look at more than one scenario. Using multiple scenarios in an IF function is easy.
In this lesson we learn how to use a Pivot Table to manipulate the way our information is shown within the Pivot Table.
We continue to explore different ways to manipulate our value fields.
Whoa, there are a lot of ways to show values in a Pivot Table. We continue to look at some useful methods.
Our final lesson in looking at different ways to display how our values fields are represented.
There are a number of ways to sort a pivot table. We explore some useful methods.
Learning how to expand and collapse pivot tables can save you time and energy.
Applying filters to your table allows you to easily access the information you need.
We explore how to manage subtotals and grand totals within our Pivot Tables.
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.
Here we look at how to answer the challenge questions.
Test your understanding of this section.
Grouping dates in Excel allows you to easily analyse time relevant information.
In this lesson we explore how to group our date relevant information by weekday, which is not a built in feature.
Learn how to group numbers in pivot tables to summarize continuous value fields from your data.
Customize how your information is grouped by using manual grouping.
Understanding pivot cache will help you get around some common problems associated with grouping data in your Pivot Tables.
Test your knowledge of Pivot Table grouping.
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.
Let's make our Pivot Tables look amazing! Well, I'm sure you can make them prettier than mine at least.
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.
We explore some settings that we can apply to our fields.
Test your knowledge of styles and layouts within Pivot Tables.
Here we have an initial look at how we can answer questions that aren't already available in our source data.
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 is easy, but not obvious at first.
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.
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.
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.
Test your knowledge of calculated fields.
Slicers allow you to easily analyze your information in a visual manner.
You can easily control multiple Pivot Tables with a single slicer. Let's learn how.
You can also use timelines to analyze your data visually. The work great for date fields.
There are some slicer and timeline settings that it's good to be aware of. Here we go through the key ones.
Test your slicer and timeline knowledge.
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 is an awesome way to quickly convey information in Pivot Tables. Let's have a look.
We can create a Pivot Chart that displays the information from our Pivot Table through a chart or graph.
We get some additional options with our Pivot Charts compared to normal charts.
Test your knowledge of data visualization in Pivot Tables.
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.
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.
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.
This is part 1 of our 2 part Bike Shop answer video. It's pretty intense, so hang in there :)
We continue our in-depth practice answer in this video. If you are able to answer these questions, you've mastered Pivot Tables!
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.