Lessons

Resources

Full Course Outline
Skills Test Quiz - Under Construction
Certificate of Completion
Post-Class Survey

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 2003 Expert 224
Get notifications when this page is updated

Intro

In lesson 224, you will learn more advanced data analysis techniques with Microsoft Excel, focusing on Pivot Tables Part 2. We will cover grouping data, generating sub-totals, formatting your pivot tables, creating calculated fields and items, and exploring base fields for running totals and year-over-year comparisons. We will also go through a special employee costs example and make use of functions like IF and VLOOKUP that were covered in earlier courses. This lesson builds on topics from Excel 223 and previous classes and uses Excel XP, but the material applies to most versions.

Transcript

Welcome to Microsoft Excel 224 Data Analysis 5 Pivot Tables Part 2. Brought to you by ExcelLearningZone.com. I am your instructor Richard Rost.

In today's class, we are going to pick up where Pivot Tables Part 1 left off. We are going to talk about grouping data in your pivot tables, generating sub-totals, and formatting our pivot tables to make them look pretty. We will also cover some of the advanced options that we did not cover in the first part of this class.

We will show you how to make calculated fields and calculated items. We will discuss base fields, which allow us to do all kinds of neat things like show running totals and comparisons of previous year data. We have a special sample employee costs lesson that we are going to go over. We will show you how to use various techniques and tricks that we have used in previous lessons, such as IF functions and VLOOKUP, along with all kinds of other useful tools. There is a lot of information packed into today's class.

Before taking this class, you should have taken Excel 223 Data Analysis 4, which is Pivot Tables Part 1, where we started going over pivot tables. You should have also taken a lot of the previous Excel classes, including all the 200-level material, and the 100-level material before that. We will use techniques like the IF function and VLOOKUP that we learned in our previous Excel classes. If none of this seems familiar to you, please review the previous Excel material.

We are still using Excel XP in class. This lesson should be valid for just about every version of Microsoft Excel, with some minor cosmetic differences that you should be able to figure out.

The sample data for this class can be found at ExcelLearningZone.com/excel/224, which will actually redirect you to the 223 page, as we put all the information together on one page. The sample Excel file can also be found on your hard drive in the Program Files\\599CD\\Excel224 folder, if you installed the courseware from our executable file that you downloaded from the website or from our CD-ROM. If you are watching the videos on the website through the Play in Browser, you will have to get the files from the website itself.

If you need help finding anything or have an Excel question, you can contact us at ExcelLearningZone.com/contact.

Quiz

Q1. What is one of the new topics covered in Pivot Tables Part 2 that was not covered in Part 1?
A. Making calculated fields and calculated items
B. Creating bar charts
C. Printing Excel spreadsheets
D. Recording macros

Q2. What main concept does grouping data in a pivot table help with?
A. Organizing and summarizing data for analysis
B. Backing up Excel files
C. Sending emails directly from Excel
D. Splitting cells

Q3. Which techniques from previous lessons will be used in this class?
A. IF functions and VLOOKUP
B. Mail merge and tracking changes
C. Hyperlinks and macros
D. Watermarking sheets

Q4. What is the purpose of exploring base fields in this lesson?
A. To show running totals and comparisons of previous year data
B. To import data from Word documents
C. To reduce file size
D. To animate charts

Q5. Which of the following is TRUE regarding the Excel version used in this class?
A. Excel XP is being used, and the material applies to most Excel versions with minor cosmetic differences
B. Only Excel 2019 is supported for this lesson
C. The lesson is not compatible with Excel Online
D. Excel for Mac features only are demonstrated

Q6. Where can the sample data for this class be found?
A. At ExcelLearningZone.com on the Excel 224 or 223 pages, and on the hard drive in the appropriate folder
B. Only through email request
C. From a Google Drive link provided in the video
D. By searching on Microsoft.com

Q7. What should students do if they are not familiar with the concepts used in this class?
A. Review the previous Excel material from 100-level and 200-level classes
B. Skip to advanced Access courses
C. Guess and hope to understand later
D. Focus only on formulas and ignore other content

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-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 continues our journey with Pivot Tables in Microsoft Excel, building directly on where we left off in Pivot Tables Part 1. In this lesson, I will guide you through grouping data within pivot tables, creating subtotals, and formatting your pivot tables for a polished, professional presentation. We will also get into some advanced pivot table features that were not addressed in the first part of this series.

A key topic in this class involves creating calculated fields and calculated items. I will walk you through base fields, which let us add features like running totals and year-over-year comparisons. There is also a dedicated lesson on sample employee costs, using practical examples to solidify your understanding. Throughout the session, I will show you how to make the most of techniques covered in previous classes, such as using the IF function and VLOOKUP, along with several other valuable tools. This session is packed with information designed to expand your data analysis skills in Excel.

Before diving into this lesson, you should have completed Excel 223: Data Analysis 4, which was Pivot Tables Part 1, as well as the earlier Excel classes at both the 100-level and 200-level. Familiarity with functions like IF and VLOOKUP will be important, so if those concepts are new to you, I recommend reviewing the previous material.

For this video, we are still using Excel XP, though everything you learn here should apply to most other versions of Excel. You might encounter some minor appearance changes in different versions, but nothing that should prevent you from following along.

The sample data used in this lesson is available at the Excel Learning Zone website on the page for Excel 224, which redirects to the 223 page where all related materials are collected. If you have installed the courseware, you will also find the sample Excel file in the Program Files\\599CD\\Excel224 directory on your computer. If you are accessing the course through your browser, you will need to download these files directly from the website.

If you run into any questions or difficulties, or if you need assistance finding resources, you are invited to reach out through the contact page at the Excel Learning Zone website.

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

This is an introduction and overview message for a video tutorial on advanced Pivot Tables in Microsoft Excel.