Excel Macros: What They Are, Examples and How to Create Them with VBA | Free Excel Course: From Beginner to Advanced

What you'll learn

Record macros that repeat tasks with one click.
Read and edit the VBA code Excel generates.
Write your first macros with loops and conditions.
Know when to use macros, Power Query or another tool.
Create one PDF per client with a ten-line macro.

📥 Practice this lesson

Save a copy of the file as .xlsm. In that copy, convert the Sales table to a normal range (Table Design → Convert to Range), because the macro uses the sheet's AutoFilter, then run SheetPerRegion: you should get new sheets named West (100 orders), Central (107) and East (93).

practice-excel.xlsx · Sales sheet

A macro is a saved set of instructions that Excel runs for you: formatting a report, copying data between sheets, creating a PDF for each client. Macros are written in VBA (Visual Basic for Applications), a programming language built into Excel for Windows and Mac. It's the last piece in going from advanced user to the person who automates the team's busywork.

.xlsmthe file type that keeps macros; saving as .xlsx deletes them

Alt + F11opens the VBA editor to read and change the code

F8runs the macro one line at a time so you can find errors

Set up Excel
Record your first macro
Read the VBA code
Write loops and conditions
Pick the right tool

Set up Excel

Show the Developer tab: File → Options → Customize Ribbon → check Developer. On a Mac: Excel → Settings (or Preferences) → Ribbon & Toolbar → check Developer.
Save workbooks that contain macros as .xlsm (Excel Macro-Enabled Workbook). An .xlsx file can't hold macros: if you save it that way, they're removed.

Security: macros can be dangerous

A macro can delete files or send data elsewhere, which is why attackers hide malware in them. Microsoft Office now blocks macros by default in files that come from the internet or arrive as email attachments, and shows a security-risk banner. Only enable macros in files you know, from sources you trust. If one of your own files is blocked after downloading it, you can unblock it in Windows: right-click the file → Properties → check Unblock.

Your first recorded macro

The macro recorder turns your clicks into code, no typing required:

Developer → Record Macro. Name it without spaces, for example FormatReport. You can assign a shortcut like Ctrl + Shift + R.
Do the actions: bold row 1, add a fill color, autofit the column widths, apply the Currency format to column D, freeze the top row.
Developer → Stop Recording.

Open another report with the same layout and run the macro (Developer → Macros → Run, or your shortcut). Excel repeats everything in a second.

Absolute or relative references

By default the recorder captures exact cells (A1, D2…). If you turn on Use Relative References before recording, it captures moves relative to the active cell ("one to the right, two down"). It's the same idea as lesson 3's references, applied to actions.

1. You record a macro and save the workbook as .xlsx. What happens?

The macro is saved anyway.
The macro is lost: you need to save as .xlsm.
The file won't open.

.xlsx files can't contain macros. Excel warns you when you save; choose Excel Macro-Enabled Workbook (.xlsm).

The VBA editor

Press Alt + F11 (or Developer → Visual Basic, which also works on a Mac). In the Modules folder you'll find the recorded code. It will look something like this, only longer, because the recorder captures everything:

Sub FormatReport()
    Rows("1:1").Font.Bold = True
    Range("A1:F1").Interior.Color = RGB(31, 78, 121)
    Range("A1:F1").Font.Color = RGB(255, 255, 255)
    Columns("A:F").AutoFit
    Columns("D:D").NumberFormat = "$#,##0.00"
End Sub

How to read it:

Sub ... End Sub marks the beginning and end of the macro.
Range("A1:F1") is a range; Rows and Columns are rows and columns.
After the dot come properties: .Font.Bold, .Interior.Color, .Value…
VBA always uses U.S. notation (comma for thousands, period for decimals), even on a computer set to another region.

Writing macros: variables, loops and conditions

The recorder can't repeat or decide. For that you write code. If you've done the Python for data analysis lesson in our data analytics course, you'll recognize every idea.

Flag overdue invoices

Suppose a sheet has each invoice's due date in column D, and you want "Overdue" in column E when the date has passed:

Sub MarkOverdue()
    Dim r As Long
    Dim lastRow As Long

    lastRow = Cells(Rows.Count, "A").End(xlUp).Row   ' last row with data

    For r = 2 To lastRow
        If Cells(r, "D").Value < Date Then
            Cells(r, "E").Value = "Overdue"
            Cells(r, "E").Font.Color = RGB(192, 0, 0)
        Else
            Cells(r, "E").Value = "Current"
        End If
    Next r

    MsgBox "Checked " & (lastRow - 1) & " invoices."
End Sub

Dim declares variables; Long is a large whole number.
Cells(r, "D") is the cell in that row, column D.
End(xlUp) works like pressing Ctrl + ↑ from the bottom of the sheet: it finds the last row with data, even as the list grows.
Date is today's date, like TODAY().
An apostrophe ' starts a comment.

Use Excel functions inside VBA

Dim total As Double
total = WorksheetFunction.Sum(Range("D2:D500"))
Range("H1").Value = total

One sheet per region

A more advanced example: create a sheet with each region's orders from the sales list (column B = region).

Sub SheetPerRegion()
    Dim src As Worksheet, newSheet As Worksheet
    Dim region As Variant
    Set src = Worksheets("Sales")

    For Each region In Array("West", "Central", "East")
        Set newSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        newSheet.Name = region
        src.Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=region
        src.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy newSheet.Range("A1")
        src.AutoFilterMode = False
    Next region
End Sub

2. What is Cells(Rows.Count, "A").End(xlUp).Row for?

Counting how many sheets the workbook has.
Jumping to cell A1.
Finding the last row with data in column A.

It climbs from the bottom row of the sheet to the first value, like Ctrl + ↑. That way the macro keeps working as the list grows.

3. You want a macro to add up column D and store the result in a variable. Which line is valid VBA?

total = WorksheetFunction.Sum(Range("D2:D500"))
total = =SUM(D2:D500)
total = SUM("D2:D500")

Inside VBA, worksheet functions are called through WorksheetFunction, and ranges are referenced with Range(...). The other two mix up worksheet formula syntax with VBA.

Run macros the easy way

A button on the sheet

Insert → Shapes, draw a rectangle, right-click → Assign Macro.

Best for: letting coworkers use the macro without knowing it exists.

A keyboard shortcut

Under Developer → Macros → Options.

Best for: macros you run several times a day.

Personal Macro Workbook

When recording, choose to store the macro in the Personal Macro Workbook.

Best for: having the macro available in every file you open.

Debug without fear

In the editor, press F8 to run the macro one line at a time and watch what each instruction does. Save a copy of the file before testing new macros: a macro's actions can't be undone with Ctrl + Z.

Worked example: one PDF per client at an accounting firm in Charlotte

A small accounting firm in Charlotte, North Carolina, emails a monthly statement to 40 clients. It has a Statement sheet that fills itself in with formulas (XLOOKUP, SUMIFS) based on the client name typed in B1, and a Clients sheet with the 40 names in A2:A41. By hand, that's change B1, export to PDF and name the file, 40 times. With a macro:

Sub PDFPerClient()
    Dim sh As Worksheet, cell As Range
    Dim folder As String
    Set sh = Worksheets("Statement")
    folder = ThisWorkbook.Path & "\PDF\"               ' the PDF folder must exist

    For Each cell In Worksheets("Clients").Range("A2:A41")
        sh.Range("B1").Value = cell.Value              ' formulas recalculate for that client
        sh.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=folder & cell.Value & ".pdf"
    Next cell

    MsgBox "Done: " & Worksheets("Clients").Range("A2:A41").Count & " PDFs in " & folder
End Sub

Time to prepare 40 statements (minutes, estimated)

By hand120

With the macro2

By hand: about 3 minutes per client (40 × 3 = 120). With the macro: however long Excel takes to create the files, plus a quick review.

Two details make it reliable: the For Each loop handles any number of clients if you swap the fixed range for one that grows (for example, with End(xlUp)), and ThisWorkbook.Path makes it work in whatever folder you save the workbook. On a Mac, the folder separator is / instead of \.

Common macro mistakes

Testing a new macro directly on the original file.

Work on a copy: what a macro does can't be undone with Ctrl + Z.

Recording fixed ranges (A2:A41) for a list that changes size.

Find the last row with End(xlUp) or use CurrentRegion.

Running SheetPerRegion twice and getting an error.

The "West" sheet already exists: delete it first, or add a check to the code.

Enabling macros in an attachment you weren't expecting.

If you don't know who made it or why, don't enable it. Ask first.

Macros, Power Query or something else?

You need to…	Best option
Import, clean and combine data that arrives every month	Power Query (lesson 11)
Summaries that update themselves	PivotTables or dynamic array functions
Act on the workbook: formats, new sheets, PDFs, emails, buttons	VBA macros
Automate Excel for the web or connect to Teams and Outlook	Office Scripts and Power Automate (Microsoft 365)
Process huge files or connect to APIs	Python

4. Every month you combine 5 CSV reports and clean them. Which tool fits best?

A recorded macro.
Careful copying and pasting.
Power Query.

Importing and cleaning recurring data is exactly what Power Query is for, and it's easier to maintain than a macro.

Advanced-level project

Build a Sales Tracker workbook that brings together what you've learned so far:

A Power Query query that imports and cleans sales from a folder (lesson 11).
An order-entry sheet with data validation and protection (lesson 8).
A dashboard with a PivotTable, chart and slicer (lesson 9) and a search box built with FILTER (lesson 10).
A macro with a button that flags overdue orders, and another that formats the report.

See solution

There's no single right answer: each part is built from its lesson's example. For the overdue macro, use MarkOverdue from this lesson; for formatting, record FormatReport. Your workbook is in good shape if it passes this checklist:

Clicking Refresh All updates the data, the PivotTable and the chart.
The entry sheet blocks invalid dates and quantities.
The workbook is saved as .xlsm and the macros work with more rows than you had when you wrote them.
Each macro button has a label that says what it does.

What you've mastered so far

Beginner

Organizing data, formulas, references, formatting and tables.

Intermediate

IF, VLOOKUP and XLOOKUP, SUMIFS, validation and protection.

Advanced

PivotTables, dynamic arrays, Power Query and macros.

Summary

A macro repeats actions for you; it lives in .xlsm workbooks.
The recorder writes the code; Alt + F11 lets you read and adjust it.
Loops (For, For Each) and conditions (If) do what the recorder can't.
End(xlUp) keeps a macro working as the list grows.
Use Power Query to import and clean data; use macros to act on the workbook (PDFs, sheets, formatting).

You've completed the Advanced level! You now cover what most job postings mean by "advanced Excel." Next comes Level 4: Expert, where you'll use Excel for decisions that involve money.

Next lesson: Excel Financial Functions: PMT, Amortization Schedules, NPV and IRR. You'll calculate loan payments, build an amortization schedule and evaluate investments with NPV and IRR.