Excel Macros for Beginners: Record Your First Macro

Excel macros for beginners start with one tool: the macro recorder. You switch it on, do a task once, such as formatting a report, and Excel saves your steps so you can replay them with a shortcut. You don't need to write any code. This guide shows how to record, run and save your first macro, and what to watch out for so a macro doesn't damage your data.

What is a macro in Excel?#

A macro is a saved list of actions that Excel can repeat for you. When you record one, Excel writes your clicks and typing as code in VBA (Visual Basic for Applications), the programming language built into desktop Excel.

Macros are worth it for tasks you repeat often and always do the same way:

• Formatting a weekly export: bold headers, a fill colour, number formats, column widths.
• Setting up a page for printing: margins, orientation and print titles. The print setup guide shows the steps you'd record.
• Clearing an input area before the next month.

For a one-off task, a macro is usually slower than doing it by hand. Keyboard shortcuts, the Format Painter and Flash Fill solve many repeat jobs without any code.

How to show the Developer tab#

The Developer tab holds every macro tool, but it's hidden by default. You can record simple macros from View > Macros without it, but switching it on is worth the 20 seconds:

• Windows: File > Options > Customize Ribbon, tick Developer in the right-hand list, and click OK.
• Mac: Excel > Preferences > Ribbon & Toolbar, tick Developer in the list of main tabs, and click Save.

The Developer tab now appears at the right of the ribbon, with Record Macro, Macros, Visual Basic and Use Relative References in the Code group.

How to record your first macro#

This example records a macro called FormatReport that turns a raw export into a tidy table. Open a sheet with headers in row 1 and data below.

1. Click any cell in the sheet.
2. Click Developer > Record Macro (or View > Macros > Record Macro).
3. In Macro name, type FormatReport. Names must start with a letter and can't contain spaces; use an underscore or capitals instead.
4. Optional: set a Shortcut key. Type a capital letter, such as R, so the shortcut becomes Ctrl+Shift+R. A lowercase letter would replace a built-in shortcut, such as Ctrl+C for copy. On a Mac, macro shortcuts use Option+⌘.
5. In Store macro in, keep This Workbook.
6. Click OK. Everything you do is now recorded.
7. Do the task: select row 1, make it bold (Windows: Ctrl+B; Mac: ⌘B), add a fill colour, select the data columns and apply a number format, then double-click a column border to autofit.
8. Click Developer > Stop Recording (or View > Macros > Stop Recording).

The recorder captures everything, including mistakes and extra clicks. If you go wrong, stop, delete the macro and record it again. Planning the steps first, ideally as shortcuts, makes a cleaner macro; the Excel keyboard shortcuts cheat sheet is a good place to find them.

The other storage choice, Personal Macro Workbook, saves the macro in a hidden workbook that opens with Excel, so you can use it in every file on your computer.

How to run your macro#

Run it on a fresh copy of the export to check it works. You have three options:

• Shortcut key: press the one you set, such as Ctrl+Shift+R.
• Macros list: click Developer > Macros (Windows: Alt+F8), pick FormatReport and click Run.
• A button: click Developer > Insert > Button (Form Control), draw it on the sheet and assign the macro. Anyone can then click it.

Before you run a macro, save the workbook. Macro actions can't be undone with Ctrl+Z (Mac: ⌘Z), so a macro that deletes rows or overwrites values can't be reversed except by closing without saving.

Absolute or relative references?#

By default the recorder uses absolute references: it remembers the exact cells you clicked, such as A1:E1. The macro then always formats those cells, wherever you are when you run it.

Turn on Developer > Use Relative References before recording when you want the macro to work from the active cell instead. For example, a macro recorded with relative references that bolds "the cell I'm on and the four to its right" works on any row. It's the same idea as relative and absolute references in formulas, and the same idea behind filling formulas down with Ctrl+D.

Use absolute for fixed layouts like a standard report. Use relative for steps you want to repeat on whatever row or cell you select.

How to save a workbook with macros#

Save the file as an Excel Macro-Enabled Workbook (.xlsm):

1. Click File > Save As.
2. In the file type list, choose Excel Macro-Enabled Workbook (*.xlsm).
3. Click Save.

If you save as a normal .xlsx file, Excel warns that the VBA project can't be saved in a macro-free workbook. Click No to go back and pick .xlsm. Clicking Yes saves the file without your macro. The comparison of XLSX vs XLSM explains the file types in detail.

How to see and edit the macro code#

Click Developer > Visual Basic (Windows: Alt+F11) to open the VBA editor. Your macro sits in a Module under the workbook's name. A short formatting macro looks something like this:

Sub FormatReport()
    Range("A1:E1").Font.Bold = True
    Range("A1:E1").Interior.Color = RGB(221, 235, 247)
    Columns("A:E").AutoFit
End Sub

Recorded code is usually longer, because the recorder writes every Select and every setting it touched. You can make small changes safely, such as replacing "A1:E1" with "A1:H1" to cover more columns. Close the editor to go back to Excel; the change is saved with the workbook.

Macro safety and limits#

A macro can run any code, including code that deletes files or sends data. That's why Excel treats macro files carefully:

• Enable Content appears in a yellow bar when you open a macro file. Click it only if you trust where the file came from.
• Blocked macros. For files downloaded from the internet or opened from an email, current versions of Excel on Windows block macros by default and show a red security bar. This protects you from harmful attachments.
• No undo. As noted above, save before running.
• Desktop only. You can't record or run VBA macros in Excel for the web, or in the mobile apps.
• Many tasks don't need a macro. Copying a format is faster with the Format Painter, and splitting or joining text by example is faster with Flash Fill. Summary lines and totals are best done with formulas, which update themselves.

Practise it#

Macros replay what you'd do by hand, so the better you know the manual steps, the cleaner your macros. Practise formatting shortcuts in a live spreadsheet, the exact steps you'd record for a report, then practise Fill Down and Fill Right to copy formulas without code. Each lesson checks your work instantly. Then try the exercise on this page to build a summary line with a formula instead of a macro.