Macros in Excel: Record, Edit and VBA Basics Guide

A macro in Excel is a recorded sequence of clicks, keystrokes, and commands that Excel plays back on demand. Instead of doing the same routine twelve times a week — formatting headers, copying a range to a new tab, exporting a report to PDF — you record the steps once, save them inside the workbook, and trigger them with a button or a keyboard shortcut.

The recording is stored as VBA code (Visual Basic for Applications), which means once you can record, you can also edit, and once you can edit, you can write macros from scratch that do things the recorder cannot.

People sometimes treat macros and VBA as separate topics. They aren't. Every macro is VBA code; the macro recorder is just the friendliest on-ramp to writing VBA. Hit Record, do the task, hit Stop, and Excel writes the procedure for you. Open the VBA Editor with Alt+F11 and you'll see the lines it generated.

Tidy those lines up a bit, add a loop or a conditional, and suddenly you're automating things that would take a human an entire afternoon. That progression — record, read, refine, write — is how nearly every Excel power user picked up VBA in the first place.

This guide walks the whole path. You'll switch on the Developer tab (it's hidden by default), record your first macro, look at the generated code, save the workbook as .xlsm so the macros survive, deal with the yellow security warning, store macros in the Personal Macro Workbook so they're available in every file you open, and finish with a handful of starter macros most beginners need within their first week.

Nothing here assumes prior coding experience. By the end you'll be comfortable around the Developer ribbon, the Macros dialog, and the VBA Editor — three places where a lot of Excel's hidden horsepower lives.

Before you can record anything, Excel needs to show the Developer tab on the ribbon. Microsoft hides it by default because most casual users never touch it, but it's where the Record Macro, Visual Basic, and Macros buttons live. Go to File → Options → Customize Ribbon.

On the right-hand side you'll see a list of Main Tabs with checkboxes. Tick the box next to Developer, click OK, and the tab appears between View and Help. Quick alternative on Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer. The setting persists across sessions, so you only do this once per machine.

Now have a look at what you just unlocked. The Developer tab has four groups worth knowing about. Code holds Visual Basic, Macros, Record Macro, Use Relative References, and Macro Security. Add-ins lets you manage Excel and COM add-ins. Controls puts form controls and ActiveX controls onto your worksheets — checkboxes, buttons, dropdown lists tied to macros.

XML and Modify cover schema-mapped XML data and document-protection workflows. For your first month of macros you'll mostly live in the Code group. Everything else is there when you need it, but Record Macro and Visual Basic are the two buttons you'll click a hundred times before you click anything else.

Two ribbon settings inside the Developer tab deserve a quick mention. The first is Use Relative References. With it off (the default), a recorded macro that moves down three cells will always move to those exact cells — A4, A5, A6 — no matter where you started. With it on, the macro moves three cells relative to whatever cell is selected when you press play.

That distinction is the single biggest source of "why didn't my macro work this time?" confusion. The second setting is Macro Security, which opens the Trust Center. By default, Excel disables all macros from files you didn't create, with a yellow notification bar offering Enable Content. We'll come back to that in detail.

Recording your first macro takes under a minute. On the Developer tab, click Record Macro. A small dialog appears asking four things: macro name, shortcut key, store-in location, and description. The name can't have spaces (use underscores or camelCase — Format_Headers or FormatHeaders both work) and can't start with a number.

The shortcut key is optional but handy: pick something like Ctrl+Shift+H and Excel will run the macro every time you press it. Store in lets you choose This Workbook (the macro lives inside the current file), New Workbook (Excel creates a new file for it), or Personal Macro Workbook (more on that shortly). For now, leave it on This Workbook. Click OK and the recorder is rolling.

Everything you do from this point is being saved as VBA code. Select cell A1, type "Quarter", press Tab, type "Revenue", press Tab, type "Profit". Select A1:C1, change the font to bold, fill the row with light gray, center-align the text. Click any cell to deselect, then hit Stop Recording on the Developer tab (or the small stop button at the bottom-left of the status bar). Your macro is done. Press Alt+F8 to open the Macros dialog, select your macro, click Run, and the same thing happens again on a fresh worksheet. That's the entire loop: record, stop, run.

Two recording tips save people a lot of frustration later. First, plan the steps before you press Record. Mentally rehearse the click sequence — open the file, select the range, copy, paste-special-values, save as PDF — so you don't record yourself fumbling around in menus. Excel captures the fumbling too, and your macro ends up doing extra work it doesn't need to do.

Second, decide up front whether the macro needs to start from a fixed cell (absolute) or from wherever the cursor happens to be (relative). Toggle Use Relative References on the Developer tab before recording if you want the relative behavior. You can't change that decision after the fact — you have to re-record.

Press Alt+F11 and the VBA Editor opens in a separate window. On the left, the Project Explorer shows a tree of every open workbook. Expand your file, find the Modules folder, and double-click Module1. The center pane fills with the code Excel generated when you recorded.

It will look something like Sub FormatHeaders() followed by a few lines of Range and Selection commands and ending with End Sub. Don't worry about understanding every line yet — the goal of the first read-through is just recognizing that the recorder produced ordinary text-based code, which means anything text-based code can do, you can edit by hand.

Try one small edit. Find a line with a specific cell reference like Range("A1:C1").Select and look at what comes after it. If the next line says Selection.Font.Bold = True, you can compress those two lines into Range("A1:C1").Font.Bold = True — one line, same result, no need to Select first. The recorder writes a lot of Select-then-act pairs because it's mimicking your mouse, but well-written VBA usually skips the Select step entirely. Close the editor and run the macro again with Alt+F8 → Run. Same outcome, slightly cleaner code. You just did your first round of VBA cleanup.

Now save the file. If you try to save with the standard Excel Workbook (.xlsx) format, you'll get a warning: "The following features cannot be saved in macro-free workbooks." That's because .xlsx is, by design, macro-free — saving in that format strips out any VBA. You need the macro-enabled format, .xlsm.

Click File → Save As, choose Excel Macro-Enabled Workbook (*.xlsm) from the file-type dropdown, and save. The two formats look identical inside Excel but only .xlsm retains the code. There's also .xlsb (binary, macro-enabled, smaller and faster for huge files) and .xltm (macro-enabled template). For everyday work, .xlsm is the format you want.

One more file-format note: never email someone an .xlsx when you meant to send macros. The macros are gone the moment you save in the wrong format, with no warning beyond the dialog. If you've been working in .xlsx and you've just recorded a macro, save-as .xlsm immediately. Otherwise the next save (or autosave) silently destroys everything you just recorded. This is one of the most common first-week macro mistakes — people record a macro, save, close, reopen, and the macro is missing.

Open a macro-enabled workbook that you didn't create yourself and Excel does something deliberately cautious: it disables all the macros and shows a yellow bar near the top of the window that reads "SECURITY WARNING — Macros have been disabled." Next to it is an Enable Content button. This is Microsoft's macro-security model in action.

The yellow bar exists because macros — being arbitrary code — can do anything Excel can do, including delete files, send emails, and exfiltrate data. Disabling them by default and asking for explicit user consent is the right safety stance for files arriving from anywhere outside the user's own machine.

Click Enable Content once and Excel remembers your decision for that specific file. Open it again next week and the warning won't reappear. The trust scope is per-file plus per-location plus per-publisher, governed by Trust Center settings reachable through File → Options → Trust Center → Trust Center Settings → Macro Settings.

The four standard options range from "Disable all macros without notification" (most restrictive, no yellow bar — macros just silently don't run) through the default "Disable all macros with notification" (yellow bar, click to enable) up to "Enable all macros" (most permissive, not recommended). A separate setting under Trusted Locations lets you whitelist whole folders. Files saved there bypass the warning entirely — useful for a Macros folder where you keep tools you've built or audited yourself.

One detail catches people out: even if you click Enable Content, the macros only run for that session if the file is in the Downloads folder, network share, or another location marked as "from the internet." Files Excel considers internet-sourced get a Mark of the Web stamp, which forces stricter handling.

Move the file to a Trusted Location, or right-click → Properties → Unblock at the bottom of the General tab, and the file behaves like a local document. Without that step, you'll click Enable Content and the macros will still be blocked because Microsoft is double-checking that you really meant it.

The Personal Macro Workbook solves the most common second-week macro problem: "I built a useful macro inside Workbook A, but I want to run it inside Workbook B." Macros saved in This Workbook are locked to that file. The fix is the Personal Macro Workbook, a hidden file called PERSONAL.XLSB that Excel auto-loads in the background every time it starts. Anything stored there is available in every workbook you have open. It's the right home for tools you want everywhere — format-headers, copy-as-values, export-to-PDF, jump-to-formulas-view, anything you'd reach for in any file.

Creating it is a one-time step. On the Developer tab, click Record Macro. In the Store macro in dropdown, pick Personal Macro Workbook (it's the third option). Give the macro any throwaway name like Init, click OK, click anywhere in a cell, then click Stop Recording. Excel quietly creates PERSONAL.XLSB in your XLSTART folder and stores the throwaway macro inside it.

Now press Alt+F11 to open the VBA Editor and look at the Project Explorer on the left — you'll see VBAProject (PERSONAL.XLSB) listed alongside your current workbook. Expand it, find Module1, and your Init macro is there. From now on, any macro you record with Store in: Personal Macro Workbook lands in that same file.

The first time you close Excel after adding macros to PERSONAL.XLSB, Excel will ask whether you want to save the changes. Always say yes. The file lives at %appdata%\Microsoft\Excel\XLSTART\PERSONAL.XLSB on Windows and inside ~/Library/Group Containers/UBF8T346G9.Office/User Content.localized/Startup.localized/Excel/ on Mac. You don't normally open it directly — Excel handles that. But if you ever switch machines, that file is the one to copy over: drop it into the equivalent XLSTART folder on the new machine and every macro you've ever stored personally comes with you.

A handful of small, practical macros cover most of what new users actually want to automate. Format Headers is the classic first macro: select a header row, run the macro, the row turns bold, fills with a light color, and centers the text. Record it once in your Personal Macro Workbook with a shortcut like Ctrl+Shift+H and you'll use it constantly.

Copy Range as Values is another high-frequency tool: copy a range with formulas, paste-special-values into the same location, removing the formulas while preserving the displayed numbers. Useful before sending a file to someone who shouldn't see (or might break) the calculations.

A third beginner-friendly macro is Save As PDF. Record yourself clicking File → Export → Create PDF/XPS → choose location → save. The recorded macro becomes a one-click export with a fixed filename pattern. Light editing in the VBA Editor lets you make the filename include today's date, the active sheet name, or a value from a specific cell — turning the macro from a literal repeat-this into a smart-this.

AutoFit All Columns is even simpler: select all cells, double-click any column border (or use Format → AutoFit Column Width), stop recording. Now any sheet with cramped columns is one shortcut away from readable.

For people who do a lot of report cleanup, two more macros pay off quickly. Delete Blank Rows sweeps through a selection and removes any row where the cells are empty — a single-keystroke replacement for the Filter → Blanks → Delete dance. Trim All Cells applies the TRIM function to every selected cell and replaces the result, killing the leading/trailing spaces that often sneak in from external data. None of these require writing code from scratch. Each is a 30-second recording, a 60-second cleanup in the VBA Editor, and a permanent addition to your Personal Macro Workbook.

Once recording feels comfortable, two skills unlock most of what people imagine when they hear "Excel automation." The first is using variables and loops to do something many times. For i = 2 To 100 followed by Cells(i, 5) = Cells(i, 3) * Cells(i, 4) and Next i writes the product of columns C and D into column E for rows 2 through 100.

That's three lines of VBA replacing what would otherwise be a hundred clicks or a fragile formula. The recorder will never generate a loop — you have to write it — but the syntax is forgiving, the errors are obvious, and once you've written one loop you can write a hundred.

The second is conditional logic with If...Then...End If. If Cells(i, 5) > 1000 Then Cells(i, 6) = "High" lets the macro decide what to do based on data. Chain a loop and an If together and you have a sorting/flagging tool that runs across an entire dataset in under a second. Add MsgBox for user feedback (MsgBox "Processed 47 rows") and InputBox for asking the user a question, and you have macros that feel like little applications instead of repeating-clicks tools.

The transition from recorded macros to written macros happens gradually. Start by editing the code Excel recorded — remove a Select line here, tighten a range reference there. Move on to combining two recordings into one Sub. Then write a small Sub by hand using lines you've seen in recordings. Within a few weeks you'll be writing macros from scratch that the recorder couldn't produce because the recorder doesn't know about your specific workflow. That's the destination: VBA as a personal toolset, your tools, doing your work the way you want it done.

Macros change the relationship between you and Excel. Before macros, Excel is a place where you do things by hand and the same task takes about the same time every time. After macros, repetitive things stop being repetitive — you record them once, save them in the Personal Macro Workbook, bind them to a shortcut, and the time savings compound across every week you use Excel.

People who learn macros usually report the same arc: the first one feels magical, the next ten feel routine, and within a month they're recording macros for things they didn't even realize were taking up time.

Three habits keep the macro experience smooth long-term. Save macro-heavy files as .xlsm from the start; saving as .xlsx silently destroys your VBA. Read your recorded code at least once — even if you don't understand every line, you'll catch the recorder being inefficient and you'll absorb VBA syntax through exposure.

Treat the Personal Macro Workbook as your toolbelt; anything you'd want in every workbook lives there, not in the file you happened to be working in when you recorded it. Three small habits, applied from day one, and macros stop being a thing you have to remember to maintain.

If you came to this guide hoping macros would feel less mysterious by the end, that's the goal: the recorder is friendly, the editor is just a text window, the file format is a checkbox, and the security warning is one click. None of it is hard once you've done each step once. The hard part is committing to record the next repetitive task instead of doing it by hand again. Do that this week — one macro, one .xlsm save, one Personal Macro Workbook entry — and Excel starts feeling noticeably faster within days.