Skip to content
Tutorials Center
My learning

Topics

Lesson 72 of 93 ยท Automation and Collaboration

Macros and the Macro Recorder

Learn Excel macros: show the Developer tab, record and run a macro, absolute vs relative recording, the Personal Macro Workbook, macro security and xlsm files.

Do you repeat the same clicks every week? Format a header, delete three columns, sort, add a total row, save as PDF. A macro is a recording of those actions that Excel can replay with one click or one keyboard shortcut. You do not need to write code to make one: the Macro Recorder watches what you do and writes the code for you.

Macros are written in VBA (Visual Basic for Applications), which you meet in the next lesson, VBA Basics. Excel cannot run on this site, so everything in this lesson that belongs to Excel is read-only text and code blocks. The live examples are JavaScript stand-ins: a small model of a sheet and a recorder that stores actions and replays them, so you can see why absolute and relative recording behave differently. They are teaching models, not the real recorder.

Record, run, repeat

Record Do the task once.
Save as xlsm Macros need this format.
Run Button, shortcut or Alt+F8.
Reuse On any sheet, any week.
1 / 2

Show the Developer tab

Recording and running are available on the View tab (Macros button at the right), but the Developer tab is more convenient. It is hidden by default:

  1. Open File > Options > Customize Ribbon.
  2. In the right list, tick Developer.
  3. Press OK.

The Developer tab holds Visual Basic, Macros, Record Macro, Use Relative References, Macro Security and the controls for adding buttons to a sheet. Excel for the web can neither record nor run VBA macros; use the desktop app, or look at Office Scripts in Office Scripts, Power Automate and Python in Excel.

Record your first macro

Suppose each week you receive a list in columns A to D and you always make the header bold, yellow and the columns the right width. Use this plan:

  1. Click a cell, then Developer > Record Macro (or the small record icon at the left of the status bar).
  2. In the dialog, set:
    • Macro name: letters, numbers and underscores, starting with a letter, with no spaces (FormatHeader).
    • Shortcut key: optional, Ctrl + a letter. Choose carefully: it overrides built-in shortcuts such as Ctrl+S inside that workbook while it is open. Use a Shift combination (Ctrl+Shift+H) to be safe.
    • Store macro in: This Workbook, New Workbook or Personal Macro Workbook (see below).
    • Description: a sentence that tells future you what it does.
  3. Press OK. Recording starts.
  4. Do the task: select A1:D1, press Ctrl+B, apply a yellow fill, select the columns and AutoFit Column Width.
  5. Click Stop Recording.

Every action between OK and Stop is saved, including mistakes. If you click the wrong thing, stop and record again; fixing it mid-recording leaves extra steps in the code. Only record what the macro must do: select, format, sort. Avoid scrolling and clicking around; the recorder stores selections, not scrolling.

What the recorder writes

Press Alt+F11 to open the Visual Basic Editor and look in Modules. The recorded macro might read like this (real VBA, shown read-only):

VB
Sub FormatHeader()
'
' FormatHeader Macro
' Make the header row bold and yellow
'
    Range("A1:D1").Select
    Selection.Font.Bold = True
    Selection.Interior.Color = 65535
    Columns("A:D").Select
    Columns("A:D").EntireColumn.AutoFit
    Range("A2").Select
End Sub

It is plain and readable: Sub ... End Sub wraps a macro, Range("A1:D1").Select selects cells and Selection refers to them. 65535 is the number VBA uses for yellow (red 255 + green 255 times 256). The recorder usually writes more lines than needed, with lots of Select; you can later tidy the code (see VBA Basics).

Run a macro

  • Developer > Macros (or Alt+F8), select it, press Run.
  • Press the shortcut key you chose.
  • Add a button: Developer > Insert > Button (Form Control), draw it on the sheet and pick the macro. Click it to run.
  • Add it to the Quick Access Toolbar: File > Options > Quick Access Toolbar > choose "Macros".
  • To stop a macro that runs too long, press Esc or Ctrl+Break.

Always keep a backup before the first run on important data: a macro run cannot be undone with Ctrl+Z, because Excel empties the Undo list when a macro changes the sheet.

Absolute versus relative recording

By default the recorder is absolute: it stores cell addresses. Select A1, type a title, select B2: the code says Range("A1") and Range("B2"), so it always acts on the same cells.

With Developer > Use Relative References turned on (it stays on until you turn it off, or close Excel), the recorder stores moves from the active cell: "one cell down, two to the right". Relative recording makes a macro that works on whichever cell you start in. Turn it on before you click Record, or at any point during recording, as it is a toggle.

Which to use?

Situation Choose
Always format the same header range Absolute
Work on the selected cell or data block, wherever it is Relative
Add a total row under a table of changing length Relative, with Ctrl+Down to jump to the end
Insert a stamp at a fixed place Absolute

Use keyboard navigation while recording relatively, such as Ctrl+Down, Ctrl+Right and Ctrl+Shift+Down, because they find the real edge of the data rather than a fixed row.

Stand-in: a recorder that stores actions

This model keeps a sheet and an active cell. It records actions in two ways: absolute (cell addresses) and relative (offsets from where you started). Then it replays both from a different starting cell.

JS Absolute vs relative recording (stand-in)

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Both replays are the same when you start in B2, but from E10 the absolute macro still writes into B2:B3 and the relative one writes into E10:E11.

The Personal Macro Workbook

A macro stored in This Workbook is available only while that file is open. Choose Personal Macro Workbook in the Record Macro dialog to keep it in a hidden file called PERSONAL.XLSB, which Excel opens in the background every time it starts. The macros in it work on any workbook, which is perfect for general tools such as "clean up a pasted list".

Facts:

  • The file is created the first time you record a macro into it and is stored in Excel's XLSTART folder on your computer. It belongs to your user account, not to the workbook, so a colleague does not receive it with the file.
  • View > Unhide shows it if you need to see its sheets. Do not delete it, or you lose the macros.
  • When you close Excel you may be asked whether to save changes to the Personal Macro Workbook: choose Save.
  • Macros that a workbook needs (for a colleague, for a button on a sheet) must live in that workbook instead.

Macro security and file formats

Macros are programs, and programs can do harm: delete files, send data, download software. Malware has often been delivered as a macro in an attachment. So Excel is cautious.

  • File > Options > Trust Center > Trust Center Settings > Macro Settings offers: disable all without notification, disable with notification (the default; a yellow bar lets you enable them), disable except digitally signed macros, and enable all (not recommended).
  • Workbooks that came from the internet or an email carry a "Mark of the Web", and current versions of Microsoft 365 block their macros completely, showing a red Security Risk banner. If you trust the file, right-click it in File Explorer > Properties > tick Unblock > OK, then reopen it.
  • Trusted Locations are folders whose files may run macros without a prompt. Only trust a folder you control.
  • Never enable macros in a file you did not expect, even if it says the content cannot be shown otherwise.

Macros can only be saved in macro-enabled formats:

Format Extension Macros?
Excel Workbook .xlsx No (they are removed)
Excel Macro-Enabled Workbook .xlsm Yes
Excel Binary Workbook .xlsb Yes
Personal Macro Workbook .xlsb Yes

If you press Save on a workbook with a macro in .xlsx, Excel warns that the VBA project will be lost. Choose No and save as .xlsm (see Saving, File Formats and Sharing).

A realistic stand-in: macro as a replayable list of formatting steps

A recorded macro is a script stored in the workbook. Here is a second stand-in: a list of named steps over a small table that you can replay on new data. It shows why a macro is helpful when the same data arrives every week, and why the checks in the code matter: the recorder never asks "what if the sheet looks different?"

JS Replaying the same steps on new data (stand-in)

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The same steps ran on two different data sets with different row counts. A real recorded macro that selects fixed ranges such as Range("A2:B4") would not adapt; that is the main reason to learn VBA, which can find the last row for itself.

Habits that keep macros safe

  1. Test on a copy of your data. A macro cannot be undone.
  2. Give every macro a clear name and description.
  3. Save the workbook as .xlsm before you close it.
  4. Keep the recording short and focused; record several small macros rather than one huge one.
  5. Check the recorded code. Recorded code that clicks cells is fragile if the layout changes.
  6. Never run a macro from a source you do not trust.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why can't you save a workbook that contains a macro as .xlsx?

    The .xlsx format has no place for the VBA project. Save as .xlsm (or .xlsb) to keep macros.
  2. 2. You want a macro that always acts on whichever cell is selected when it starts. Which recording mode do you need?

    With Use Relative References on, the recorder stores moves from the active cell instead of fixed addresses.
  3. 3. Where should you store a general-purpose macro that you want available in every workbook?

    The Personal Macro Workbook (PERSONAL.XLSB) opens hidden each time Excel starts, so its macros work with any file.
  4. 4. What happens to the Undo list when a macro changes the worksheet?

    Excel cannot undo what a macro did. Keep a backup or test on a copy before running macros on important data.
  5. 5. A macro file you downloaded shows a red Security Risk banner and will not run. What is the safe way forward?

    Files from the internet are blocked by default. After confirming they are trustworthy, tick Unblock in the file's Properties. Do not weaken security for the whole system.

Summary

  • Turn on the Developer tab, press Record Macro, do the task once, press Stop; run it with Alt+F8, a shortcut key or a button.
  • Recorded code is VBA; open it with Alt+F11. Absolute recording stores addresses, relative recording stores moves from the active cell.
  • The Personal Macro Workbook holds macros for every workbook; macros a file needs must be saved in that file.
  • Macros need .xlsm or .xlsb; a macro run cannot be undone, so test on a copy.
  • Macro security protects you from malware: keep the default, unblock only files you trust, and use Trusted Locations carefully.

Next: VBA Basics.