The Complete Excel Course with AI Integration

How to write a macro in Excel that checks your data

You check the same sheets by hand; a macro you wrote yourself does it in one click. In The Complete Excel Course with AI Integration you write it as FormatWizard: a recording that formats a problem row in one keystroke grows into VBA that walks every row of a sheet, writes what is wrong in a flag column and colours the cell at fault. It is industry standard, designed after talking with people from many different industries; a starter workbook gets you going, and every line is yours to change, in Excel alone.

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:

  • Record a macro that formats a problem row, then switch on relative references so one shortcut formats whichever row you select.
  • Run your macros from the Developer tab and from a button of your own on the Quick Access Toolbar, and keep them safe in an .xlsm file.
  • Write VBA in a blank module: Option Explicit, variables and their data types, a Sub that does the work and a Function that hands back the last row of data.
  • Loop through every row with For Next, flag blank cells and text where a number belongs, and colour each problem cell with RGB.
  • Clear the last run's flags first, then join every check in one master macro protected by an error handler.
  • See each step of your code print to the Immediate window with Debug.Print, and fix the compile errors Option Explicit brings to light.

Projects

What you will build.

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

  • Project 11

    FormatWizard

    One click that checks a whole sheet for you.

    The build on this page, from its starter workbook: sales rows with a flag column, swept for blanks and figures stored as text, each fault written up and lit up right where it sits.

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

  • Project 02

    Dunder Michael Paper Company employee database

    Where data is first cleaned and guarded by hand: TRIM strips stray spaces and a pick-list refuses a wrong department, so you know what your macro hunts for when it sweeps a sheet.

  • Project 03

    Sales Goal Tracker

    A sales targets workbook in which COUNT and COUNTA first test whether a column is complete and truly numeric, the very questions FormatWizard asks of every row.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 24 of them, 1 h 49 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 if IF and COUNTA already feel familiar: the FormatWizard starter workbook is downloaded there, and the macro grows lesson by lesson until Lesson 152 runs every check at once. New to formulas? Begin at Lesson 10, or right at the start, Lesson 1, if you have never opened Excel. With the macro done, AutoFlow takes automation outside the workbook with Power Automate Desktop flows, on Windows, from Lesson 174.

24 of 186 lessons · 1 h 49 min

01 Microsoft Excel fundamentals: building a foundation from scratch 1 of 13 lessons · 3 min A tax rate locked in place while the amounts beside it move: relative, absolute and mixed references, the same choice the macro recorder offers between a fixed row and whichever row you select.
  1. 10 Relative vs absolute references in Excel Dollar signs and mixed references 3 min
04 Essential formulas and functions (IF, SUMIF and more) 2 of 13 lessons · 8 min Marks set against a pass mark give TRUE or FALSE, and IF turns each into a pass or a fail on a school sheet: the same yes-or-no decision every If in your macro takes, one row at a time.
  1. 28 Logical functions in Excel TRUE and FALSE comparisons for beginners 5 min
  2. 29 The IF function in Excel Pass or fail results with a single condition 3 min
04 Essential formulas and functions (IF, SUMIF and more) 2 of 13 lessons · 11 min SUM, COUNT and COUNTA on sales records: COUNT skips anything that is not a number, so a blank or a figure stored as text shows up at once, and COUNTA counts every filled cell, the formula form of your macro's checks.
  1. 34 SUM, COUNT and COUNTA in Excel SUM with FILTER and counting numbers with COUNT 7 min
  2. 35 SUM, COUNT and COUNTA in Excel COUNTA, unique counts and completeness checks 4 min
05 Text functions and data validation (enhancing data quality) 1 of 7 lessons · 6 min TRIM, UPPER, LOWER and PROPER on an employee list: invisible spaces stripped from job titles, emails and locations, and why such a space breaks formulas and filters, the trap your macro avoids with VBA's own Trim.
  1. 42 TRIM, UPPER, LOWER and PROPER in Excel Clean messy text and fix capitalisation 6 min
05 Text functions and data validation (enhancing data quality) 1 of 7 lessons · 4 min Data validation that turns the department column into a pick-list drawn from its own sheet, so a mistyped name is refused at entry: prevention, where the macro is the sweep that follows.
  1. 45 Data validation in Excel Create drop-down lists for consistent data entry 4 min
14 VBA and macros in Excel: FormatWizard 17 lessons · 1 h 16 min FormatWizard from first recording to finished tool: a macro recorded fixed and then relative, run from the ribbon or a toolbar icon and kept in an .xlsm file; the editor, its trust settings and a first line of code; then variables, GetLastUsedRow, a clean-up routine, row-by-row checks gathered in one flag, light red highlights, IsNumeric, Debug.Print, and RunFullValidation with its error handler.
  1. 136 Start here Excel macros and VBA for beginners What macros are, the FormatWizard project 3 min
  2. 137 Record your first Excel macro Name, run and use absolute references 6 min
  3. 138 Relative references in Excel macros Make recorded macros dynamic 4 min
  4. 139 Developer Tab in Excel Run macros from a button and the quick access toolbar 4 min
  5. 140 Save macros in Excel Macro-enabled workbooks and the XLSM file format 3 min
  6. 141 The VBA Editor in Excel Open it with Alt+F11 and learn the workspace 4 min
  7. 142 Working in the VBA editor Macro security, trust settings, switching to Excel 6 min
  8. 143 Your first VBA code Modules, worksheets, rows, columns and formatting 4 min
  9. 144 VBA variables and data types Sub vs function for Excel beginners 6 min
  10. 145 VBA functions in Excel Build GetLastUsedRow with find and exit function 5 min
  11. 146 Clear old flags with VBA Reset messages and fill colours with if statements 5 min
  12. 147 For Next loop in VBA Flag missing region values in Excel 4 min
  13. 148 VBA If Else and loops Check missing sales data and build a FlagText message 5 min
  14. 149 Highlight cells with VBA Interior.Color and RGB for missing data 5 min
  15. 150 IsNumeric in VBA Validate unit price and amount data types in Excel 7 min
  16. 151 Debugging VBA for beginners Debug.Print and the Immediate window 5 min
  17. 152 Complete Excel validation macro One VBA subroutine with an error handler 3 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.

Where can I learn to write an Excel macro that checks my data?

In The Complete Excel Course with AI Integration from Coding Liquids, through FormatWizard, an industry-standard macro you write line by line. Starting from a workbook of sales data, you record your first macro, then write VBA that walks every row, writes Region missing, Salesperson missing or Units invalid in a flag column, colours the cell at fault and runs every check in one click behind an error handler. The finished macro-enabled workbook sits beside yours for comparison. The 24 lessons picked out here come to 1 hour 49 minutes of video; lifetime access keeps them open for every new check your sheets call for, and a Certificate of Completion is yours once you complete the course.

How do I write a macro in Excel if I have never written one?

Record one first, the way The Complete Excel Course with AI Integration opens FormatWizard. In Lesson 137 you name a macro, give it a Ctrl shortcut and record bold, italic and a fill on a row whose amount is negative; replay it and the row is formatted in one keystroke. Lesson 138 switches on Use Relative References so the same macro works on whichever row you select, and Lesson 140 saves it as a macro-enabled .xlsm file, because a plain .xlsx throws the code away. Then Lesson 141 opens the VBA editor on the code Excel wrote for you. With the instructor support plan, a question on any of these lessons goes to the instructor, for two months from the day you switch it on.

How do I create a macro in Excel without recording it?

Type it into a module of your own, which The Complete Excel Course with AI Integration has you do from Lesson 143: press Alt+F11, insert a module and point VBA at the Data sheet with Set and ThisWorkbook.Worksheets. Lesson 144 adds Option Explicit, so a mistyped name is caught before it causes harm, then Dim for each variable and a Sub that does the work. Lesson 145 writes GetLastUsedRow, a Function that searches up from the foot of the sheet with Find and hands back the last row, so your macro follows the data however far it grows.

How do I check if a cell is empty with VBA?

Loop down the rows and test each cell with its spaces trimmed, as FormatWizard does in The Complete Excel Course with AI Integration. Lesson 147 runs For r = 2 to the last row, reads the Region cell of row r through Trim, and writes Region missing in the flag column wherever nothing is left. Lesson 148 adds the Salesperson column and a FlagText variable that gathers every fault in a row, joined by a pipe, and Lesson 149 colours the empty cell itself light red with Interior.Color and RGB. The same few lines check any column you choose.

How do I check the data type of a cell in VBA?

With IsNumeric, which FormatWizard in The Complete Excel Course with AI Integration uses to catch a figure that looks right but is stored as text (Lesson 150). Inside the loop it asks whether each row's per-unit figure can be read as a number; where the answer is False, the macro colours that cell and its flag cell light red and writes Units invalid, so a wrong value never reads like a missing one. COUNT made the same test by formula in Lesson 34, where text in a number column simply goes uncounted; the macro runs it row by row and marks the culprit.

How do I run a macro from a button in Excel?

Give it a button of its own on the Quick Access Toolbar, as The Complete Excel Course with AI Integration shows in Lesson 139: switch on the Developer tab under File, Options, Customize Ribbon, then in More Commands choose Macros, add yours and pick an icon with Modify. One click then runs it on the row you are on. The Developer tab's Macros list and a Ctrl shortcut are the other ways in, and Lesson 137 explains the Personal Macro Workbook, which keeps a recorded macro ready whenever Excel opens on your computer instead of inside one file.

How do I clear old results before a macro runs again?

Write a clean-up routine and call it first, the order FormatWizard keeps in The Complete Excel Course with AI Integration. Lesson 146 stops with a warning if the sheet holds only its header, then empties the flag column with ClearContents and removes its fill by setting Interior.Pattern to xlNone, from row 2 to the last used row. Lesson 152 calls that routine ahead of the checks inside RunFullValidation, turns screen updating off while they run and sends any error to a handler that shows Err.Description in plain words, so every run starts from a clean column.

Should I use data validation or a macro to keep data clean?

Use both, since The Complete Excel Course with AI Integration teaches each for its own moment. A pick-list set up with data validation refuses a wrong entry as it is typed (Lesson 45); FormatWizard's macro sweeps the rows already there, blanks and figures stored as text included, and marks each one in a single run (Lesson 152). Want the macro to set up the drop-down lists as well? Switch on the recorder from Lesson 137, add a list once by hand and read the VBA Excel writes for it in the editor of Lesson 141: recording is how the course first shows you code.

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.