Back to courses

Advanced Excel Bootcamp: Data Analytics and Business Intelligence

The only course you need to launch your career as a Data Professional! Learn to master Excel's built-in power tools, including Power Query, Power Pivot Tables, Data Modeling, the DAX formula language, and so much more. Become a Business Intelligence Analyst and get hired in 2024.

15 Days

Average time students take to complete this course.

instructor
Taught by: Travis Cuzick
Last updated: March 2024

Course overview

We guarantee you that this is the best and most up-to-date Advanced Excel Bootcamp course that you can find. You'll learn to use the advanced Excel 'power' tools including Power Query, Power Pivot Tables, and the DAX formula language. But more importantly you'll build an end-to-end Business Intelligence solution for a fictional Surf Shop where you'll create dozens of KPIs that provide deep insight into business performance. By the end, you'll have the skills to get hired as a Data or Business Intelligence Analyst in 2024!

What you'll learn

  • Become a top 10% Business Intelligence Analyst or Data Analyst to enhance your skillset and career opportunities
  • Build an end-to-end Business Intelligence solution for a fictional surf shop, including dozens of powerful KPIs that provide deep insight into business performance
  • Use Power Query to pull external datasets into Excel, and transform them into whatever shape is needed for data analysis
  • Master the art and science of Data Modeling in Excel, connecting relational datasets from Power Query so they can be analyzed together
  • Build 'Power' Pivot Tables to rapidly extract insights from the Excel Data Model, whether your data tables have 10 rows or 10 million
  • Proficiently apply the DAX formula language, to inject sophisticated calculations and KPIs into Pivot Tables
  • Polish calculations into professional presentations, with data visualization tools like Pivot Charts and conditional formatting
  • Use Excel's powerful charting engine to tell the story of your data with attractive data visualizations
  • Learn how to build Excel formulas for data analysis, data science, data entry, and everyday office use
  • Leverage Slicers and Timelines to engineer dynamic dashboards that maximize the amount of information you can present on a single screen
  • Learn how to use Microsoft Excel to supplement your Power BI skills
  • Loads of exercises to practice and perfect your skills
  • Learn why Excel is one of the most popular pieces of software across all industries
  • Master Excel's built-in 'power' tools and see how they're nearly identical to those found in Microsoft's flagship Business Intelligence tool, Power BI

Why learn Excel for Business Intelligence?

Business Intelligence, commonly referred to as "BI", is one of the fastest growing and most in-demand fields in technology. BI is a broad term that encompasses data mining, data analysis, performance benchmarking using KPIs (Key Performance Indicators), and descriptive analytics.

Meanwhile, Excel is one of the most powerful applications ever built, with a massive and widely under-appreciated feature set. It has the horsepower and flexibility to play a crucial role in everyone's toolbox.

Together? Well that's where you can make magic happen. And that magic is what this Excel Business Intelligence Bootcamp teaches you.

Just because Excel is seemingly on everybody’s desktop does not mean that its BI tools are pedestrian.

To the contrary, Excel’s built-in “power” tools - such as Power Query for ETL (extract, transform, load) operations, the Excel Data Model for managing relational data (like that stored in relational databases), and the DAX formula language for advanced data analysis - are nearly identical to those found in Microsoft’s flagship Business Intelligence tool, Power BI.

And Excel’s extensive suite of charting and visualization tools, long considered one of its most powerful features, can translate the results of any data analysis into visually compelling and meaningful presentations that will 'wow' your colleagues and boss.

Why this Excel BI course?

This course is focused on efficiency.

So you never have to waste your time on confusing, out-of-date, incomplete tutorials anymore.

Instead, you'll learn everything from the basics of Business Intelligence to using the most advanced 'power' features Excel has to offer. All in one course.

And you'll be learning Excel BI in a fun and supportive environment.

By enrolling today, you’ll also get to join our exclusive live online community classroom to learn alongside thousands of students, alumni, mentors, TAs and Instructors.

Most importantly, you will be learning from an industry expert that has actual real-world experience working as a Data Solutions Engineer on massive datasets for some of the largest companies.

Here is what the course will cover to take you from Zero to Mastery:

The curriculum is presented in basic building blocks so that you can build your knowledge step-by-step.

We start from the very beginning by teaching you the fundamentals before quickly ramping up to advanced topics.

Here's a section by section breakdown:

1. POWER QUERY - A feature-packed yet easy-to-use tool for extracting, transforming, and loading ("ETL" for short) data from just about any source you can imagine into Excel.

From text files to databases, wherever the data you want to analyze might be stored, you can use Power Query to pull it into Excel and then transform it however you need to support your analysis.

2. POWER PIVOT TABLES & THE EXCEL DATA MODEL - You'll be introduced to one of our favorite parts of Excel: Power Pivot Tables.

A Pivot Table is a table of grouped values that aggregates the individual items of a more extensive table within one or more discrete categories (such as sums, averages, or other statistics), which the Pivot Table groups together using a chosen aggregation function applied to the grouped values. But wait, where's the power?

You'll turn your Pivot Tables into “Power” Pivot Tables by connecting them to the Excel Data Model, a relational database that lives right inside Excel. The Excel Data Model gives you the ability to mash up and analyze multiple datasets in a single Pivot Table, no VLOOKUPS required.

And unlike how “traditional” Excel starts to sputter out once you try playing around with more than a couple hundred thousand rows of data, Power Pivot lets you work with up to hundreds of millions of records in a single Excel file... Now that's power!

3. DAX PROGRAMMING LANGUAGE - Learn my favorite subject all the way from scratch to advanced: DAX (Data Analysis Expressions), a powerful programming language built right into Excel.

DAX allows you to incorporate complex portable calculations called "measures" into Pivot Tables. It might best be described as "Excel formulas on steroids".

Sound complex? It doesn't have to be.

Since the basics of DAX syntax are easy to pick up if you’re already familiar with Excel formulas, the course also ventures into advanced topics like Time Intelligence, Iterator Functions, and Variables (yes, variables in Excel formulas!).

That's right, we'll break it down step-by-step so you learn exactly how powerful the DAX programming language is and how you can master its magic.

4. DATA VISUALIZATION - Learn to tell a story with your data using a series of powerful visualization techniques - from conditional formatting to Pivot Charts - that can transform calculations into insights that can be used to make real-world decisions.

Data visualization is an important piece of Business Intelligence.

As humans we're often visual learners - we can't just look at a spreadsheet of numbers, we need to visualize it in order to separate the signal from the noise. Here you will learn how to do just that, so you can tell a persuasive story with your data that will influence how you (and your clients or boss!) make decisions.

Excel’s extensive suite of charting and visualization tools, long considered one of its most powerful features, can translate the results of any data analysis into a visually compelling and meaningful presentation for you. Even James Bond would've benefited from using data visualization to help communicate his intelligence reports!

5. DYNAMIC DASHBOARDS USING SLICERS AND TIMELINES - Plus you'll learn the techniques for building dynamic dashboards in Excel using Slicers and Timelines to not only filter charts, but actually change the metrics displayed in them.

What's the bottom line?

This course is not about making you just watch along so that when you are done with the course you don’t know what to do other than watch another tutorial... No!

This course will push you and challenge you to go from an absolute beginner in Business Intelligence and turn you into a Business Intelligence Analyst using Excel 💪.

How do we know?

Because thousands of Zero To Mastery graduates have gotten hired and are now working at companies like Google, Tesla, Amazon, Apple, IBM, JP Morgan, Facebook, Shopify + other top tech companies.

They come from all different backgrounds, ages, and experiences. Many even started as complete beginners.

So there's no reason it can't be you too.

And you have nothing to lose. Because you can start learning right now and if this course isn't everything you expected, we'll refund you 100% within 30 days. No hassles and no questions asked.

When's the best time to get started? Today!

There's never a bad time to learn in-demand skills. But the sooner, the better. So start learning Excel for Business Intelligence today by joining the ZTM Academy. You'll have a clear roadmap to developing the skills to build your own projects, get hired, and advance your career.

Join Zero To Mastery Now

What you'll build

The best way you learn is by doing. Not just watching endless tutorials. That's why a key part of this course is the exercises you'll complete and the massive real-world project that you'll get to build. Plus it'll look great on your portfolio.

Surf Shop: End-to-End Business Intelligence Solution

Surf Shop: End-to-End Business Intelligence Solution

You'll build an end-to-end BI solution for a fictional surf shop that utilizes an automated ETL (extract, transform, and load) process to fetch external data, the DAX language to create dozens of powerful KPIs, and culminates in dynamic visualizations.

Join Zero To Mastery Now

Course curriculum

To make sure this course is a good fit for you, you can start learning Excel for Business Intelligence for free right now by clicking any of the PREVIEW links below.

Introduction

4 lectures

Advanced Excel Bootcamp: Data Analytics and Business Intelligence2:40

PREVIEW

Exercise: Meet Your Classmates and Instructor

PREVIEW

Understanding Your Video Player (notes, video speed, subtitles + more)

PREVIEW

Set Your Learning Streak Goal

PREVIEW

Section 1: Power Query for Extract, Transform and Load (ETL) operations

12 lectures

Section Resources

PREVIEW

Introducing Power Query10:55

PREVIEW

Applying Basic Transformations12:13

BEGIN

Editing Transformations8:46

BEGIN

Numerical Transformations8:43

BEGIN

Text Transformations7:45

BEGIN

Date Transformations - Building a Calendar Table8:53

BEGIN

Conditional Transformations7:09

BEGIN

Merging Datasets11:34

BEGIN

Appending Datasets8:56

BEGIN

Exercises

BEGIN

Let's Have Some Fun (+ Free Resources)

BEGIN

Section 2: The Excel Data Model

12 lectures

Section Resources

BEGIN

Introducing Excel's Data Model8:56

BEGIN

Putting the Data Model to Work13:08

BEGIN

Relationships10:29

BEGIN

Relationships in The Excel Data Model15:14

BEGIN

Power Pivot Tables13:05

BEGIN

Calendar Tables6:47

BEGIN

Exercise: Imposter Syndrome2:55

BEGIN

Power Pivot Pitfalls8:05

BEGIN

Multiple Data Tables11:22

BEGIN

Exercises

BEGIN

Unlimited Updates

BEGIN

Section 3: Analyzing the Data Model With DAX

17 lectures

Calculated Columns With DAX10:22

BEGIN

Project Exercise - Calculated Columns with DAX

BEGIN

The IF Function in DAX11:35

BEGIN

Project Exercise - The IF Function in DAX

BEGIN

The SWITCH Function15:10

BEGIN

Project Exercise - The SWITCH Function

BEGIN

The RELATED Function9:44

BEGIN

Project Exercise - The RELATED Functions

BEGIN

Introducing Measures12:07

BEGIN

Project Exercise - Introducing Measures

BEGIN

Using DAX Functions in Measures10:39

BEGIN

Project Exercise - Using DAX Functions in Measures

BEGIN

The CALCULATE Function13:33

BEGIN

Project Exercise - The CALCULATE Function

BEGIN

The ALL Function10:48

BEGIN

Project Exercise - The ALL Function

BEGIN

Course Check-In

BEGIN

Section 4: Advanced DAX

16 lectures

Introducing Time Intelligence Functions17:32

BEGIN

The ISBLANK Function4:42

BEGIN

Project Exercise - The ISBLANK Function

BEGIN

The DATEADD Function10:40

BEGIN

Project Exercise - The DATEADD Function

BEGIN

The DATESINPERIOD Function13:01

BEGIN

Project Exercise - The DATEINPERIOD Function

BEGIN

Variables in DAX10:33

BEGIN

Project Exercise - Variables in DAX

BEGIN

The X Functions8:42

BEGIN

Project Exercise - The X Functions

BEGIN

The RANKX Function9:25

BEGIN

Project Exercise - The RANKX Function

BEGIN

The TOPN Function9:45

BEGIN

Project Exercise - The TOPN Function

BEGIN

Implement a New Life System

BEGIN

Section 5: Data Visualization Techniques

10 lectures

Pivot Table Formatting Tips8:52

BEGIN

Conditional Formating: Data Bars, Color Scales, and Icon Sets15:02

BEGIN

Custom Conditional Formats10:11

BEGIN

Introducing Pivot Charts14:58

BEGIN

Column Charts6:06

BEGIN

Line Charts, Area Charts, and Timelines11:43

BEGIN

Combo Charts5:49

BEGIN

Changing Measures With Slicers13:28

BEGIN

Using Pivot Tables as Formulas17:18

BEGIN

Project Exercise - Data Visualization Techniques

BEGIN

Where To Go From Here?

6 lectures

Thank You!1:17

BEGIN

Review This Course!

BEGIN

Become An Alumni

BEGIN

Learning Guideline

BEGIN

ZTM Events Every Month

BEGIN

LinkedIn Endorsements

BEGIN

Meet your instructor

Your Excel Business Intelligence instructor (Travis) isn't just an expert with years of real-world professional experience. He has been in your shoes. He makes learning fun. He makes complex topics feel simple. He will motivate you. He will push you. And he will go above and beyond to help you succeed.

Travis Cuzick

Hi, I'm Travis Cuzick!

Travis has been architecting and coding data solutions for well over a decade and for some of the biggest companies on the Fortune 500. Travis aims to make the learning process as efficient, intuitive, and fun (yes, fun!) for his students as possible.

SEE MY BIO & COURSES

Travis Cuzick

Data Solutions Engineer

Frequently asked questions

Are there any prerequisites for this course?

  • Basic Excel knowledge is required, including experience with Pivot Tables. If you don't have that, our Excel Bootcamp is included with your ZTM Academy membership and will teach you everything about Excel from scratch!
  • Microsoft Excel (Excel 365 is used for demonstrations, but any version of Excel from 2016 or newer is compatible).
  • Windows 7 operating system or newer (a significant portion of advanced Excel functionality only works with Windows).

Who is this course for?

  • Both beginner and advanced data professionals looking to learn and utilize Microsoft Excel for business intelligence
  • Students who are interested in going beyond a normal "beginner" Excel bootcamp
  • Anyone who is interested in learning a tech skill that has been around since 1987, is used by ~750 million people and will be around for many years to come
  • Business Intelligence Analysts, Data Scientists, Developers, Business Analysts, Product Managers, Sales/Marketers, and anyone who uses data to make business decisions
  • Anyone who wants to work in the fields of business intelligence, data analysis, and/or data science
  • Programmers who want to accelerate their career by adding data skills to their toolkit
  • Programmers who want to learn a tool for the rapid development of data-driven applications

Do you provide a certificate of completion?

We definitely do and they are quite nice. You will also be able to add Zero To Mastery Academy to the education section of your LinkedIn profile as well.

Can I use the course projects in my portfolio?

Yes, you’d be crazy not to in our slightly biased opinion! All projects are downloadable and ready to use the minute you join.

Many of our students tell us the projects they built while following along with our courses were what got them interviews and because they built the projects themselves, they could confidently explain and walk through their work during the interview.

You know what that means? Job offer!

Are there subtitles?

Yes! We have high quality subtitles in 11 different languages: English, Spanish, French, German, Dutch, Romanian, Arabic, Hindi, Portuguese, Indonesian, and Japanese.

You can even adjust the text size, color, background and more so that the subtitles are perfect just for you!

Still have more questions about the Academy?

Still have more questions specific to the Academy membership? No problem, we answer some more here.

Invest in a better you. For less than a coffee a day.

Choose your currency:
$ USD US Dollar
Lifetime
100% OFF$999
$999
Only pay once, ever
You're serious about advancing your career and never getting left behind
Start Learning Now

MOST POPULAR

Save 40% vs. monthly (that's $189 a year)
Annual
100% OFF$279 / year
$23 / month
$279 / year
You're committed to getting hired and starting a career in tech
Start Learning Now
Monthly
100% OFF$39 / month
$39 / month
You're ready to upskill and advance your career
Start Learning Now

Every ZTM membership includes:

Unlimited access to all courses, projects + workshops, and career paths
Access to our private Discord with 400,000+ members
Access to our private LinkedIn networking group
Custom ZTM course completion certificates
Live career advice sessions with mentors, every month
Full access to all future courses, content, and features
100% RISK FREE

We know you'll love ZTM. That's why we provide a no hassle, 30-day money back guarantee.