Excel Tutorials
A macro records the clicks you already make and plays them back on demand, turning a ten-minute formatting routine into one keystroke. This lesson covers recording, running, editing the generated code, absolute vs relative references and macro security, using the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
A macro is a saved sequence of actions that Excel can replay for you. When you record one, Excel watches what you do and writes it out as VBA (Visual Basic for Applications), the programming language built into Office. Running the macro re-executes those steps in the same order, in a fraction of a second.
The important thing is that you do not need to write any code to use them. The Macro Recorder does the writing, which means anyone who can perform a task manually can automate it. Reading the code it produces is also the easiest way to start learning VBA, because you can see exactly which line matches which action you took.
Macros pay off on anything repetitive and predictable: reformatting a weekly export, stripping blank rows out of a download, applying the same twelve formatting steps to every new report. If you find yourself doing an identical sequence more than a couple of times a month, it is a macro candidate.
Step-by-step, matching the video above.
Macro tools live on a tab that is hidden by default. Go to File > Options > Customize Ribbon, tick Developer in the right-hand list and click OK. You can also record a macro from the View tab or the status bar without it, but the Developer tab is where the code, security and button tools live.
The recorder captures everything you do, including mistakes and stray clicks. Run through the task once manually first so you know the exact order of steps, then record it cleanly on the second pass.
Give it a name with no spaces (use underscores), optionally assign a Ctrl+Shift shortcut key, choose where to store it, and add a description so the next person knows what it does.
This Workbook keeps it inside the current file. Personal Macro Workbook stores it in a hidden workbook that opens with Excel, making the macro available in every file on that machine. New Workbook puts it into a fresh file.
Do the task exactly as you normally would: formatting, sorting, deleting columns, applying filters, whatever the repetitive job is. Every action is being translated into VBA behind the scenes as you work.
The stop button sits on the Developer tab and in the bottom-left of the status bar. Nothing is captured after you click it.
Press Alt+F8, pick the macro from the list and click Run, or use the shortcut key you assigned. Test it on a copy of your data first, since running a macro clears the undo history.
Use File > Save As and pick Excel Macro-Enabled Workbook (.xlsm). Saving as a normal .xlsx silently strips every macro out of the file.
This one setting decides whether your macro always writes to the same cells or works wherever the cursor happens to be.
If you start in A1 and type into B2 while recording, the macro will always write to B2, no matter which cell is selected when you run it. Good for macros that target a fixed layout, like a report template that never moves.
Click Use Relative References on the Developer tab before you record. Now the macro records the movement instead of the address: one cell down, two to the right. Run it from D10 and it acts on D11 and F11 instead of B2.
Anything you repeat down a list, formatting each new row, splitting a name, stamping a date, should almost always be recorded with relative references on.
| Recorded Step | What VBA Captures | Runs As |
|---|---|---|
| Select row 1 | Rows("1:1").Select | Absolute |
| Bold the header | Selection.Font.Bold = True | Absolute |
| Freeze the top row | ActiveWindow.FreezePanes = True | Absolute |
| AutoFit all columns | Cells.EntireColumn.AutoFit | Absolute |
Four clicks that you would otherwise repeat on every monthly export become a single keystroke. Because the header is always row 1 in this report, absolute recording is the right choice here. If instead you were formatting whichever row you happened to be sitting on, you would turn on Use Relative References first.
You do not need to write VBA to benefit from reading it. The recorder writes it for you and small edits go a long way.
Your recorded macro sits under Modules > Module1 in the project pane on the left. Double-click it to see the code the recorder generated.
The recorder selects a cell and then acts on the selection, two steps where one will do. Combining Range("B2").Select and Selection.Font.Bold = True into Range("B2").Font.Bold = True makes the macro noticeably faster on large files.
If you do not know the VBA for something, record yourself doing it once and read what the recorder wrote. It is the fastest VBA reference there is.
Anything after an apostrophe is ignored when the macro runs, so use it to leave notes explaining what each block does for whoever opens the file next.
A keyboard shortcut works for you. A button on the sheet works for everyone else.
On the Developer tab choose Insert > Button (Form Control), drag it onto the sheet, and Excel immediately asks which macro to assign to it.
Draw a rectangle from the Insert tab, right-click it and choose Assign Macro. You get the same behaviour with full control over colours and text, which looks far better than the default grey button.
For macros stored in your Personal Macro Workbook, adding them to the Quick Access Toolbar makes them available across every workbook you open, not just the one with the button in it.
Macros are code, so Excel treats files containing them cautiously by default. Here is what that means in practice.
Under Developer > Macro Security the default is to disable macros with a notification, which shows a yellow bar you can click to enable them. Leave it there rather than enabling all macros outright.
Downloaded .xlsm files are marked with Mark of the Web and may show a red banner instead of a yellow one. Right-click the file, choose Properties and tick Unblock, but only when you know the source.
Adding a folder as a Trusted Location in the Trust Center lets macros in that folder run without a prompt. Use a dedicated folder, not your entire Documents directory.
A recipient who gets an unexpected .xlsm file and clicks Enable Content is exactly the habit that phishing relies on. Flag it in the email so enabling is a considered choice, not a reflex.
A standard .xlsx file cannot contain macros. Excel warns you, but the warning is easy to click past, and once saved the code is gone with no way to recover it. Always save as .xlsm.
Running a macro clears Excel's entire undo stack. If the macro deletes the wrong 400 rows, Ctrl+Z will not bring them back. Test on a copy every time.
The macro works perfectly on the row you recorded it on and writes over the wrong cells everywhere else. If the macro should act wherever the cursor is, turn on Use Relative References before you start recording.
Macro names must begin with a letter and cannot contain spaces. Excel rejects the name with an unhelpful error, so use underscores: Format_Monthly_Report.
Assigning Ctrl+S or Ctrl+C to a macro replaces the normal behaviour for as long as that workbook is open. Add Shift to the combination (Ctrl+Shift+F) to stay clear of the built-in keys.
Every wrong click, every extra cell selection and every typo you correct gets written into the code. Do a practice run first, then record the clean version.
A macro saved to This Workbook only exists in that one file. If you want it available in every workbook you open, store it in the Personal Macro Workbook instead.
If you are writing code to clean up the same export every week, the export is the problem.
Free 14-day trial. No credit card required.
A macro is a recorded sequence of actions that Excel saves as VBA code and can replay on demand. It lets you repeat a multi-step task, like formatting a report, with a single click or keystroke.
No. The Macro Recorder writes the VBA for you based on the actions you perform, so you can record and run useful macros without writing a single line yourself.
Go to File > Options > Customize Ribbon, tick Developer in the list on the right, and click OK. The tab then appears on the ribbon with the macro, code and security tools.
Alt+F8 opens the Macro dialog where you can pick any macro and run it. You can also assign your own Ctrl+Shift key combination when you first record the macro.
The file was almost certainly saved as a standard .xlsx workbook, which cannot store macros. Save as Excel Macro-Enabled Workbook (.xlsm) to keep them.
Absolute recording locks the macro to the exact cell addresses you used, so it always acts on those same cells. Relative recording captures movement instead, so the macro acts relative to whichever cell is selected when you run it.
No. Running a macro clears Excel's undo history, so Ctrl+Z will not reverse it. Always test a new macro on a copy of your workbook.
Choose Personal Macro Workbook when recording. It is a hidden workbook that opens with Excel, making its macros available in every file on that computer.
Macros are real code, so only enable them in files from a source you trust. Excel disables macros by default and shows a yellow notification bar, which is the setting you should leave in place.
Press Alt+F11 to open the Visual Basic Editor, then find your macro under Modules in the project pane. You can read, rename and edit the recorded code there.
Yes. Use Developer > Insert > Button (Form Control) and assign the macro, or right-click any shape and choose Assign Macro for a better-looking custom button.
Every business starts with a spreadsheet. Updoot is where you scale past it, records link themselves automatically.
Start Your Free Trial →