Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Subscribe to Excel Expert 7
Get notifications when this page is updated

Intro

In lesson 7 of my Microsoft Excel expert series, you will learn all about pivot tables in Excel 2010. I will show you what pivot tables are, why they are useful, and how to build and edit them from scratch. We will cover working with pivot table options, explore new features like slicers, make design and layout changes, create pivot charts, and group data in your tables. This lesson is designed for experienced Excel users who are ready to take their skills to the next level.

Transcript

Welcome to Excel 2010 Expert Level 7, brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost.

Today's class is all about pivot tables. You'll learn what a pivot table is and why they're useful. You'll learn how to build a pivot table from scratch and how to edit a pivot table once you've built one. You'll learn about many of the different options available for pivot tables. You'll learn how to make design and layout changes to your pivot tables. You'll learn how to build pivot charts out of your pivot table data. And you'll learn how to create custom grouping levels for your data.

This course was developed for Excel 2010. Most of what's covered is also valid in Excel 2007. There are a couple of features that were added in 2010, like slicers, that you won't have available in previous versions. If you're using Excel 2003 or earlier, the interface is much different. Go to my website at ExcelLearningZone.com and look for Excel 223 and 224 under the Excel 2003 category. Those two lessons cover pivot tables for Excel 2003 and earlier.

This is an expert level course for Microsoft Excel. I strongly recommend that you take all of my beginner courses 1 through 5 before taking this course, and preferably my other expert classes 1 through 6 before starting this one. Many of the topics covered in those other classes will be necessary for today's class.

My courses are broken up into four different groups: beginner, expert, advanced, and developer. The beginner courses are for novice users with little or no experience with Excel. The expert series, which is what you're watching right now, is designed for more experienced users who are already comfortable with Excel. Expert classes go into a lot more depth about each topic than the beginner classes did, and we'll cover more functions, features, tips, and so on.

When you've mastered the expert classes, move up to the advanced lessons. You'll learn how to build macros, build user forms, create your own templates, and many more advanced features that not everyone will use, but they really add enhanced functionality and professionalism to your spreadsheets. Finally, my developer series is designed to teach you how to program in Visual Basic for Applications with Microsoft Excel. This will allow you to create Excel-based programs for your users, automate your spreadsheets, and integrate Excel tightly with the other Office applications.

Each of my series is broken down into different levels. For example, the beginner series contains five different levels, which you should have taken previously. This class is the seventh level of the expert series. Each level teaches you new and different topics in Microsoft Excel, building on lessons learned in the previous levels. When you finish all of the expert classes, you'll move up to the advanced series, and finally the developer series.

Now let's take a more detailed look at exactly what we're going to cover in today's class.

In lesson one, we're going to learn what pivot tables are, why they're so useful, and what you can do with them. In lesson two, we'll create our very first pivot table. We'll create a table showing a list of sales, broken down by each city and by each year, with totals for each.

Now that we know how to build a pivot table, in lesson three, we'll learn how to edit that pivot table. We'll see how to change fields, collapse and expand levels, filter and sort the data, and more.

In lesson four, we'll take a look at some of the pivot table options, and we'll see a feature called slicers that is new in Excel 2010. In lesson five, we're going to look at some of the pivot table design options. In lesson six, we'll learn how to create a pivot chart, which is like a pivot table in chart format. In lesson seven, we're going to learn how to group data in our pivot tables.

If you need help with the topics covered in today's lessons, feel free to post your questions in the Excel Interactive Student Forums. If you're watching this course using my custom video player software or online at my web theater, you should see the student forum for each lesson appear in a small window next to the class videos, if you have an active internet connection. Here, you'll see all the questions that the other students have asked, as well as my responses to them and the comments that some of the other students may have made. I encourage you to read through these questions and answers as you start each lesson. You can post your own questions and comments as well.

If you're not watching your lessons online, you can still visit the student forums later by visiting ExcelLearningZone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually create the same spreadsheet that I make in the video. Build the spreadsheet with me step by step. Don't try to apply what you're learning right now to other projects until you've mastered the sample spreadsheet.

If you get stuck or don't understand something, watch the video again from the beginning, or tell me what's wrong in the student forum.

Most importantly, keep an open mind. Excel might seem intimidating at first, but once you get the hang of it, you'll see that it's really easy to use.

Quiz

Q1. What is the primary topic covered in Excel 2010 Expert Level 7?
A. Data validation techniques
B. Pivot tables
C. Spreadsheet security
D. Conditional formatting

Q2. Which feature is specifically new to Excel 2010 that is mentioned in the video?
A. Charts
B. Macros
C. Slicers
D. Data sorting

Q3. What is recommended before taking this expert level course?
A. Taking only advanced lessons
B. Completing beginner courses 1 through 5 and expert classes 1 through 6
C. Using Excel for at least one year
D. Reading the Excel user manual

Q4. What is the suggested approach for working through the lessons in this course?
A. Try to apply the lessons to all your projects immediately
B. Skip the video and read the transcript
C. Watch each lesson through once, then follow along and create the example spreadsheet step by step
D. Only watch the lessons that seem interesting

Q5. What will lesson two specifically teach you?
A. How to create a macro
B. How to build your first pivot table
C. How to make Excel graphs
D. How to set up workbook security

Q6. What is the purpose of the Excel Interactive Student Forums?
A. To download free templates
B. To post questions, see other students' questions and instructor responses, and make comments
C. To buy additional lessons
D. To access Excel's official documentation

Q7. In which lesson will you learn about grouping data in pivot tables?
A. Lesson three
B. Lesson five
C. Lesson seven
D. Lesson one

Q8. Which type of user is the expert series designed for?
A. Novice users with little or no Excel experience
B. Experienced users already comfortable with Excel
C. Only professional programmers
D. Users who have never used spreadsheets

Q9. What happens after you complete all expert level classes according to the course structure?
A. You repeat the expert lessons
B. You move up to the advanced lessons
C. You stop taking Excel courses
D. You start over with the beginner lessons

Q10. What advice does the instructor give if you do not understand something in a lesson?
A. Ignore it and move on to the next lesson
B. Watch the video again from the beginning or ask for help in the student forum
C. Contact Microsoft support directly
D. Skip lessons that are difficult

Answers: 1-B; 2-C; 3-B; 4-C; 5-B; 6-B; 7-C; 8-B; 9-B; 10-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Excel Learning Zone covers Expert Level 7, which is all about pivot tables in Microsoft Excel 2010. I'm Richard Rost, your instructor for this course, and in this session, I'll be guiding you through everything you need to know about pivot tables, starting from the basics all the way up to more advanced features.

We'll begin by looking at what a pivot table is and why it can be such a powerful tool for analyzing your data. I'll show you how to create a pivot table from scratch, and once we have one set up, I'll walk you through the process of editing your pivot table. This includes modifying which fields are included, expanding and collapsing data, and using sorting and filtering options.

We'll also explore many of the options available within pivot tables. You'll see how to change the design and layout to make your reports clearer and easier to understand. Part of this will involve looking at slicers, which were introduced in Excel 2010 and allow you to interactively filter your pivot tables. If you're using an earlier version of Excel, such as 2007, most of the features in this course still apply, though slicers and a few other options will not be available to you. If you're using Excel 2003 or an older version, the user interface is quite different, so for those cases, I recommend checking out courses Excel 223 and 224 under the Excel 2003 category on my website, where pivot tables for older versions are covered in detail.

This is an expert-level course, so I strongly suggest that you have already completed the first five beginner courses and the previous six expert courses. The concepts and skills taught in those classes are necessary for you to get the most out of today's material. My curriculum is structured into beginner, expert, advanced, and developer levels. The expert level, where we are now, is geared towards users who already feel comfortable with Excel and are ready to learn more about its functions and tools in greater depth. After completing the expert series, you can move on to the advanced courses, which introduce additional features like macros, user forms, and templates and help you add more professional capabilities to your spreadsheets. Finally, there is the developer series, which is dedicated to learning how to program in Visual Basic for Applications (VBA) and fully automate your work in Excel or integrate it with other Office applications.

Each series is organized into different levels. We've already covered five beginner levels, and this class is the seventh in the expert series, building on previous material to help you steadily increase your knowledge of Microsoft Excel. Once you've completed all the expert classes, you can proceed to advanced and developer topics.

Now let's look at exactly what we will cover in today's class. Lesson one explains what pivot tables are, their usefulness, and the kinds of tasks you can accomplish with them. In lesson two, we will build our very first pivot table, creating a report that lists sales by city and by year, complete with totals. Lesson three covers editing pivot tables, including adjusting fields, collapsing and expanding your data, filtering, sorting, and more.

Lesson four examines various options for pivot tables, including slicers, that allow you to visually control which data is displayed. In lesson five, we discuss different design and layout choices you can make for your pivot tables. Lesson six introduces pivot charts, which are graphical representations of your pivot table data. Then in lesson seven, we'll talk about grouping data within your pivot tables, so you can create custom levels and organize your information more effectively.

If you need assistance with the material at any point, you can post questions in the Excel Interactive Student Forums. If you are using my custom video player or watching the lessons online in my web theater, the student forum for each lesson should appear right next to the class video as long as you are connected to the internet. There, you can see questions and answers from other students as well as my own responses. I encourage you to review these before starting each lesson and to participate by adding your own questions or comments.

For those not watching the lessons online, the forums are still accessible by visiting my website at ExcelLearningZone.com.

To maximize your learning, I recommend watching each lesson all the way through once before trying anything on your own computer. Then, go back and follow along step by step, creating the same sample spreadsheet that I demonstrate in the session. Once you have mastered the provided example, you can start applying what you've learned to your own projects.

If you have trouble or something doesn't make sense, watch the lesson again from the beginning, or ask for help in the student forum so I can help you resolve any issues.

Above all, keep an open mind. While Excel can seem overwhelming at first, once you practice these skills you'll find it becomes much more manageable.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

What a pivot table is and its uses
Building a pivot table from scratch
Creating a sales summary by city and year
Editing pivot table fields
Collapsing and expanding pivot table levels
Filtering and sorting pivot table data
Exploring pivot table options
Using slicers in Excel 2010 pivot tables
Customizing pivot table design and layout
Creating pivot charts from pivot table data
Grouping data in pivot tables