The Complete Excel Course with AI Integration

Power Query or VBA: which to automate with?

The export you clean every month and the sheet you check every week are two different jobs, and Excel has a tool for each. In The Complete Excel Course with AI Integration you build both on industry-standard projects: FormatWizard, a VBA macro that walks every row, writes what is wrong in a flag column and runs every check in one click, and SalesFusion, where Power Query records the cleaning once and replays it on the next year's file and on a whole folder. Weigh them here, then master both.

Taught by Sagnik Bhattacharya and Virginia Thorn

The Complete Excel Course with AI Integration: Sagnik Bhattacharya and Virginia Thorn

What you will be able to do

You will be able to:

  • Match each repeated task to its tool: Power Query for data that arrives again and again, VBA for acting on the sheet in front of you.
  • Write a VBA loop that checks every row and writes each fault it finds into a flag column.
  • Run every check in one click from a master macro whose error handler explains a failure in plain words.
  • Reuse a Power Query cleaning pipeline on a new year's file by changing only its source.
  • Combine every file in a folder with Power Query and add business rules as custom columns, so each new file is cleaned on the next refresh.

Projects

What you will build.

The course has 14 industry-standard projects. These two matter most here.

  • Project 11

    FormatWizard

    One click that checks a whole sheet for you.

    The VBA side of the choice: a macro that loops through every sales row, writes each fault into a flag column and runs all its checks behind an error handler, changing the sheet itself, where Power Query leaves its source untouched and loads a fresh table.

  • Project 12

    SalesFusion

    A sales report that refreshes itself from a folder of files.

    The Power Query side: a cleaning recipe recorded once on one year's sales file, reused on the next year's and stretched over a whole folder of period files, with no macro behind it.

Tap or pinch to zoom in, drag to move around. Click or scroll to zoom in, drag to move around.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 8 of them, 42 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 136 Excel macros and VBA for beginners

Start at Lesson 136 and keep to course order: VBA first, then Power Query from Lesson 154, the order in which the course hands one tool on to the other. To build either project yourself, follow its whole section: FormatWizard from Lesson 136 to Lesson 152 with its starter workbook, SalesFusion from Lesson 153 with its dataset of CSV files.

8 of 186 lessons · 42 min

14 VBA and macros in Excel: FormatWizard 1 of 17 lessons · 3 min What a macro is, a recorded sequence of actions replayed in one click, and what VBA is, the language that runs it, met on FormatWizard's sales sheet and its flag column.
  1. 136 Start here Excel macros and VBA for beginners What macros are, the FormatWizard project 3 min
14 VBA and macros in Excel: FormatWizard 2 of 17 lessons · 10 min VBA working the sheet row by row: a For Next loop writes Region missing into the flag column wherever a region is blank, then a FlagText variable, set first as a challenge, gathers every fault in a row, with parts of the code for you to write with Claude or ChatGPT and then correct.
  1. 147 For Next loop in VBA Flag missing region values in Excel 4 min
  2. 148 VBA If Else and loops Check missing sales data and build a FlagText message 5 min
14 VBA and macros in Excel: FormatWizard 1 of 17 lessons · 3 min RunFullValidation calls each check in turn behind an error handler, with screen updating off while it runs, so the whole sheet is audited in one click; the lesson closes by passing the data work on to Power Query.
  1. 152 Complete Excel validation macro One VBA subroutine with an error handler 3 min
15 SalesFusion: advanced data tools and a Power Query modelling system 1 of 21 lessons · 5 min Power Query's answer to the weekly clean-up: an ETL pipeline whose logic you set once, after which each fresh file is extracted, transformed with your saved steps and loaded by itself.
  1. 154 Introduction to Power Query in Excel The ETL workflow explained 5 min
15 SalesFusion: advanced data tools and a Power Query modelling system 1 of 21 lessons · 5 min A cleaning pipeline reused: duplicate the query, point its source at the next year's file, and every trim, replacement, type change and custom column runs again before the new year is merged with its product and region names.
  1. 164 Reuse a Power Query pipeline Duplicate queries for a new year of sales data 5 min
15 SalesFusion: advanced data tools and a Power Query modelling system 2 of 21 lessons · 16 min A folder handled as one dataset: every file combined with From Folder and cleaned once in the sample file, then custom columns typed in M, Power Query's formula language, for the discount rate, profit, margin and returns, and a conditional column that marks high-value orders.
  1. 166 From Folder in Power Query Combine every sales file in a folder automatically 9 min
  2. 167 From Folder automation in Power Query Profit metrics and fiscal year columns 7 min

Instructors

Who teaches it.

Virginia Thorn smiling at the camera, dark hair tied back, wearing a navy top.

Instructor, Excel with AI

Virginia Thorn

Emmy Award winner. Teaches Excel with AI at Coding Liquids.

Questions

Asked before starting.

Should I use Power Query or VBA to automate Excel?

Use Power Query when the repeated job is getting data in and into shape: imports, cleaning, merges, appends and whole folders of files, recorded as steps a refresh replays. Use VBA when the job acts on the workbook itself: checking every row, writing flags, colouring cells, running routines in order at a click. Plenty of workflows want both, Power Query to prepare and VBA to check. The Complete Excel Course with AI Integration from Coding Liquids teaches the two back to back, and the 8 lessons picked here (42 minutes) show each one at work.

What is the difference between Power Query M and VBA?

M describes what should happen to a table; VBA tells Excel what to do, one instruction at a time. Every step you click in Power Query is saved in M, and The Complete Excel Course with AI Integration has you type short M formulas into custom columns, from a rule that turns any discount into a decimal rate to profit and margin (Lesson 167); whole queries written by hand in the Advanced Editor are not taught. VBA is a programming language of variables, If statements and For Next loops, written in the VBA editor to walk a sheet row by row, and FormatWizard builds it into a full validation tool, loop by loop (Lesson 147).

Can Power Query replace VBA macros?

For data preparation, yes, with no code to write: The Complete Excel Course with AI Integration shows Power Query replaying a whole cleaning pipeline on the next year's file once its source is changed (Lesson 164) and taking in every file of a folder on refresh (Lesson 166). Power Query leaves its source data untouched and loads a fresh table; it does not colour cells or pop up messages. So the checks that mark faults on the sheet itself stay with VBA, as in FormatWizard's RunFullValidation (Lesson 152). Give each tool the work it was made for.

Do I need to know VBA to use Power Query?

No, and that is much of Power Query's appeal: its steps are made with ribbon buttons and menus, and Power Query writes the code behind them for you. In The Complete Excel Course with AI Integration the SalesFusion lessons ask for no VBA at all, from the ETL pipeline (Lesson 154) to a folder of files combined into one table (Lesson 166); the only typing is a short formula in a custom column. VBA has lessons of its own in FormatWizard, for the jobs a query is not built for.

Which part of a monthly report should Power Query do, and which part VBA?

Give Power Query everything before the analysis and VBA everything that acts on the finished sheet. In The Complete Excel Course with AI Integration, Power Query takes the monthly files: save a new export into the folder and the next refresh cleans it with the same steps as every file before it (Lesson 166). VBA takes the checks: one click on RunFullValidation clears old flags, marks missing regions and salespeople, writes Units invalid wherever a per-unit figure fails its check, highlights each fault and reports when it is done (Lesson 152). Built that way, the month's routine comes down to a refresh and a click.

Is Power Query easier to learn than VBA?

Power Query is quicker to start, since its steps are clicks and a first cleaning query takes no code; VBA asks more of you and gives you more control in return. The Complete Excel Course with AI Integration eases you into both. FormatWizard begins by recording a macro you replay with one shortcut (Lesson 137), opens the VBA editor on the code behind it (Lesson 142) and builds up to loops you write yourself. SalesFusion starts with what Power Query is for and how its ETL pipeline runs (Lesson 154) before a single step is clicked.

Which is easier to share with colleagues, a Power Query workbook or a VBA macro?

A Power Query workbook travels as an ordinary .xlsx file, which is how the finished SalesFusion workbook of The Complete Excel Course with AI Integration comes; keep its source files in the project folder the queries point to, because a moved file breaks the connection (Lesson 153). A macro must be saved as a macro-enabled .xlsm file, since a plain .xlsx strips the code out (Lesson 140), and whoever opens it sees macros disabled until they choose Enable Content for a file they trust (Lesson 142). Learn both sets of rules and either one travels well.

Is there one course that teaches Power Query and VBA side by side?

Yes: The Complete Excel Course with AI Integration teaches VBA in FormatWizard and Power Query in SalesFusion, back to back, both industry-standard projects designed after talking with people from many different industries. The macro section ends where Power Query begins (Lesson 152), so you meet the two as one toolkit for the work you repeat. Lifetime access keeps both sections open for the day a new chore needs one or the other, the Certificate of Completion you earn carries a verification link, and a 14-day money-back guarantee stands behind the purchase.

Excel with AI

See the whole course.

The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 industry-standard projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.

The same course

For another job, goal, level, topic or comparison.