This online course is 6 weeks long, followed by a 2-week period to complete the final exam (online, open book). Lessons are released on Wednesdays and Fridays of each week. You are not required to be online at any specific time. You register and pay on our website and instructions on how to access your course will be emailed to you immediately after registration.

The actual time commitment involved in completing any given lesson can vary significantly based on a number of factors including reading speed, familiarity with the topic, related experience, the amount of time spent completing optional assignments, and involvement with discussion board. For planning purposes we suggest setting aside 2 hours per lesson as a starting point.

Microsoft Excel - Pivot Tables

Microsoft Excel - Pivot Tables

Wouldn't it be great to learn how to effectively use all the advanced Excel pivot table features? In this practical and information-packed course, you'll see how to maximize this program's functions and capabilities. 

Most organizations rely heavily on Microsoft Excel pivot tables to analyze and report financial information. Your company is probably no exception. By learning these advanced techniques, you can become more valuable to your organization.

You'll see how to work with the numerous pivot table options and system settings to become skilled in developing useful analysis models and reports within your company. Impress your coworkers by learning how to create functional and eye-catching interactive dashboards using a combination of pivot tables, Pivot Charts, and Slicers. You'll discover advanced techniques for pivot tables, like creating Timelines, calculated fields, and calculated items. You'll learn how to use Excel's Table function to efficiently manage changes to the pivot table's source data in order to avoid reporting mistakes when data is added or deleted.

Whether you're new to pivot tables, need a refresher, or want to become the pivot table guru within your organization, this course is for you!

Week 1 Wednesday - Lesson 1

Pivot Table Concepts
In our first lesson, you'll develop a solid understanding of the requirements to use pivot tables. We'll go over the foundational elements you need to understand to get the most out of pivot tables, and we'll take a peek into the sometimes-intimidating Pivot Table Fields task pane and pivot table-specific Ribbon tabs.

Week 1 Friday - Lesson 2

Creating a Pivot Table
Not all data is created equal. In this lesson, you'll learn what's required to make sure your data is organized well enough to be used in a pivot table. I'll give you an example of a very common issue that occurs when the source data changes or expands, and I'll show you how to easily eliminate that issue in just a few quick clicks of the mouse.

Week 2 Wednesday - Lesson 3

Pivot Table Analyze and Design Tabs

Working efficiently with pivot tables requires knowing where to find the proper pivot table feature without searching high and low for it. Most pivot table features reside on one of the two pivot table-specific Ribbon tabs. Knowing how the different features are grouped and what each one provides is a must if you want to consider yourself a master pivot table user, so we'll explore all of it in today's lesson.

Week 2 Friday - Lesson 4

Pivot Table Formatting Inside and Out
Formatting a pivot table report can be challenging if you don't know all the secrets. In this lesson, you'll learn how to format cells and numbers, as well as how to handle empty cells or errors in your source data. In addition, you'll get some exposure to renaming fields and how to sort and filter data within your pivot table report.

Week 3 Wednesday - Lesson 5

Pivot Table Options and Field Settings
Most users aren't aware there are certain options and settings that provide little-known but extremely useful features. Most of these features are accessed through the pivot table Ribbon tabs, but some aren't. In this lesson, you'll learn how to use some of the more obscure settings in the Pivot Table Options and Field Settings dialog boxes.

Week 3 Friday - Lesson 6

Value Field Settings
Similar to the Field Settings, the Values section of a pivot table report have unique settings and summarization options. For most people, simply summing or counting the data within the Values section is enough. But once you learn how to use these lesser-known settings, you'll go from an average pivot table user to an expert.

Week 4 Wednesday - Lesson 7

Grouping Pivot Table Fields
A pivot table is ideal for analyzing and summarizing data. By default, the table does an excellent job at summarizing the data within the various fields, but there is a way to summarize the data even further. In this lesson, we'll group ordinary pivot table fields, which will create new fields that you can use within the pivot table or as a Slicer. In addition, you'll learn how to expand and collapse fields to help in summarizing the pivot table data.

Week 4 Friday - Lesson 8

Data-Integrity Checks and Report Filters
A pivot table is a great way to locate data anomalies in large data sets that would otherwise be hard to find scanning through the source table. In addition, the proper use of report filters can assist in any data investigation, as well as providing a great tool to create multiple reports from a single pivot table report. You'll learn all about these tools in today's lesson.

Week 5 Wednesday - Lesson 9

Cloning a Pivot Table and the Wonderful World of Slicers
Pivot tables are extremely useful and flexible for data analysis and reporting. It's easy to add, remove, or simply move fields to create different-looking reports from the same source data. Routinely, it's necessary to create different versions of reports, but the best way to do so isn't as straightforward as you might think. You'll see what I mean in this lesson.

Week 5 Friday - Lesson 10

Calculated Fields and Items
Pivot tables are great for analyzing and reporting information, but they're limited in their functionality when your source data is missing needed information. Or, perhaps the information isn't needed in the source data and is only needed for analysis for a short time. Using Excel's Calculated Items and Fields feature provides you with a way to create items that are missing from the source data or that you need for a one-time analysis. You'll see what I mean when we practice using it in today's lesson.

Week 6 Wednesday - Lesson 11

Working With Slicers and Pivot Charts
Your pivot table isn't complete without a Slicer and pivot chart. In order to create an effective dashboard, a pivot table and pivot chart controlled by multiple Slicers is a must. Creating and managing Slicers and Pivot Charts is really quite simple and a lot of fun. You'll see what I mean in today's lesson.

Week 6 Friday - Lesson 12

Timelines and Dashboard Development
There are many interactive tools and techniques available to make pivot tables and pivot charts fun to use, and we'll go over some of my favorites in this, our final lesson. Similar to a Slicer, Excel offers a tool called Timelines. Timelines are available when a field available in your pivot table or chart is a date. Within a dashboard, using the Timeline feature can be useful and eye catching to the users.

Microsoft Windows 7, Microsoft Windows 8.x, or Microsoft Windows 10; Microsoft Excel 2010, Microsoft Excel 2013, or Microsoft Office 2016.  A free 30-day trial of Office 365 Home Premium may be available at Office Online. Then, a subscription can be purchased for as little as $6.99 per month.

Please be sure to install the software on your computer before the course begins. 

Note: This course is not suitable for Macintosh users who are using the Office Mac Home versions or for users of older versions of Microsoft Excel. "Starter Version" and "Web App" versions of Microsoft Excel will not work with the full version of Excel taught in this course.

Course Details
This course is fully online, you require internet access and an email account. The course duration is 6 weeks, followed by a 2-week period to complete the final exam (online, open book). Lessons are released on Wednesdays and Fridays of each week, for a total of 12. You are not required to be online at any specific time.

In addition to the specific lesson content, there is a discussion board with each lesson and often there is an optional assignment to apply the learning.

Following each lesson, there is a short multiple choice quiz. Your score on these quizzes does not count towards the final mark but completing these helps solidify your learning as well as prepare you for the final exam.

The final exam is an open-book, multiple choice exam and you need to achieve a minimum of 65% on the final exam to pass the course. There is only one opportunity to pass the exam. A certificate of completion from Ed2Go is available for printing immediately upon successful completion of the course and a certificate from the University of Waterloo will be emailed typically 1-2 weeks later.

Many of the Ed2Go courses are eligible towards the various online certificates offered by WatSPEED.

Choose your course start date:

Jun 14, 2023Jul 12, 2023Aug 16, 2023Sep 13, 2023Oct 18, 2023