Introduction

Welcome! Pivot Tables & Charts Guide

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Excel Expert Level 7. In this course we will focus on pivot tables, discussing what they are, why they are useful, and how to build and edit them. We will cover pivot table options, design and layout changes, creating pivot charts, and grouping data. We will also talk about features new in later versions, such as slicers, and review the different levels of courses available. Guidance will be provided on how to use the student forums and get the most out of the lessons by following along step by step.

Navigation

Prev: X07
Next: Lesson 01

Keywords

TechHelp Excel, pivot tables, Excel 2010, pivot charts, slicers, grouping data, edit pivot table, pivot table design, filter and sort pivot table, Excel expert, build pivot table, sales data analysis, collapse expand levels, Excel forums, custom grouping

Subscribe to Introduction
Get notifications when this page is updated

More Information

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 will learn what a pivot table is and why they are useful. You will learn how to build a pivot table from scratch and how to edit a pivot table once you have built one. You will learn about many of the different options available for pivot tables. You will learn how to make design and layout changes to your pivot tables. You will learn how to build pivot charts out of your pivot table data, and you will learn how to create custom grouping levels for your data.

This course was developed for Excel 2010. Most of what is covered is also valid in Excel 2007. There are a couple of features that were new and were added in 2010, like slicers, that you will not have available in previous versions. If you are using Excel 2003 or earlier, the interface is much, 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 are 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 will cover more functions, features, tips, and so on.

When you have mastered the expert classes, move up to the advanced lessons. You will 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 contained 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 will move up to the advanced series, and finally the developer series.

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

In lesson one, we are going to learn what pivot tables are, why they are so useful, and what you can do with them.

In lesson two, we will create our very first pivot table. We will 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 will learn how to edit that pivot table. We will see how to change fields, collapse and expand levels, filter and sort the data, and more.

In lesson four, we will take a look at some of the pivot table options, and we will see a feature called slicers that is new in Excel 2010.

In lesson five, we are going to take a look at some of the pivot table design options.

In lesson six, we will learn how to create a pivot chart, which is like a pivot table in chart format.

In lesson seven, we are going to learn how to group data in our pivot tables.

If you need help with the topics covered in today's lessons, please feel free to post your questions in the Excel Interactive Student forums. If you are 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 will 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. Feel free to post your own questions and comments as well.

If you are 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. Do not try to apply what you are learning right now to other projects until you have mastered the sample spreadsheet.

If you get stuck or do not understand something, watch the video again from the beginning or tell me what is 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 will see that it is really easy to use.

Intro

Quiz

Q1. What is the main topic covered in this expert level course?
A. Pivot tables in Microsoft Excel
B. Formulas and functions in Microsoft Excel
C. VBA programming in Microsoft Excel
D. Formatting worksheets in Microsoft Excel

Q2. Which feature was introduced in the 2010 version that is NOT available in earlier versions like 2007?
A. Slicers
B. Pivot charts
C. Conditional formatting
D. Data validation

Q3. If you are using an older interface like Excel 2003 or earlier and want to learn about pivot tables, what should you do?
A. Visit excellearningzone.com and look for Excel 223 and 224
B. Search for Excel 501 and 502
C. Only watch the current expert 7 class
D. Wait until you upgrade your software

Q4. What is one of the key topics covered in lesson two of this class?
A. Creating a basic pivot table showing sales by city and year
B. Learning how to enter data into a worksheet
C. Writing complex formulas
D. Importing data from Access

Q5. What is recommended before taking this expert level course?
A. Complete all beginner courses 1 through 5, and preferably expert classes 1 through 6
B. Jump directly into expert 7
C. Take developer courses first
D. Only finish beginner level 1

Q6. How does the expert series differ from the beginner series in this course?
A. It goes into more depth, covering advanced functions and features
B. It only reviews the basics again
C. It skips all formulas
D. It is only for chart creation

Q7. What is the main benefit of a pivot table as explained in the video?
A. To quickly summarize and analyze large amounts of data
B. To improve cell formatting
C. To create VBA user forms
D. To calculate loan payments

Q8. What is the recommended approach to learning from this course?
A. Watch the lesson once, then watch again and follow along by creating the same spreadsheet
B. Memorize the script without using Excel
C. Only read the PDF notes
D. Practice with your own unrelated data immediately

Q9. If you get stuck or do not understand something in the course, what should you do?
A. Watch the video again or post your question in the student forum
B. Skip to the next lesson
C. Buy a new textbook
D. Call technical support immediately

Q10. What can students use the Excel Interactive Student forums for?
A. To ask questions about the lessons and read responses from the instructor and other students
B. To report software bugs to Microsoft
C. To request refunds
D. To download new Excel versions

Q11. What is a pivot chart?
A. A chart created from pivot table data
B. A default Excel chart unrelated to pivot tables
C. A VBA-generated graph
D. A type of data validation tool

Q12. Which sequence correctly describes the course levels provided by ExcelLearningZone.com?
A. Beginner, Expert, Advanced, Developer
B. Advanced, Expert, Developer, Beginner
C. Expert, Beginner, Advanced, Developer
D. Developer, Beginner, Expert, Advanced

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A

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 focuses on expert-level concepts in Microsoft Excel 2010, specifically on pivot tables. In this class, I will explain what a pivot table is, how to build one from scratch, and why they are so helpful when working with large sets of data. You will also learn how to modify your pivot tables, work with the different options available, make changes to the design and layout, create pivot charts, and set up custom grouping levels for your data.

This course was originally designed for Excel 2010, but the material is also applicable to Excel 2007, except for a few features like slicers that were introduced in the 2010 version. If you are using Excel 2003 or earlier, the user interface is significantly different, so you will want to check out my other lessons specifically for those versions on my website under the Excel 2003 section. Lessons 223 and 224 cover pivot tables for those older versions.

Since this is an expert-level course, I highly recommend that you complete my beginner lessons 1 through 5 and, ideally, expert lessons 1 through 6 before starting this one. Many skills covered in earlier classes will be essential for what we will cover today.

All of my courses are organized into four categories: beginner, expert, advanced, and developer. The beginner classes are intended for those with little or no prior experience in Excel. The expert series, like the one you are watching now, is for users who are already comfortable with Excel and want to dig deeper into the details of each topic. Here, we build on what is covered in the beginner series and introduce new functions, features, and tips.

Once you feel confident with the expert material, you can move on to the advanced lessons where you will learn to create and use macros, build user forms, design your own templates, and explore a range of features that provide greater functionality and a more professional look to your spreadsheets.

The developer series is focused on teaching you how to program using Visual Basic for Applications within Microsoft Excel. This gives you the ability to automate processes, develop custom programs, and integrate Excel closely with other Office applications.

Within each series, the material is divided into levels. For example, the beginner series has five levels that should be completed first. This class is the seventh level in the expert series. Each level builds on topics from previous classes. Once you have finished all the expert material, you can progress to advanced and then the developer series.

Let me give you a quick overview of what we will cover today.

In the first lesson, I will explain what pivot tables are, why they are valuable, and what you can accomplish with them.

In the second lesson, we will walk through building your first pivot table, using a dataset with sales that are grouped by city and year, including totals for each.

After creating a pivot table, in the third lesson, we will look at how to edit it. This includes changing fields, expanding or collapsing different levels, and using features like filtering and sorting your data.

Lesson four is about the various pivot table options, and I will also show you how to use slicers, which are new in Excel 2010.

In lesson five, the focus will be on design options for your pivot tables.

Lesson six covers creating a pivot chart, which allows you to visualize your pivot table data in chart format.

Finally, in lesson seven, I will explain how to group your data within the pivot tables.

If you want help with any of the topics in these lessons, you can post questions in the Excel Interactive Student forums. If you are watching through my custom video player or the web theater online, you should see the forum appear next to the lesson videos when you are connected to the internet.

In the forums, you can read through questions and answers from other students, see my replies, and interact with fellow learners. Feel free to participate by asking your own questions or sharing comments.

If you are not watching online, you can still visit the forums afterward by going to excellearningzone.com/forums.

To get the most benefit from this course, I recommend that you first watch each lesson all the way through without trying to follow along. Then, watch the lesson a second time while recreating the examples on your own computer. Build the same spreadsheets I show in the videos, following each step as I demonstrate. Wait to apply these techniques to your own projects until you are comfortable with the sample exercises.

If you have any problems or are unsure about something, watching the lesson again or asking in the forums can help.

Remember to keep an open mind while learning. Excel might seem complex at first, but with some practice, you will find it becomes much easier to use.

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 pivot tables are and their uses
Building a pivot table from scratch
Editing a pivot table after creation
Changing fields in a pivot table
Collapsing and expanding pivot table levels
Filtering and sorting pivot table data
Pivot table options overview
Using slicers with pivot tables
Pivot table design and layout changes
Creating pivot charts from pivot table data
Custom grouping levels in pivot tables

Article

Pivot tables are a powerful feature in Microsoft Excel that allow you to quickly summarize, analyze, and present large amounts of data in a flexible and interactive way. If you have ever worked with extensive lists of information, such as sales data for different cities and years, you probably know how challenging it can be to extract meaningful summaries or reports from that data. Pivot tables solve this problem by making it easy to reorganize and slice your data to see exactly the results you need, all without changing your original data.

Let me walk you through what pivot tables are and why they are so useful. Essentially, a pivot table gives you a way to take a large table of raw data and view it in different ways, depending on what you want to see. For example, you can take a sales log with columns for date, product, city, and amount, and use a pivot table to see total sales by city, total sales by year, or even break down sales by both city and year at the same time. The best part is you can rearrange or "pivot" your summary with just a few clicks.

Creating your first pivot table starts with your data. Make sure your information is organized as a list with column headers. For example, suppose you have a sales table with columns for Date, City, and Sales Amount. Once your data is ready, select any cell inside your data range and use the Insert menu to choose Pivot Table. Excel will ask you to confirm the data range and ask where you want to place your pivot table, either in a new worksheet or in an existing one. After you click OK, Excel will create a blank pivot table and display a field list on the right side of the screen.

Building the pivot table from here is simply a matter of dragging fields into different areas. Suppose you want to see total sales by city and by year. Drag the City field into the Rows area to list each city, and drag the Date field, formatted as years, into the Columns area. Put the Sales Amount field into the Values area, and Excel will automatically summarize the amounts for each city and year. If you need to, you can change how the values are calculated by clicking the Values field and selecting options like sum, count, or average.

Once you have built your pivot table, you might want to make changes or explore your data in deeper ways. You can edit your pivot table by moving fields around, adding or removing fields, or even collapsing and expanding different levels to focus on what interests you. For example, you can collapse the year columns to see total sales per city, or expand them to break the numbers down year by year. Filtering and sorting your data is easy too. Use the dropdown menus on your row or column labels to display only certain cities or years, or to sort the data by the totals.

There are many options available to customize how your pivot table looks and behaves. For instance, you can choose from different layout options, such as compact, outline, or tabular forms, which control how your data is displayed. You can also apply different styles or color schemes to make your summaries easier to read. In more recent versions of Excel, slicers are available as a visual way to filter your pivot table. Slicers display as buttons associated with fields like city or year, so you can click what you want to see and the pivot table updates instantly.

Design options for pivot tables go even further. You can format your pivot table to highlight certain values, display grand totals and subtotals, or adjust the way labels are repeated or hidden. This helps you make your summary data clearer and more suitable for presentation.

Sometimes you will want to visualize your summarized data, and pivot charts make this possible. A pivot chart is just like a regular Excel chart, such as a column or bar chart, but it is directly tied to your pivot table. Whenever you rearrange the fields in your pivot table, the associated pivot chart automatically updates to reflect those changes.

Grouping data in pivot tables is another useful feature. Suppose your data includes dates down to the day, but you want to see results summarized by month or quarter. You can select your date field in the pivot table and use the grouping option to group your data by days, months, quarters, or years. You can also group numeric fields, such as grouping ages into ranges, or group text fields, such as sales by region.

If you run into questions while practicing these techniques, many resources and forums are available online where you can ask for help. It is a good idea to read through common questions and answers from other students and users, as these can clarify concepts and help you get past any obstacles you encounter.

As you learn to use pivot tables, I recommend following along with sample data sets first. Try not to immediately apply what you are learning to your most important spreadsheets until you have practiced and feel comfortable building and modifying pivot tables from scratch. Take your time exploring, move fields around, experiment with settings, and do not hesitate to revisit explanations or guides if you get stuck.

Learning Excel deeply takes some patience, but understanding pivot tables is one of the most rewarding skills you can master. Once you get comfortable with them, you will find them invaluable for quickly analyzing and visualizing data, presenting results to others, and making better decisions with your information.

Primary Topics

pivot tables, building pivot tables, editing pivot tables, pivot table options, slicers, pivot table design, pivot charts, grouping data

Secondary Topics

recommended course progression, student forums, learning methodology