Module 2: Source Data Basics

Getting the source data in the right layout/format is absolutely critical for building pivot tables.  

In Module 2 you will learn some of the basics to preparing your data.  This includes: getting the data in a tabular format, deleting or handling blanks, converting text to numbers or dates, and using Excel Tables.  

These steps will become your checklist for preparing any data set for use with a pivot table.  Doing the prep work up front saves a TON of time and makes pivot tables much easier to work with.

Modules 3: Pivot Table Basics

In Module 3 you will learn how to insert and build pivot tables.  This includes tips and shortcuts for creating pivot tables.  

We also learn about the Pivot Table Field List.  The Field List is the control panel for the pivot table where we drag-and-drop fields to build the reports.  

You will learn about each Area of the pivot table, and how we can use them to quickly change the layout of our reports.  

This will help you take the guess work out of it.  No more trial and error with dragging and dropping fields into different areas.  You will be able to visualize what the pivot table will look like before building it out.

Module 4: Formatting, Layout & Calculations

Formatting our reports to meet the boss's expectations is probably one of the most important parts of the job.  In Module 4 you will learn:

• How to apply number formatting to make your pivot tables look good.
• The 3 different report layouts for pivot tables.
• How to style the pivot table with different fill and border colors.
• The pivot table options menu that contains 30+ settings, and which ones are important.
• How to change the calculation type of create percentage of total metrics in your pivot tables.

Modules 5: Transforming Source Data with Power Query

Often times our source data is not in the right layout for a pivot table.  If the source data isn't right, then the pivot table won't work the way we expect it to.

In Module 5 you will learn how to transform your data into the right layout using Power Query .  This is referred to as Unpivoting or Normalizing a data set.

Power Query (now called Get & Transform in Excel 2016) is a great new data transformation tool that makes this process easy.  It is available for Excel for Windows for versions 2010 and beyond.  In this module I give an overview of how Power Query works, and then explain how to use it to transform our data with a series of step-by-step videos.

We look at how to Unpivot or Normalize different types of reports such as Actual vs Budget reports and inventory reports.  We prepare the data for use in pivot table so we can easily create flexible pivot table reports.

Module 6: Adding More Data

In Module 6 you will learn how to add more data to your source range, and how to refresh the pivot tables.

I explain why using Excel Tables are critical for this process.  They not only make it easier to add new data, but also help prevent a lot of errors.  This module will help you create systems for updating your reports with new data on a periodic basis.

Getting the Tables and pivot tables setup properly in the beginning can save hours of work and frustration in the future when we need to add new data to our pivot tables.

Module 7: Grouping Date Fields

Typically our source data has columns that contains dates.  We might want to create a pivot table report that groups those dates by year, quarter, month, week, etc.  

There are two main ways to group dates with Pivot Tables.

1. Pivot Tables have a built-in Group Fields feature that can quickly group date fields.
2. We can add columns to our source data that contains formulas to create groups.

In Module 7 you will learn both of these methods, and the pros and cons for each method.  You will also learn how to group dates for fiscal calendars.

Module 8: Pivot Table Filtering Techniques

In Module 8 you will learn time saving techniques for filtering your pivot tables.  This includes:

• How to apply filters to the different areas of the pivot table.
• Creating filters that automatically filter for the current time period (week, month, year, etc.).
• Creating filters for multiple criteria on the values area using AND and OR logic.  
  For example, how to apply a filter to only include transactions with revenue greater than $1,000 and quantity less than 30.

Along with the videos you are also getting the Excel files I use throughout the course. 

You get the BEFORE and AFTER files, so you can follow along with the videos and practice the techniques.  This type of hands-on learning will help you improve your skills quickly.

The course site is very easy to use. It tracks your progress for you, allowing you to come back right where you left off. This keeps you organized and helps you focus your time on learning.

You can access the course anytime, from any device. Watch the videos on any tablet or smartphone.

Each page in the course has a comments section. You can ask questions here and also see what other students in the community are asking. I will be here answering your questions and helping you along the way.

You will be emailed this awesome Certificate of Achievement when you complete the course.  You can automatically post it to your LinkedIn profile, share it on social media, add it to your resume, and print & frame it.  

This will help you market your new skills and let employers know that you are a certified Excel Superstar!

Slicers are an awesome tool that allow us to make our pivot tables and charts interactive.  Users love this feature.

In this bonus module I share creative solutions for various ways to use slicers.  This includes techniques like: adding a search box to a slicer, filtering/sorting a slicer with a slicer, dynamic chart labels, MTD comparisons, and much more.

These techniques will help bring life to your workbooks and make them easier to use.

Sometimes we have source data in multiple worksheets or workbooks.  It can be challenging and time consuming to combine the data for use with a pivot table.

Power Query (Get & Transform) makes this process much easier.  In this bonus module you will learn how to combine data from multiple worksheets or workbooks.

We look at multiple examples of how to clean up and transform the data, then combine (append) it into one large table.  If you have formatted reports that look like the example above then this training will save you a TON of time! 

Power Query allows us to setup automated systems for cleaning up, transforming, and combining the data.  New worksheets or workbooks can automatically be added to the output Table when new data is published every period.

The Combine Data with Power Query Course ($49 value) is included as a free bonus with your enrollment.  The course includes 9 tutorial videos that will teach you how simplify this process and prepare your data for use with pivot tables.