Record a Macro in Excel: Macro Recorder Step by Step

Part of the free Module 12: Excel VBA Course · Lesson 2 of 18 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.

To record a macro in Excel, click Record Macro on the View or Developer tab, give the macro a name and a shortcut key, perform the task once, then click Stop Recording. The Excel Macro Recorder converts every click into VBA code stored in a module, so you can replay the task on any range with Alt+F8 or your shortcut, without writing code yourself.

What the Excel Macro Recorder does and when to use it

The recorder is a translator. While it is switched on, every formatting change, sort, filter, page setup or copy and paste you make is written as VBA into a standard module. That makes it the fastest way to automate a repetitive mouse job such as applying a house style to a report, setting print areas or cleaning an export from an accounting system.

It is also the best VBA teacher you will ever have. Do not know how to set a fill colour or freeze panes in code? Record it, then read what Excel wrote. You learn the exact object names, properties and constants without searching.

The recorder has clear limits. It cannot make decisions, repeat itself or ask the user a question, and it records more than you need. Most professional macros therefore start as a recording and are trimmed and extended by hand, which is exactly what the rest of this course teaches.

Where to find the Record Macro button

The command is available in three places and all three open the same dialog.

• View > Macros > Record Macro, available in every copy of Excel without any setup.

• The small macro icon in the status bar at the bottom left of the Excel window. It turns into a square Stop button while you record.

• Developer > Code > Record Macro, next to the Use Relative References toggle you will need later. The Introduction lesson shows how to switch the Developer tab on.

The Record Macro dialog explained

Four fields appear when you click Record Macro. Get them right before you press OK, because recording starts the moment the dialog closes.

Field	What to enter	Rules and advice
Macro name	Format_Table	Letters, numbers and underscores only. Must start with a letter. No spaces, no cell-style names such as A1 or R2C3.
Shortcut key	m or M	A lower-case letter gives Ctrl+letter; a capital gives Ctrl+Shift+letter. Prefer Ctrl+Shift so you do not override Ctrl+C, Ctrl+S and friends.
Store macro in	This Workbook	This Workbook keeps the macro with the file. Personal Macro Workbook makes it available in every workbook on this PC. New Workbook creates a separate file.
Description	Applies the standard table style	Optional. It becomes a comment at the top of the recorded procedure.

How to record a macro in Excel step by step

1. Open a workbook, select a small range of cells and click any Record Macro button.
2. In the Record Macro dialog type Format_Table as the name. In Shortcut key type m to create Ctrl+M. Set Store macro in to This Workbook and click OK.

3. Apply the formatting you want to automate: outside and inside borders, font name and size, fill colour and centre alignment. Work carefully, because every action is recorded, including mistakes and their corrections.

4. Click Stop Recording on the View or Developer tab, or click the square icon in the status bar.
5. Test it. Select a different range, such as I4:K9, press Alt+F8 to open the Macro dialog, pick Format_Table and click Run.

The second range receives exactly the same formatting. From now on selecting a range and pressing Ctrl+M does the whole job in one keystroke.

Three ways to run a recorded macro

Method	How	Best for
Shortcut key	Press the Ctrl or Ctrl+Shift combination you chose	Macros you use many times a day
Macro dialog	Alt+F8, select the macro, click Run	Occasional macros, or checking which macros a file contains
Button or shape	Developer > Insert > Button (Form Control), or right-click any shape and choose Assign Macro	Macros other people will run without knowing the shortcut
Quick Access Toolbar	File > Options > Quick Access Toolbar, choose Macros, add the macro	Personal Macro Workbook macros you want in every file

Absolute versus relative references

By default the recorder stores absolute references. If you click cell B2 while recording, the code says Range("B2").Select and the macro will always go to B2, whatever is selected when it runs. That is right for a fixed report layout and wrong for a task that should happen “here”.

Click Developer > Use Relative References before or during recording and the recorder writes movements instead: ActiveCell.Offset(1, 0).Select means “one row down from wherever I am”. Use relative mode for macros such as “fill this row and move to the next”, and absolute mode for “put the title in A1”. You can toggle the setting while recording, so one macro can mix both.

Read and clean the recorded code

Press Alt+F11 to open the Visual Basic Editor and double-click Module1 under Modules in the Project Explorer. A recording of border, font and alignment changes looks roughly like this, shortened for clarity:

Option Explicit

Sub Format_Table()
' Format_Table Macro
' Keyboard Shortcut: Ctrl+m
    Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
    Selection.Borders(xlEdgeBottom).Weight = xlThin
    With Selection.Font
        .Name = "Arial"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
    End With
    Selection.HorizontalAlignment = xlCenter
End Sub

Option Explicit belongs once at the very top of the module; the recorder does not add it, so type it yourself. Because the code refers to Selection, the macro works on whatever is selected when it runs.

Notice the noise. The recorder sets Strikethrough, Superscript and Subscript to False even though you never touched them, and it writes a separate block for each of the four borders. Cleaning a recording means three things: delete properties you did not change, remove every .Select and Selection. pair by working on the range directly, and wrap related properties in one With block. The hand-cleaned version below does the same work and runs faster:

Sub Format_Table_Clean()
    If TypeName(Selection) <> "Range" Then Exit Sub
    With Selection
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
        .Font.Name = "Arial"
        .Font.Size = 11
        .HorizontalAlignment = xlCenter
        .Interior.Color = RGB(221, 235, 247)
    End With
End Sub

The guard clause stops the macro if a chart or shape is selected instead of cells. Later in the course you will replace Selection with an explicit range such as Worksheets("Data").Range("A1").CurrentRegion, so the macro no longer depends on what the user clicked.

Worked example: recording a report clean-up

A payroll export arrives every Friday with the raw layout below, and you want a bold header, sorted names and a fitted print page.

A: Employee	B: Department	C: Hours
Ravi	Sales	38
Anita	Finance	40
Mohan	Sales	35

1. Click Record Macro, name it Clean_Payroll, shortcut Ctrl+Shift+P, store in This Workbook.
2. Click A1, press Ctrl+A to select the block, then Data > Sort by Employee, A to Z.
3. Select row 1 and press Ctrl+B. Select columns A:C and double-click a column border to autofit.
4. Open Page Layout > Orientation > Landscape and set Width to 1 page.
5. Click Stop Recording.

Result: the sheet now reads Anita, Mohan, Ravi with a bold header and prints on one landscape page. Next Friday, paste the new export, press Ctrl+Shift+P and the whole clean-up takes under a second. In the editor you will see that the sort step was recorded with a fixed range such as A1:C4; change it to Range("A1").CurrentRegion so the macro copes with a longer list.

The Personal Macro Workbook

Choose Personal Macro Workbook in the Store macro in list and Excel creates a hidden file called PERSONAL.XLSB in your XLSTART folder. It opens silently every time Excel starts, so every macro inside it is available in every workbook. It is the right home for general tools such as “format as report” or “unhide all sheets”. When you close Excel it asks whether to save changes to the Personal Macro Workbook; click Save or the macro is lost. To edit its code, unhide it with View > Unhide or open it directly in the Visual Basic Editor under VBAProject (PERSONAL.XLSB).

Save the workbook correctly

Press F12 and choose Excel Macro-Enabled Workbook (*.xlsm) or Excel Binary Workbook (*.xlsb). The default .xlsx format cannot hold VBA. Excel warns you before stripping the code, but many beginners click Yes and lose an afternoon of work.

What the Macro Recorder cannot do

• No logic. It cannot write If, Select Case or loops, so a recording never decides anything or repeats itself.
• No input. It cannot ask the user a question or read a value into a variable.
• No user forms or events. Automatic macros that run when a file opens or a cell changes must be written by hand.
• Some actions are not recorded at all. Steps inside dialogs of other applications, and many Power Query, chart and shape edits, record poorly or not at all.
• It is slow by design. Recorded code selects and scrolls exactly as you did, which is far slower than working on ranges directly.

Tips and common mistakes

• Plan the clicks first. Do a dry run with the recorder off, then record in one clean pass. Fixing a mis-click during recording adds junk to the code.
• Do not use a shortcut Excel already owns. Ctrl+C, Ctrl+V, Ctrl+S and Ctrl+Z are common victims. Ctrl+Shift+letter is almost always free.
• Turn on relative references for “do it here” macros. Absolute recordings silently jump back to the original cells.
• Delete Select and Activate lines when editing. Replace Range("A1").Select followed by Selection.Font.Bold = True with Range("A1").Font.Bold = True.
• Recorded macros cannot be undone. Ctrl+Z does not reverse a macro, so test on a copy of your data.
• Enable macros when the file reopens. The yellow security bar blocks the code until you click Enable Content.
• Name macros for what they do. Macro1, Macro2 and Macro3 are unreadable within a week.

Errors and how to fix them

Message or symptom	Cause	Fix
“Invalid procedure name” when clicking OK	Name contains a space, starts with a number or looks like a cell reference	Use letters and underscores only, for example Clean_Payroll
Macro runs but formats the wrong cells	Recorded with absolute references	Re-record with Use Relative References on, or change the code to use Selection or ActiveCell
Macros are missing after reopening the file	Saved as .xlsx	Save as .xlsm or .xlsb; the code is gone from the .xlsx copy, so re-record if you have no backup
Alt+F8 shows an empty list	Macros disabled by the security bar, or the macro lives in another closed workbook	Click Enable Content, or open the workbook (or PERSONAL.XLSB) that holds the macro
Run-time error 1004 on a recorded Sort or PasteSpecial line	The sheet or range recorded no longer exists or is protected	Edit the sheet and range names in the code, or unprotect the sheet before running

Practice exercise

1. Record a macro named Header_Style that makes the selected row bold, white on dark blue, and centred. Assign Ctrl+Shift+H and test it on three different rows.
2. Record Print_Setup with landscape orientation, fit to one page wide and a footer showing the page number. Store it in the Personal Macro Workbook and run it from a new file.
3. Record the same movement, “go two cells to the right and type Done”, twice: once with relative references off and once on. Compare the two procedures in the editor.
4. Take any recording and rewrite it without a single .Select line, then confirm it produces the same result.

Key takeaways

• The Macro Recorder turns your clicks into VBA in a standard module; find it on the View tab, Developer tab or status bar.
• Choose a valid name, a Ctrl+Shift shortcut and the right storage location before you press OK.
• Absolute references replay fixed addresses; Use Relative References records movement from the active cell.
• Run macros with the shortcut, Alt+F8, a button or the Quick Access Toolbar.
• Clean recorded code by removing unchanged properties and Select or Activate lines.
• Save as .xlsm or .xlsb, or the macro disappears.

Related lessons

• Excel VBA course hub
• Introduction to VBA and the Visual Basic Editor
• Cells and Range objects in VBA
• Formatting cells with VBA
• Debugging VBA code
• Excel sort and filter module
• Microsoft Support: Automate tasks with the Macro Recorder

Frequently asked questions

Where is a recorded macro stored in Excel?

In a standard module, named Module1, Module2 and so on, inside the workbook you chose under Store macro in. Press Alt+F11, expand the workbook in the Project Explorer and open the Modules folder to see the code. Macros stored in the Personal Macro Workbook live in PERSONAL.XLSB in your XLSTART folder.

Can I edit a macro after recording it?

Yes. Open the module in the Visual Basic Editor and change the code directly. Delete properties you did not intend to set, remove Select and Activate lines and replace fixed addresses with Selection or a named range. Press F5 inside the procedure to test the edited version immediately.

Why does my macro only work on the original cells?

It was recorded with absolute references, so it selects the same addresses every time. Either record it again with Developer > Use Relative References switched on, or edit the code so it works on Selection or ActiveCell rather than a fixed cell address such as B2.

What is the difference between a macro and VBA?

A macro is a procedure that Excel can run, usually a Sub. VBA, Visual Basic for Applications, is the language the procedure is written in. The recorder produces macros written in VBA; when you edit or write them yourself you are programming in VBA. In everyday use the two words are interchangeable.

Can I undo a macro in Excel?

No. Running a macro clears the undo history, so Ctrl+Z will not reverse its changes. Save the workbook before running an untested macro, or work on a copy. Professional macros sometimes create their own backup sheet for the same reason.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.