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.
This training is targeted at users who:
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.
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:
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.
The core of this course contains 31 hours of training, based on:
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.
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.
| Date | Time | Format |
|---|---|---|
| 2022-08-19 | 9:00 AM – 9:30 AM PDT (4:00 PM – 4:30 PM UTC) | Kick-off Call – Option 1 |
| 2022-08-19 | 4:30 PM – 5:00 PM PDT (11:30 PM – 12:00 AM UTC) | Kick-off Call – Option 2 |
| 2022-08-24 | 9:00 AM – 9:30 AM PDT (4:00 PM – 4:30 PM UTC) | Live Q&A |
| 2022-08-31 | 4:00 PM – 4:30 PM PDT (11:00 PM – 11:30 PM UTC) | Live Q&A |
| 2022-09-07 | 9:00 AM – 11:00 AM PDT (4:00 PM – 4:00 PM UTC) | Live Q&A (+AMA) |
| 2022-09-14 | Catch-up and practice week | |
| 2022-09-21 | 9:00 AM – 9:30 AM PDT (4:00 PM – 4:30 PM UTC) | Live Q&A |
| 2022-09-28 | 4:00 PM – 4:30 PM PDT (11:00 PM – 11:30 PM UTC) | Live Q&A |
| 2022-10-05 | 9:00 AM – 9:30 AM PDT (4:00 PM – 4:30 PM UTC) | Live Q&A |
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 |
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 |
First release of the course.
Power Query Recipes Updates:
Power Query Recipes Updates:
Power Query Recipes Updates:
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.
Continue your learning journey with our other training offerings. We’ll help you take your skills to the next level.
Subscribe to our newsletter for
information on Power BI,
Power Query, DAX, and more,
plus details on courses, events,
and promotions.
Reviews
There are no reviews yet.