Instructor for this course
more

Many Microsoft Excel users feel that pivot tables are somehow beyond their grasp, yet this is one of Excel's easiest and best features. In this course Excel expert David Ringstrom, CPA, shows you how to whip ugly raw data into a format ready for analysis with a pivot table. You'll quickly be dragging and dropping fields to instantly create reports from lists of data. For this session David offers four different handouts that are specific to Excel 2013, 2010, 2007, and 2003, respectively. 

You learn how to:

  • Quickly whip unwieldy data into the format required for pivot table analysis.
  • Use a simple keyboard shortcut to post the same formula to multiple cells at once.
  • See how the Table feature can vastly improve the integrity of pivot tables in Excel.
  • Explore the basics of pivot table creation.
  • See the nuances of subtotaling data within a pivot table.
  • Dig deeper into the numbers by using the Report Filter command to create break-out tables.
  • Incorporate calculations within, or alongside, pivot tables.
  • Disable the GETPIVOTDATA function if you don't need it for your analysis.
  • See how pivot tables differ from worksheet formulas and the importance of the Refresh command.

Intro Video Transcript

Hello, I am David Ringstrom, a CPA from Atlanta and welcome to this Illumeo course session entitled Excel Efficiency: Pivot Tables.

This class presents a special challenge for me for two reasons. One, it's tough to go very deep on pivot tables in just an hour, but you'll find I pack a lot of content in. So we'll go from cleaning up data so that it becomes pivot table ready, to then covering a surprising number of details about pivot tables in general. So by the end of the hour, you should be much more comfortable working with pivot tables than you presently are. Secondly, the other challenge is that the pivot table feature has evolved a great deal from Excel 2003 through 2013. And so the best way that I have found to handle these distinctions and these differences is to actually, for this presentation, provide four different handouts for the material.

So I've created the PowerPoint four times, if you will. Once for Excel 2003, 2007, 2010, and 2013. So in the video you will see me teach from Excel 2010, but you will be able to download PDF versions of the PowerPoint presentations that match your version of Excel. And then you can carry out the numbered steps that you saw me do on the screen and have that as reference material.

I'm going to be teaching from Excel 2010 today, but pretty much everything I cover is going to be in the same location in Excel 2013 or Excel 2007. If there's any big differences, then I'll make notes of that on the corresponding slides. The biggest difference that you will note is that Excel 2007 does not have a File menu. So during this presentation if you see me click on the File menu or I mention the File menu and you are using Excel 2007, just note that you will click on that little round button, it's called the Office button, instead. If you happen to still be using Excel 2003, then some of the slides will have notes in blue along the bottom, and those are the equivalent steps you would carry out in your version of Excel.

Let's first take a look at our raw text file. So we see that at the bottom, it's called Fruit.txt and we can see the Region ID only shows up on one row. The Region shows up on one row, the City ID, the City, and so on. So there's the gaps that we're going to fill in. Also there is a blank row after the equal signs. We're going to want to eliminate those equal signs. And there're also some headings that repeat in the midst of our data.

So we see how the file looks in Notepad. To open it in Excel, we'll go to the File menu. We'll choose Open, and in this case we're going to need to change our file type, Step 3, to text files so that we can see our Fruit.txt file. We double click on it, and it then opens up in Excel.

At this point the Text Import Wizard opens on the screen, and typically Excel's going to guess correctly whether our data is delimited or fixed width. Delimited signifies that there is some type of separator between the fields, and in this case we have a Tab character between each field. If the file were fixed width, then we'd have to specify where the column breaks were because each column would take up a certain number of spaces on the screen, but in this case it's a delimited file.

So Step 5, we click Next. That brings us to the second screen of the Wizard where we could adjust the delimiter if we need to. By default, Excel sets the delimiter to Tab, so we are going to accept that default choice of Tab and then click Next, which brings us to the third screen of the Wizard.

At this point we can make some adjustments to our data, and one of the things that's frustrating when we open text files in Excel is that Excel throws away the leading zeroes. And so, in this case, we've got some columns that have leading zeroes that we'd like to preserve. So to preserve those, we're going to click on the Column. So when we see Step 7, we click on the Reg ID column and then we mark it as Text. We can repeat that for the City ID column, the Vendor ID column, and the Item ID column.

Other columns, if there's columns that we don't want to keep, we can throw those away. And to do so, if we scroll over to the right we will see that there is a unit of measure column that's marked U/M. We can click on that unit of measure column and mark it as Step 11, Do Not Import, and then click Finish. And so what that will do, these steps here on this slide allow us to keep our leading zeroes, and then throw away the unit of measure column that we didn't need to see with our pivot table analysis. When we're done with our analysis, we click Finish.

Let's go to Excel and carry out these steps. So we are going to go to the File menu, choose Open and notice I've already changed my File type to Text Files. Normally, it's going to default to Excel files. In this case, it already changed it to Text Files so that I could view my Fruit.txt file. I double click on it. The Text Import Wizard launches. The default choice is delimited. Again, this is a tab delimited file. I can't tell that on the initial screen, but when I click Next in the data preview window, I'm going to see that the data breaks up in the columns. So I can unclick Tab. The data jams back together. Click Tab. That signifies I've chosen the correct delimiter.

Click Next again and this is the screen where I get to modify the columns. So if I want to preserve those leading zeroes, I can mark that as Text. I can click here, mark this Text. Choose that Column, mark it as Text. Scroll over. See if the additional columns will change Item ID, just to be safe, in case it has leading zeroes. The unit of measure, we wanted to throw that column away. I click on that Column, say Do Not Import and then when I click Finish, we'll see that all the columns will come into my spreadsheet except unit of measure will get thrown away.

Learning Objectives

  • Transform raw data into the ideal format for pivot table analysis
  • Simplify pivot table maintenance by way of Excel's Table feature
  • Contrast different methods for performing calculations within and outside of pivot tables

173 Reviews (441 ratings)Reviews

5
Member's Profile
Course material was thorough and gave me a good review of pivot tables and taught me the reason to use tables
5
Member's Profile
Nice presentation. Very helpful. A good quick introduction to PTs.
4
Member's Profile
The slides have way too much data and are confusing as a reference tool. This was excellent for a much better understanding of how to use pivot tables. I especially like all of the shortcuts that you included. I always use keyboard shortcuts when possible. A lot of data in a short period of time, so much more that could be covered so hopefully there is an advanced course in the works.
5
Member's Profile
As the presenter acknowledges, 1 hr is not nearly enough time for this topic. Many useful tips but pace is very hurried if a beginner. That said, I've taken about 17 hrs of this presenter's classes (between Proformative and other outlets) and am constantly amazed at how much more there is to know. Another very valuable resource!
5
Anonymous Author
Screen views were very helpful. Instructor did a good job of describing what you were going to be clicking and why before actually showing a walk-through of clicking them. Allowed you to be ready to learn how to do it without being distracted by clicks and new menus first.
5
Member's Profile
Good course. Mr. Ringstrom goes fast, but having the ability to come back and review important information later will make this course very useful. It would be great to have separate classes for Excel 2003, 2007, 2010 & 2013 so we wouldn't need to learn all four.
5
Member's Profile
Circled numbers and letters on the screen shots to show the steps in detail, followed with real example demonstration. Most effective and efficient way to make student understand Pivot table functions in detail in a short one hour lesson. Amazing class!
Anonymous Author
Great topic and the course was good for an introduction to Pivot tables. However, for me it was difficult to follow along primarily because the slides were a little too cluttered and also because I have an older version of Excel (v 2008 for MAC).
5
Anonymous Author
Very helpful course. I am an avid pivot table user already, and I still learned a great deal. I especially liked how he differentiated between the different versions of Excel and gave easy shortcuts. Will definitely take another course he does!
5
Member's Profile
Excellent introduction to Pivot Tables, which covers the basic differences between key different Excel versions. Good detail on the presentation slides, which are numbered by steps to follow the processes and see the screenshots.
5
Member's Profile
Very well organized in terms of comparing the various changes in Pivot Tables features for Excel versions from 2003 to 2013. The only drawback was some difficultly in trying to cover so much material on only one hour.
5
Anonymous Author
Needed a crash course on pivot tables and this course didn't disappoint. David's combination of lecture and interactive examples helped reinforce the course content. A lot of info packed into a short period of time.
4
Member's Profile
Very good tutorial with good pacing and flow from one subject to another. More in depth than I thought it would be. The Powerpoint accompaniment is a bit busy though. Best to separate the blocks on more slides.
3
Member's Profile
Good information but the structure with the screenshots and instructions were way too busy and rushed through. Would have been just as good to go straight to the live demo for each topic and expand more there.
5
Member's Profile
Good stuff. I regularly use pivot tables and I learned some things I didn't know that will make my life easier. Great presentation - would like to see the next one for more advanced stuff.
5
Member's Profile
The PowerPoint slides were good for background and then the visual, step by step live showing crystallized the teaching for me. I am a visual person so showing me the steps was very helpful
4
Member's Profile
The course overall thought me a lot and I appreciate the reference books David recommended. I will definitely need to look into them and do a few lessons to get a better grasp of the topic.
4
Member's Profile
I didn't know about the ability to turn off the GetPivotData feature. That was a great thing to learn, will save me lots of time. It would be nice to see a course focused only on Excel 2016.
5
Anonymous Author
I loved everything. There is a lot of information - Fast Paced. Starting and stopping the video to understand and practice is helpful for everyone from beginner to non-beginner.
4
Anonymous Author
Learned a few new things here; didn't necessarily needed to see the multiple ways to do the same thing. The easiest route is likely automatically preferred for most people.
4
Anonymous Author
Great overview - It became a little confusing when using several versions of excel. I believe it should be focused on the versions where significant changes had been made.
5
Member's Profile
thorough explanations provided at just the right pace. providing excel tips and keyboard shortcuts along the way that are not specific to pivot tables was also beneficial.
5
Anonymous Author
Lots of info for creating and using pivot tables. This course also included other useful techniques for organizing and sorting data even if you don't use a pivot table.
5
Anonymous Author
This course is well organized for Pivot Tables. You are "walked through" the steps in a slide and then those steps are immediately reinforced in an actual pivot table.
5
Member's Profile
Great review of pivot table capabilities across different versions of Excel. I especially appreciated the select blanks and formula approach to filling in missing data.
5
Member's Profile
This course highlighted the many features that Excel pivot tables that I was unaware of. The course desribed the techniques to become a more efficient table builder.
5
Member's Profile
Excellent. Although I use pivot tables already, there were so many helpful tips that I did not know that will help to increase my efficiency and productivity.
3
Anonymous Author
Presenter understands Excel and Pivot tables very well. A little confusing with the various versions of Excel although provides the broadest training option.
5
Member's Profile
David does a good job explaining the material then demonstrating it as an example. A lot of good information regarding pivot tables in this session.
5
Anonymous Author
I've used Excel and Pivot Tables for a while and always considered myself proficient however did learn new tips and tricks that will be very useful.
4
Anonymous Author
Very helpful trick to fill in missing data in blank cells. The presentation was effective but could be made easier to follow by using transitions.
5
Member's Profile
Instructor does a great job explaining some cool pivot table features. I had considered myself an expert user and I still learned a few things.
5
Member's Profile
I liked the simple style of presentation and the way the instructions are repeated and reinforced from presentation slide to excel workbook.
5
Anonymous Author
Great course, effective instructor, material is well prepared. Particularly appreciated the effort to look at 4 different versions of Excel.
5
Member's Profile
I think the course was very informative; however, I think the material should have been presented in two sessions. There was a lot to cover.
5
Anonymous Author
Fantastic course. In addition to learning about Pivot tables, I picked up a lot of tips and tricks for other functions within Excel. 12
4
Anonymous Author
The course was well presented. The materials used are well demonstrated and easy to follow. The presenters voice was loud and clear.
5
Anonymous Author
Great information on Pivot Table. I really felt like I learnt a lot of new tricks and I have been using Pivot table for four years.
4
Member's Profile
Personally this was over my head as I am new to Pivot Tables, however, I thought this was a good course. I learned a few things too.
4
Member's Profile
The PDF supporting materials are all the same. The 2010 does not show the correct 2010 commands, it is for a prior version of excel.
5
Anonymous Author
The course is well structured and explanations are clear. I expected a bit more pivot table functions included in the course
5
Member's Profile
I like how the instructor gave an overall depiction of what to do and then illustrated it. His delivery was easy to understand.
3
Member's Profile
It was hard to follow. I think this type of training is better live when you are working the examples along with the instructor.
5
Anonymous Author
Great course. Provided detailed information about pivot tables that is difficult to get elsewhere. Will definitely use again.
5
Member's Profile
I learned a few new shortcuts. The screens were a little too busy but the speaker explained what he was doing very well.
4
Anonymous Author
Some useful tips for different pivot table functions. Like that slides are provided for the different versions of excel.
5
Member's Profile
Very practical course. It gets a little overwhelming with the differences between the different Excel versions though.
5
Member's Profile
Excellent well delivered course for those who do not use pivot tables very much.and good refresher for everyone else.
4
Anonymous Author
I like the way the instructor explained the function first then showed you exactly how to do it on the application.
3
Member's Profile
Provided some helpful tips that will help me work more efficiently. I'll be looking for a Pivot Graphs lesson next!
5
Anonymous Author
The approach of using the slides with the step by step instructions before showing in Excel was extremely helpful.
5
Anonymous Author
Great insights. Best information gained was how to easily fill blank information from a text file once imported.
4
Member's Profile
Learned a few new tricks for pivot tables. Didn't know that you could create pivot table from the table command.
5
Member's Profile
Really good pivot table course. Only issue is the number of versions he teaches to. Still very worthwhile.
4
Anonymous Author
Excellent mini-course with lots of tips & techniques. It takes some of the mystery away from Pivot Tables.
5
Member's Profile
This course was a good overview of pivot tables and how to better utilize them in excel for audit functions
4
Member's Profile
A lot of information and a step by step instruction. Very useful for anyone who has use for pivot tables.
5
Member's Profile
I thought I knew how to do pivot tables but I now know there is so much more. They can be done easier.
5
Member's Profile
Very helpful overview. David goes quickly, but not too quickly...he covers a lot of ground in one hour
5
Member's Profile
This has improve my excel knowledge tenfold. thank you for your well thought out plan and explanations.
5
Anonymous Author
I use Pivot tables almost every day and this class taught me a few new things- definitely recommended!
5
Member's Profile
I enjoyed this course. It was a good refreshed for Excel, and gave me some tools I did not know about.
5
Member's Profile
Reeeally good shortcuts and tips for even the advanced Pivot Table user. Very worthwhile course.
5
Member's Profile
Very good information -- the Excel file helped to work through the topics as they were discussed
5
Member's Profile
This is great because even though I have 2016, when I get a job, my employer may not have 2016.
4
Anonymous Author
Answers many questions regarding the "unknown" functions of Pivot table. Now life is easier :-)
3
Member's Profile
don't need the static slide at beginning of each video. inconsistent volume within each video
5
Member's Profile
Good review of Pivot Tables. Learned some new tricks and brushed up on older skills. Thanks!
5
Anonymous Author
This is a powerful tool, great to see how this can be used and effectively used with reports.
5
Anonymous Author
This is a great update on the additional features and functions for newer versions of Excel!
5
Member's Profile
I found the course very effective. I learned several things and I am a excellent Excel user.
5
Member's Profile
Provides techniques that I was not familiar with even thought I use pivot tables frequently
4
Member's Profile
For someone who uses PivotTables, this was a bit basic but I still gained a few good tips.
4
Member's Profile
I liked, and learned very useful tricks which will allow me to accomplish my tasks faster.
5
Member's Profile
Excellent introduction to pivot tables. Will provide great help for basic analyze work.
5
Member's Profile
As promised, David packed a LOT of helpful information in this brief course. Great job!
5
Anonymous Author
Kind of long for a 1 hour course. With the time spent practicing skills, more like 1.5
5
Anonymous Author
The explanation of the pivot table options that are buried in menus was very helpful.
5
Member's Profile
Comprehensive subject matter coverage; move at your own pace; relevant subject matter
5
Anonymous Author
Very good course, detailed and informative. It will be helpful for entry level users.
5
Anonymous Author
I wish course should be little lengthly and cover more item related to Pivot table.
5
Member's Profile
I like the examples in excel and the explanation of the different versions of excel.
3
Anonymous Author
It would be helpful if a spreadsheet of the data he was manipulating was available.
5
Member's Profile
Pivot Tables are a must when compiling data so this is a great refresher course.
5
Anonymous Author
Though I have been using Pivot tables after this I learned some more techniques
5
Anonymous Author
Using the Report Filter to create multiple tabs is relevant and nice to learn.
5
Member's Profile
I like that these are actually recorded webinars and not just reading material.
4
Member's Profile
Good refresher course w/some advance features such as getpivot data function.
Member's Profile
A lot of information in a short amount of time. Additional redue would help.
5
Member's Profile
Lots to learn in this one..may take a replay or tow but well worth the time
4
Member's Profile
I liked learning about all the things you can do with the pivot table tools
3
Anonymous Author
Decent course - bit fast paced and need to practice more using the notes.
5
Member's Profile
There were several very helpful keyboard shortcuts offered in this course
4
Member's Profile
Liked the comparing and contrasting with the multiple versions of Excel
5
Member's Profile
Very good explanation of Pivot Tables. Easy to follow and understand.
5
Anonymous Author
Decent delivery by instructor. Not for beginners but still clear.
4
Anonymous Author
the course was informative but each lesson ends very abruptly
5
Member's Profile
Harder to understand than i thought with my pivot experience.
4
Anonymous Author
Learned a number of useful functions - worth the hour spent.
5
Member's Profile
Excellent course. Very valuable for day-to-day excel users.
5
Anonymous Author
Very informative and helpful for a novice pivot table user.
4
Anonymous Author
This is very good for the people who always make a report.
5
Member's Profile
I really like this course it made me a better excel person
5
Member's Profile
Clarity in presentation. Explain logic followed by example
5
Anonymous Author
Clearly explained and demonstrated the various functions.
4
Anonymous Author
The process was a bit hard to follow not being hands-on.
5
Member's Profile
Excellent material. Was very pleased with the content.
4
Anonymous Author
Great tips for making pivot tables of various kinds!
5
Member's Profile
Great! The instructor was really effective. Thanks.
4
Member's Profile
Nice course for learning basics about Pivot tables
5
Member's Profile
Great course wish the time was longer than an hour
3
Anonymous Author
Constant audio changes gets a little distracting.
4
Anonymous Author
Thank you for making pivot tables interesting
4
Anonymous Author
Helpful, pivot tables confuse many Excel users
5
Member's Profile
Helpful excel tips for all versions of excel
5
Member's Profile
Great course! Very informative and helpful.
5
Anonymous Author
Very relevant topics and useful shortcuts.
4
Anonymous Author
The data on the slide is a little busy
5
Member's Profile
Loved it ... saved a lot of time for me
5
Anonymous Author
was able to easily follow the examples
3
Anonymous Author
Lesson starts and ends very abruptly
5
Member's Profile
it was very informative, tons of info
3
Member's Profile
Little more basic than I was hoping
4
Member's Profile
Very informative and well laid out
5
Member's Profile
Good materials showing every step.
5
Member's Profile
Good introduction to Excel pivots
5
Member's Profile
awesome...wish I had it years ago
5
Member's Profile
Learned some very useful tips
5
Anonymous Author
Great Course! Learned a lot.
5
Anonymous Author
Very informative and useful.
3
Anonymous Author
A lot of useful information
5
Member's Profile
pace was a little too fast
4
Anonymous Author
Very helpful Excel course!
5
Anonymous Author
I like the short cut keys
4
Anonymous Author
It was a very good course
5
Anonymous Author
Another excellent course
4
Member's Profile
very clear explanations
4
Member's Profile
Very easy to follow.
5
Anonymous Author
Easy to follow along.
5
Member's Profile
another great class!
5
Member's Profile
clear presentation
5
Member's Profile
Easy to understand
5
Member's Profile
thanks, well done!
4
Anonymous Author
Quite insightful!
5
Anonymous Author
Very informative
5
Anonymous Author
Tough questions!
5
Anonymous Author
shortcut methods
5
Member's Profile
Learned a lot!!
5
Member's Profile
Very helpful!!
5
Member's Profile
great examples
5
Member's Profile
Great course!
4
Member's Profile
Great Course!
4
Anonymous Author
Great course.
5
Anonymous Author
Perfect pace.
4
Member's Profile
Very helpful!
Anonymous Author
value course
4
Anonymous Author
informative!
5
Anonymous Author
Useful info
3
Anonymous Author
Good review
4
Anonymous Author
Very useful
5
Member's Profile
Excellent
5
Anonymous Author
Very good.
5
Member's Profile
Excellent.
5
Anonymous Author
Good one
4
Anonymous Author
God job!
4
Member's Profile
good job
4
Anonymous Author
helpful
3
Member's Profile
Useful.
5
Member's Profile
Great.
4
Anonymous Author
Fine
4
Anonymous Author
good
4
Anonymous Author
good
4
Member's Profile
ok

Prerequisites

Course Complexity: Advanced

Prerequisite: Proficiency with Excel

 

Advanced Preparation: None

 

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 ext. 106, or send an e-mail to .
Course Syllabus
INTRODUCTION AND OVERVIEW
Pivot Tables
  7:18Filter Blank Rows, Fill Blank Cells
  6:21Table Feature; Creating a Pivot Table; Removing Fields
  8:56Subtotaling; Expanding-Collapsing Fields
  8:21Renaming Fields; Report Filter feature
  11:41Pivot Table Calculations
  6:54Refreshing Pivot Tables
Conclusion
  2:47Closing Remarks
Continuous Play
  58:14Excel Efficiency: Pivot Tables
SUPPORTING MATERIALS
  PDFSlides: Excel Efficiency Pivot Tables-Excel 2003
  PDFSlides: Excel Efficiency Pivot Tables-Excel 2007
  PDFSlides: Excel Efficiency Pivot Tables-Excel 2010
  PDFSlides: Excel Efficiency Pivot Tables-Excel 2013
  PDFExcel Efficiency: Pivot Tables Glossary/Index
  xlsExcel Efficiency Pivot Tables-Excel 2003
  xlsxExcel Efficiency Pivot Tables-Excel 2007-2013
REVIEW & TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM