Go back to Courses

Excel Fundamentals Boot Camp

  • On-Demand
  • Coached
Author: Ken Puls
In the Excel Fundamentals Boot Camp you’ll learn all the core skills for the Excel analyst, beginning with a refresher on Excel formulas and functions (including the powerful VLOOKUP), and then moving into PivotTables, data visualizations, and using Power Query to gather, clean, and transform your data.
Course

Excel Fundamentals Boot Camp

$695 Renews at $169 per year
(All prices in USD)
This Course contains:
31h of training content
18 Content Modules
3 Workshop Lab
Downloadable files and Handouts
Support and Discussion groups
Certificate of completion
DURATION:
31h
TOOLS:
Excel
SKILLS:
Excel Formulas
Data Modeling
Data Visualization
Data Preparation

What you'll learn?

Excel formulas and functions
Structuring data into tables
Deep dive into VLOOKUP
Introduction to data visualization
Understanding and building PivotTables
Overview of Power Query
Appending and merging data
Recipes for transforming data
Using conditional logic
Practical skills application

Course Overview

Course Description

In the Fundamentals Boot Camp, you will begin with a review core skills for the Excel analyst. This section is geared to teach critical skills to less experienced users and review those skills for intermediate users. We’ll start with basic formulas, reviewing absolute and relative referencing, and move into a review of Math, Logic, Text and Date functions. From there, we’ll look at how data SHOULD be structured in Excel for advanced analytics, then dive deep into Excel’s most feared function: VLOOKUP.  (Yes, we are very aware that XLOOKUP exists today, but trust us – if you can master VLOOKUP, you can master ANY Excel formula!) Finally, we will cap the section off by exploring some core data visualization theory, and how to apply them to Charts and conditional formats.

Armed with the skills learned early on in this course, we will be ready to unlock one of Excel’s most powerful tools: the PivotTable. We will look at how to create them, update them, and control the variety of features to show our results the way we need to see them. From grouping to controlling aggregations to slicing and dicing, you’ll acquire the skills to summarize data sets of all sizes in seconds. You’ll also learn how the data flows through a PivotTable, and its impact on security.

After learning how to quickly summarize our data, we then need to turn our focus back to Data’s dirty little secret: that it’s rarely in a good shape that is ready to use. It’s dirty, misshapen, and spread across multiple files. It’s in text files, Excel files, and databases.  It’s pivoted, transposed, and full of irrelevant records. Wouldn’t it be awesome if there was a built-in tool that allowed you to deal with all of those issues? Wouldn’t it be even more amazing if it recorded the data cleanup steps you wrote so that you could update it with a single click when the data changes? There is. It’s called Power Query. It’s built-in to Excel 2016 and higher and happens to be the focus of the next part of our workshop.

In addition to PivotTables, our boot camp will teach you how to access Power Query, how the tool works and the impact that it will have on your life. You’ll start by exploring how to append (stack) data sets in the same file, then expand this to appending data from multiple files into a single data set for analysis. We’ll cap this day by appending an entire folder full of files all at once… pure magic at work!

Lastly we will continue Power Query skill development. This section will show you 9 different ways to merge tables together, no matter where they come from. We’ll look at reshaping tabular data, pivoting and unpivoting it as needed. We will group data with ease, add conditional logic and even use the built-in toolset to build formulas by simply typing out the result we want, rather than the logic.

Who this course is for?

This training is targeted at users who:

  • Have been working with Excel at a basic level/intermediate level, 
  • Possess basic formula skills, and
  • Have experience with and/or routinely perform manual data cleanup in order to get data ready for analysis.

  • Basic skills with IF() statements, PivotTables and Power Query are assets, but not required.

Course Structure

This course is a a hybrid format of pre-recorded video training interspersed with live online Q&A and coaching with Ken Puls, for direct help with your questions. The Boot Camp is broken down into two main sections:

Section 1: Course Curriculum

The main course curriculum includes 31 hours of training. The materials are delivered in a mixture of pre-recorded video training and hands-on practice over a 7-week semester with a weekly time commitment of approximately 2.5 hours per week. (Schedule included below.) In addition, attendees will also have access to live 30-minute weekly Q&A sessions to ask questions related to the course material, for a total time estimate of 3 hours per semester.

We understand that sometimes work can get in the way of the ability to attend live sessions.  For this reason questions for the Q&A sessions may be submitted in advance, and all of the live Q&A sessions will be recorded and shared with participants. Thus, you will still be able to learn from the discussions even if you are not able to join Ken live (due to time zone conflicts, other commitments, etc.) or get called away for part of the session. This also allows you to watch the recordings on your own schedule, and to pause, rewind, and practice more with example files.

In addition, since your purchase (or renewal) provides access to the course for an entire year (from the start date of your initial semester), you are able to join any later semester as long as your subscription is active!  That’s right – in the case that life knocks you offline, the next semester is open for you to either restart or pick up where you left off.

Section 2: Long Term Support

In addition to the main course curriculum, this course also includes access to monthly “Ask Me Anything” (AMA) sessions. Each of these optional sessions is up to 2 hours in length, conducted live, and recorded for later viewing. This is a great way to receive continued coaching on a longer-term basis, as you apply your skills in the real world and run into real world problems.

As with all of our training courses, we also provide access to our curated Skillwave Support Forum as well, allowing you to get answers on a quicker basis should you need more immediate help.

If you are not able to join us live for the Q&A or AMA’s (please see below for schedules), you can still submit your questions to Ken beforehand and he will answer them in the session. You will then be able to watch the answer later on in the session recording.

Total Training Hours

The core of this course contains 31 hours of training, based on: 

  • 13h video
  • approximately 5h of practice time with the example files
  • 3h of Q&A sessions
  • approximately 10h of AMA sessions (based on attending/watching half of the AMA’s)

If registrants attend (or watch the recordings of) all 24 hours of the optional AMA sessions, this course includes up to 45 hours of training in total.

Course Schedule

Note: Registration for the current semester is OPEN.

Kick-off dates for future semesters can be found below. 

Q&A and AMA calls alternate between morning and afternoon (Pacific Time), in order to allow students from different time zones the opportunity to join the sessions in person.  With the exception of the kick-off calls, all calls are recorded, and students are able to submit questions in advance.

Also, you may join any of the Q&A sessions for as long as you are subscribed to the course, even those after your initial enrollment semester.

DateTimeFormat
2022-08-199:00 AM – 9:30 AM PDT
(4:00 PM – 4:30 PM UTC)
Kick-off Call
– Option 1
2022-08-194:30 PM – 5:00 PM PDT
(11:30 PM – 12:00 AM UTC)
Kick-off Call
– Option 2
2022-08-249:00 AM – 9:30 AM PDT
(4:00 PM – 4:30 PM UTC)
Live Q&A
2022-08-314:00 PM – 4:30 PM PDT
(11:00 PM – 11:30 PM UTC)
Live Q&A
2022-09-079:00 AM – 11:00 AM PDT
(4:00 PM – 4:00 PM UTC)
Live Q&A (+AMA)
2022-09-14Catch-up and practice week 
2022-09-219:00 AM – 9:30 AM PDT
(4:00 PM – 4:30 PM UTC)
Live Q&A
2022-09-284:00 PM – 4:30 PM PDT
(11:00 PM – 11:30 PM UTC)
Live Q&A
2022-10-059:00 AM – 9:30 AM PDT
(4:00 PM – 4:30 PM UTC)
Live Q&A

Ask Me Anything Sessions

You may join any of the AMA sessions for as long as you are subscribed to the course, even those after your initial enrollment semester.

Date Time Format
2022-09-07 9:00 AM – 11:00 AM PDT (4:00 PM – 6:00 PM UTC) Live AMA
2022-10-12 3:00 PM – 5:00 PM PDT
(10:00 PM – 12:00 AM UTC)
Live AMA
2022-11-09 9:00 AM – 11:00 AM PST
(4:00 PM – 6:00 PM UTC)
Live AMA
2022-12-15 3:00 PM – 5:00 PM PST
(11:00 PM – 1:00 AM UTC)
Live AMA
2023 Monthly 2023 AMA dates will be announced before the end of 2022 Live AMA

Future Semester Kick-off Dates

If registration for the current semester is already closed, you may add your name to the interest list for one of our future semesters.

Date Time Format
2023 2023 semester dates will be announced before the end of 2022

Bonus Gifts with this Course

Our handy Power Query Recipes collection, including all new and updated cards for as long as you are subscribed

Access to recordings of past Q&A and AMA sessions for as long as you are subscribed

Access to all future semester Q&A and AMA sessions for as long as you are subscribed

Course Update Log

First release of the course.

Power Query Recipes Updates:

  • Added a new recipe to section 20 – Reshaping Data:
    • Pivoting Vertical Sets (Recipe 20.130)
  • Updated existing recipes:
    • Pivoting Stacked Data (Recipe 20.120) – name of the “Integer-Division” column updated to reflect new wording (was formerly “Integer-Divide”)
    • Allocate over x Years from Start Date (Recipe 65.320) – the formula logic for the first period was corrected
    • Random Numbers (Recipe 70.100) – note added that the recipe is now much easier with versions of Power Query released after October 2021

Power Query Recipes Updates:

  • Added a new recipe to section 00 – Best Practice Architectures:
    • Quickly Setting Data Type of All Columns (Recipe 0.550.2203)
  • Added a new recipe to section 70 – Miscellaneous:
    • Choosing Columns vs Removing Columns (Recipe 70.130.2203)
  • Updated existing recipe in section 30 – Combining Data:
    • Cartesian Products (Recipe 30.120.2203)

Power Query Recipes Updates:

  • Added a new recipe to section 20 – Reshaping Data:
    • Shift Non-Aligned Columns Left (Recipe 20.160.2206)

Course Formats

On-Demand
This is an on-demand course, delivered via pre-recorded video modules that you can consume at your own pace. It includes all required sample files and practice sets.
Coached
This course includes live online coaching with our trainer, where you can ask your questions directly. During these sessions, our expert trainer will help you with any challenges you encountered while working through the on-demand portions of the course.

Course Content

Welcome to the Course!
4 Lessons
Getting Started
Course Schedule
Course Introduction
Who’s Ken
Excel Essentials
4 Lessons
Basic Navigation
Navigational Keyboard Shortcuts
Entering and Clearing Data
Useful Keyboard Shortcuts
Formula and Function Review
7 Lessons
Introduction to Formulas
Unlocking Excel’s Formula Power
Anatomy of a Function
Mathematical and Statistical Functions
Logical Functions
Text Parsing Functions
Working with Dates
Structuring Data in Excel
3 Lessons
Structuring Data in Excel
Excel Tables
Benefits of Excel Tables
VLOOKUP Deep Dive
7 Lessons
What is VLOOKUP
VLOOKUP Approximate Match
VLOOKUP Exact Match
Understanding VLOOKUP Matches
Common VLOOKUP Issues
Text vs Number Conversions
Guarding Against NA Results
Data Visualization
10 Lessons
Conditional Formatting
Sparklines
Chart Types
Five Rules of Effective Charting
Effective Pie Charts
Effective Bar Charts
Effective Column Charts
Effective Line Charts
Practice Lab – Lucky Acres Farm
Practice Solution – Lucky Acres Farm
From Ad-Hoc to Tabular
5 Lessons
Introduction to PivotTables
Creating PivotTables
Understanding PivotTable Mechanics
Formatting PivotTables
PivotTable Design Options
Intermediate PivotTable Skills
7 Lessons
Grouping Data in PivotTables
Sorting Data in PivotTables
PivotTable Aggregations
PivotTable Calculation Options
Filtering PivotTables
Custom Calculations in PivotTables
PivotTable Dangers
The Trouble with Data
5 Lessons
Building Modern BI Solutions
The Trouble with Data
Classic Excel Cleanup
Power Query Adds ETL to Excel
Dealing with Data in Modern Excel
Basic Importing with Power Query
4 Lessons
Accessing Power Query
How Systems Import Dates
Importing a CSV File
Update Options
Reshaping Tabular Data
10 Lessons
Basic Append Operations
Unpivot With Ease
Unpivot Subcategorized Tables
Pivoting Stacked Data
Pivoting Multi Column Vertical Pairs
Pivoting Multi Column Horizontal Pairs
Data Grouping Recipe
Ordinal Ranking
Standard Competition Ranking
Dense Ranking
Merging Data
4 Lessons
Merging Tables with Power Query
7 Ways to Join Your Data
Cartesian Products
Merging with an Approximate Match
Conditional Logic
3 Lessons
Creating Conditional Logic
Conditional Logic – Advanced
Columns From Example
Advanced Append Operations
8 Lessons
Append all Tables in the Current Workbook
Importing a Folder of Files – Overview
Importing a Folder of Files – Step 0
Importing a Folder of Files – Step 1
Importing a Folder of Files – Step 2
Importing a Folder of Files – Step 3
Importing a Folder of Files – Step 4
Update Options Revisited
Practical Skill Application
7 Lessons
Benford Analysis – Extracting First Number
Benford Analysis – Calculating Distribution Percentage
Benford Analysis – Create Benford Table
Benford Analysis – Create Comparison Table
Benford Analysis – Visualize the Results
Benford Analysis – Applying to Other Data Sets
Practice Lab – Cudaso Products
Help Resources
6 Lessons
Free Help Forums
Free Blogs
M is for (Data) Monkey
Power Query Recipes
Power Query Academy
Free Newsletters
Rate the Course
1 Lesson
Course Feedback
Q&A & AMA Submissions
2 Lessons
Q&A Submissions
AMA Submissions
Q&A & AMA Recording Archive
3 Lessons
Q&A and AMA Index
Q&A Session Recordings
AMA Session Recordings

How subscription works?

This course is built on an annual subscription format. Your registration comes with 12 months of access to the course materials, so you can refer back to the videos and example files whenever you like. Your subscription also gives you access to our private Discussion Forum, where you can ask questions related to the course materials, as well as how to apply those teachings to your own work. This forum is an invaluable resource that also allows students to practice their own learnings by helping others with those questions.

On your annual renewal date, your subscription will be automatically renewed with the credit card information on file, unless you cancel your subscription beforehand. You will be emailed a renewal reminder about one month before the renewal date. Please see the FAQs below for information about subscription renewals and cancellations.

About the author

Ken Puls

Microsoft MVP, FCPA, FCMA
Ken Puls is one of the founding partners of Skillwave.Training, and the President and Chief Training Officer of Excelguru Consulting Inc. in Canada. He is a Chartered Professional Accountant, blogger, author, and trainer with over 20 years of business and IT experience. His passion lies in exploring tools to turn data into information, and teaching others how to use them. Ken is a Microsoft MVP and a leading expert in Excel, Power Query, and Power BI.

FAQ

How do I access the course materials? Are they downloadable?
The videos are streamed on-demand and may be watched as often as you like. However, they are not downloadable. The example files and handouts for each lesson are downloadable.
Do I have to wait until the semester start date before I begin watching the videos?
No, you can begin watching the videos and using the example files as soon as you are registered. You do not have to wait for the kick-off call. Additionally, your subscription will be extended to one year from the semester start date, not the date of purchase.
Where are the course videos hosted?
All the videos for the course are hosted on Vimeo. Please note that some networks block access to Vimeo, in which case the videos will not be watchable. You may want to double-check that your network allows access to Vimeo before registering in the Academy, to ensure that you will be able to see the videos.
What support is included with the course? How do I ask questions?
All users with a current course subscription receive access to our private Discussion Forum, where they can ask questions related to the course materials, as well as how to apply those teachings to their own work. This forum is also intended to act as a place for students to practice their own learnings by helping others with those questions. While the focus of this forum is primarily aimed at allowing students to learn by helping each other, they are monitored by our course authors as well. For technical issues related to the site, course access, or other questions regarding your account, please get in touch through our Contact Us page.
How do I submit questions for the AMA sessions?
We recommend you may submit your questions (and upload any support files) at least 24 hours before the AMA via the AMA Submissions lesson. If you are not able to join the AMA session live, you can still submit your question and watch the session recording later.
Will there be recordings available of the live Q&A and AMA sessions?
The Q&A sessions will not be recorded, but the AMA session will be recorded. After the AMA session, the recording will be uploaded to the AMA Recordings lesson so you can watch them whenever you like.
Can my corporate groups get private AMA sessions?
Yes, if you have a group of 15 or more people registered in the course, we are happy to offer one private AMA session in addition to the regular AMA sessions open to all students. This will allow the instructor to help you directly with questions on your organization's data, instead of having to provide a cleansed sample data set that is safe to be viewed by others.
How do I receive my Certificate of Completion?
After all the modules and lessons have been completed, you will be able to download the Certificate of Completion from the My Courses area of your Account Dashboard.
How do I renew my subscription?
Our system will automatically renew your subscription for another year on your expiry date using the billing information on file. About a month (4 weeks) before your current expiry date, you will receive an email from us reminding you that the renewal is coming up and to confirm your billing details, so your access is uninterrupted.
How do I cancel my subscription?
You may cancel your subscription at any time, and you will continue to have access to all the course materials until your expiry date. To cancel your subscription, go to the Subscriptions of your Account Dashboard and select the subscription to cancel. After your subscription expires, you will no longer have access to the course materials.
What payment methods do you accept? What currency is used?
We accept credit card payments through our secure online payment portal, Stripe. If you would like to discuss other payment options, please get in touch through our Contact Us page. All transactions are charged in US Dollars
What is your refund policy for this course?
We think you will love our training; however, if you are unsatisfied with the training for any reason, we offer a 14-day 100% money-back guarantee, as long as you have completed less than 25% of the course. All we ask in return is that you tell us why you were unsatisfied with the training. We do not offer refunds if you have completed over 25% of the course and/or it is more than 14 days after the purchase date.
Can an account be shared with or transferred to another user?
Logins, downloads, and other materials are for the exclusive use of the registered user only. Sharing of credentials is in violation of our site policies and may result in termination of product and/or site access without notice.

Reviews

Excel Fundamentals Boot Camp

0
(0 Review)
5
4
3
2
1

Reviews

There are no reviews yet.

Only verified customers who have purchased this product may leave a review.