How to Record Your First Macro in Excel

An image of a laptop with spreadsheet related icons

Introduction

If you do the same task in Excel again and again, you’ve probably thought: “There must be a faster way.” That’s exactly what macros are for.

A macro is a set of steps that Excel can repeat for you. You do the task once, Excel records what you click and type, and then you can run the macro later with a button or a keyboard shortcut. For simple, repetitive work, recording a macro can save hours.

In this guide you will learn:

What a macro is (and what it is not)
How to enable the Developer tab
How to record a macro step-by-step
How to save macro workbooks correctly
How to run a macro and assign a shortcut
How to edit the macro in the VBA editor (basic, safe changes)
The difference between “relative” and “absolute” recording
Common mistakes and how to avoid them
When you should use formulas or Power Query instead of macros

No programming experience needed. We’ll keep it simple and practical.

What is a macro, in plain English?

A macro is a recorded “recipe” of actions in Excel. It can include things like:

Formatting a report (fonts, colours, borders, column widths)
Cleaning up a sheet (delete blank rows, change headings, convert text to numbers)
Copying data from one place and pasting it somewhere else
Creating a summary table or printing a report

What a recorded macro can’t do well

Macro recording isn’t perfect for everything. It can struggle when:

The data changes shape a lot (different columns each time)
You need “smart” logic (if this then do that, repeat for each row)
You want something flexible that works on any file, any time

For those situations, Power Query or VBA code written properly is often a better fit. But recording a macro is still the best place to start, because it teaches you how Excel automation works.

Important: macro safety (quick but worth knowing)

Macros are powerful, and that’s why they can be risky if you open macro-enabled files from unknown sources.

Only enable macros in files from people you trust
Store macro workbooks in safe locations
If you’re in a work environment, follow your IT policy

Recording your own macro in your own workbook is usually safe.

Step 1: Show the Developer tab

The macro tools live on the Developer tab, which is hidden by default.

In Excel, go to File > Options
Click Customize Ribbon
Tick Developer
Click OK

You’ll now see Developer on the ribbon.

Step 2: Plan what you want the macro to do

Before you record, decide the exact steps.

Example task:

Select the data range
Make headings bold
Add a filter
Format columns (dates, currency)
Autofit columns
Add borders

The best macros record short, clear tasks. If the macro is too long, it becomes harder to fix when something changes.

Tip: Write your steps down in plain language first. It makes recording smoother and reduces mistakes.

Step 3: Save your workbook in the right format

A normal Excel file cannot store macros.

To store macros, use:

.xlsm = Macro-Enabled Workbook (most common)
.xlsb = Binary Workbook (can be smaller and faster, but less common for sharing)

To save:

File > Save As
Choose Excel Macro-Enabled Workbook (*.xlsm)
Save

If you forget this step and save as .xlsx, Excel will warn you that macros will be removed.

Step 4: Record your first macro

Go to Developer > Record Macro
Fill in the details:

Macro name

Use a clear name with no spaces, e.g.:

FormatReport
CleanData
PrepareInvoice

Shortcut key (optional)

You can add one, like Ctrl + Shift + F.
Be careful not to overwrite common shortcuts.

Store macro in

This Workbook (best if the macro is only for this file)
Personal Macro Workbook (best if you want the macro available in all Excel files)

For your first macro, choose This Workbook.

Description (optional)

Add a short note like “Formats the sales report.”

Click OK. Recording starts immediately.

Step 5: Do the actions you want recorded (example)

Here’s a simple first macro you can try: “Format a table quickly”.

While recording:

Click anywhere inside your data.
Press Ctrl + A (select the current region of data).
Make the top row bold:
- Press Ctrl + B
Add filters:
- Home > Sort & Filter > Filter
Autofit column widths:
- Double-click a column boundary, or
- Home > Format > AutoFit Column Width
Add borders:
- Home > Borders > All Borders
Format a currency column (if you have one):
- Select the column
- Home > Number > Currency

Now stop recording:

Developer > Stop Recording

You’ve just created your first macro.

Step 6: Run the macro

To run it:

Developer > Macros
Select the macro name
Click Run

If it works, great. If it does something strange, don’t worry. That’s part of learning. You can delete it and record again, or edit it.

Step 7: Add a button to run your macro (easy for users)

Buttons are great if other people will use the macro.

Developer > Insert
Under Form Controls, choose Button
Click and drag on the sheet to draw the button
Assign your macro and click OK
Right-click the button to edit the text, e.g. “Format Report”

Now anyone can click the button to run the macro.

Step 8: Absolute vs Relative recording (this matters)

Excel records actions in two ways:

Absolute references (default)

Excel records “go to cell B2” or “select range A1:D20”.
This works if your data is always in the same place.

Relative references

Excel records “move one cell right” or “select the current region from where I am”.
This is better when you want the macro to work no matter where the active cell starts.

To use relative recording:

Developer > Use Relative References (turn it on)
Record the macro
Turn it off afterwards if you want

Simple rule:

If your report always starts in the same location, absolute is fine
If you want the macro to work on different sheets/ranges, relative is usually better

Step 9: View and edit the macro (basic and safe)

Macros are stored as VBA code (Visual Basic for Applications). You don’t need to be a coder to make small improvements.

To view:

Developer > Visual Basic

In the VBA editor:

Find your workbook in the Project window
Go to Modules
Double-click Module1 (or similar)

You’ll see code like:

A “Sub” (short for subroutine) named after your macro
A list of commands Excel recorded

A safe first edit: add a comment

Comments start with an apostrophe '

' This macro formats the report table

Another safe improvement: speed up the macro

Recorded macros can flicker on screen. You can reduce this by adding:

Application.ScreenUpdating = False

at the start, and turning it back on at the end:

Application.ScreenUpdating = True

Just make sure you add both, otherwise Excel may stay frozen visually until you restart.

Step 10: Common mistakes (and how to avoid them)

Mistake 1: Recording “mouse-heavy” steps

Dragging, clicking tiny icons, and switching tabs a lot can create messy code.

Better: use keyboard shortcuts and simple commands where possible.

Mistake 2: Selecting lots of cells you don’t need

Recorded macros often include too many Select and Activate commands.

Better: keep actions direct and simple. (You can tidy this later in VBA training.)

Mistake 3: Hard-coding a fixed range that changes

If you always select A1:D200 but sometimes you have 500 rows, the macro won’t catch them.

Better: select the current region:

Click inside the data
Ctrl + A to select the whole block
This records better behaviour.

Mistake 4: Forgetting to save as .xlsm

If you save as .xlsx, Excel removes the macro.

Fix: Save as .xlsm.

Mistake 5: Macros failing on someone else’s computer

This can happen if:

They don’t enable macros
Their Excel security settings block macros
They don’t have access to referenced files

Fix: share instructions, and keep macros in trusted locations.

Step 11: When a macro is the wrong tool (and what to use instead)

Macros are great for repeating the same steps. But for some tasks, these tools are better:

Use formulas when:

You want live results that update as data changes
You’re doing calculations, lookups, summaries, and logic

Use Power Query when:

You import and clean data regularly
You need repeatable transformations (remove columns, split, merge, unpivot)
You want refresh-based automation without clicking through steps

Use VBA code (beyond recording) when:

You need loops (“do this for each file”)
You need user forms, prompts, error handling
You want professional-level automation that runs reliably

Step 12: Next steps after your first macro

Once you can record and run a macro, your next steps are usually:

Record with Relative References for flexibility
Learn how to tidy recorded code (remove unnecessary selects)
Add basic logic (IF statements)
Loop through rows or multiple sheets
Build a simple “one-click” solution for reports

These are the skills that turn recorded macros into real automation.

Conclusion

Recording your first macro is a big step. It shows you that Excel can do repetitive work for you, consistently and quickly. Start small, record clean steps, save your workbook as .xlsm, and run the macro from the Macros list or a button. Once you’re comfortable, you can open the VBA editor and make small improvements, then build towards more powerful automation.

If you’d like structured, hands-on learning with practical exercises, these courses can help depending on where you want to go next:

Excel Introduction – solid Excel foundations
Excel Intermediate – better data handling and efficient workflows
Excel Advanced – advanced tools and reporting techniques
Excel Power User: Mastering Complex Functions and Data Models – high-impact Excel techniques for serious users
Excel Power Query – refresh-based data cleaning and transformation
Excel VBA Introduction – record macros and understand/edit VBA safely
Excel VBA Intermediate – build stronger macros with logic and loops
Excel VBA Advanced – robust automation, error handling, and professional solutions

‍

View all ARTICLES

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.