The short answer: a macro in Excel is a saved sequence of actions that Excel can replay on command. You create one either by recording what you do (Excel writes the code for you) or by writing that code yourself in VBA, the programming language built into Office. Macros exist to remove repetition: if you perform the same fifteen clicks on a file every week, a macro does them in one. The workbook has to be saved as .xlsm to keep the macro, and Excel disables macros by default until you tell it the file is trustworthy.
That definition covers the exam answer, but it leaves out the parts that decide whether macros are actually worth your time. The recorder is genuinely usable by people who have never programmed. It is also happy to record something that works perfectly today and breaks the moment your data has one more row. Knowing which is which is most of the skill.
What is a macro in Excel used for?
Macros are for work that is repetitive, mechanical, and identical every time. The classic cases are formatting and cleanup: you receive a file in the same broken shape every month, and every month you delete the same three columns, widen the same headers, format the same amounts as currency, and freeze the top row. That is perhaps a two minute job done by hand and a two second job done by macro, which sounds trivial until you multiply it by fifty files.
The pattern shows up constantly in finance work. Anyone who pulls data out of a system as a report and then has to make it usable is doing the same corrections repeatedly. Converted documents are a good example: if you regularly convert PDFs to Excel, the cleanup afterwards tends to follow a fixed routine, and a routine is exactly what a macro is good at. Our guide to cleaning up data in Excel after a PDF conversion walks through those steps manually first, which is the right order: get the sequence correct by hand, then record it.
What macros are not good for is judgment. If the task involves deciding something (is this row a duplicate or a genuine second transaction?), a macro will apply whatever rule you recorded and apply it wrongly the moment reality varies. Automate the mechanical part and keep the decision for yourself.
How do I record a macro in Excel?
Recording is the entry point and needs no code at all. Excel watches what you do and writes the equivalent VBA behind the scenes.
First, turn on the Developer tab if you do not have it: File > Options > Customize Ribbon, then tick Developer in the right hand list. You can also record from the View tab, which has a Macros button, so the Developer tab is convenient rather than essential.
Then the sequence itself:
- Do a dry run first. Perform the whole task manually once and note every step. The recorder captures mistakes and stray clicks just as faithfully as useful actions.
- Click Record Macro on the Developer or View tab. Give it a name with no spaces (Excel will refuse spaces), and optionally a shortcut key.
- Choose where to store it. This Workbook keeps the macro in the current file. Personal Macro Workbook makes it available in every workbook you open on that machine, which is what you want for general cleanup routines.
- Perform the steps, calmly and in order.
- Click Stop Recording. The macro now exists, and you can run it from Macros on either tab.
One setting deserves more attention than it usually gets: Use Relative References, a toggle next to the record button. Leave it off and the macro remembers exact cell addresses, so a macro recorded on cell B2 will always act on B2. Turn it on and the macro remembers movement instead, so it acts relative to wherever the cursor happens to be. Recording with absolute references and then running the macro somewhere else is the single most common reason a recorded macro does something baffling.
What can a macro in Excel do?
Almost anything you can do through the interface, plus a good deal you cannot. The honest split looks like this.
| Task | Recorder handles it well | Why |
|---|---|---|
| Formatting, column widths, freezing panes | Yes | Fixed actions with no conditions |
| Deleting or reordering known columns | Yes | The structure is the same every time |
| Applying a filter or sort you always apply | Yes | Same parameters each run |
| Working on a range that changes size | Not reliably | The recorder writes the range it saw, not the range that exists next time |
| Anything conditional (if blank, then skip) | No | The recorder cannot capture a decision, only an action |
| Looping over every file in a folder | No | Requires written VBA |
The bottom rows are where people graduate from recording to editing. A useful halfway house is to record something close to what you want, then open the Visual Basic Editor with Alt+F11 and change the one line that hard-codes a range. You do not need to understand the whole language to make that edit, and it turns a fragile macro into a durable one.
What is the difference between a macro and VBA?
A macro is the saved routine. VBA, which stands for Visual Basic for Applications, is the language that routine is written in. Every macro is VBA code, whether you typed it or the recorder wrote it for you. Saying "a macro" describes what it does for you, and saying "VBA" describes what it is made of.
The distinction matters mostly when you go looking for help. Search for macros and you get recorder tutorials. Search for VBA and you get the language, its objects and its syntax. If you have recorded something and now want it to behave differently under different conditions, VBA is the thing you are about to learn.
What is the difference between a script and a macro in Excel?
Office Scripts are the newer, web-friendly equivalent of macros, written in TypeScript rather than VBA, and they run in Excel for the web as well as the desktop app on Microsoft 365. VBA macros are the long-standing desktop technology and do not run in Excel for the web at all.
The practical rule: if your organization lives in the browser version of Excel, or you want automation that also works through Power Automate, Office Scripts are the direction of travel. If you are on desktop Excel, need to work offline, or are maintaining anything that already exists in a company, VBA is what you will meet. VBA is not going away, but Microsoft's investment is clearly on the Scripts side.
What is a macro enabled Excel workbook?
It is a workbook saved in a format that is allowed to store macros, and it exists because of a security decision. The standard .xlsx format deliberately cannot contain macro code. Save a workbook with macros as .xlsx and Excel will warn you, and if you continue, the macros are stripped out. This surprises people regularly: the file opens fine, and the automation has silently vanished.
| Extension | Stores macros | Use it when |
|---|---|---|
| .xlsx | No | Normal workbooks. The safe default for sharing |
| .xlsm | Yes | A workbook that contains macros you want to keep |
| .xlsb | Yes | Large workbooks, saved in binary for speed and size |
| .xltm | Yes | A template that should pass its macros on to new files |
The visible difference between .xlsx and .xlsm is exactly this one capability, which is why a file arriving as .xlsm is a signal to a recipient that code is inside it. If the format question is what brought you here, our comparison of XLS and XLSX covers how these formats differ more broadly.
Why are my macros disabled in Excel?
Because Excel assumes a macro from outside your organization is hostile until proven otherwise, and that assumption got considerably stricter a few years ago. There are two separate mechanisms, and confusing them wastes a lot of time.
The first is your macro setting, at File > Options > Trust Center > Trust Center Settings > Macro Settings. The sensible choice for most people is Disable VBA macros with notification, which blocks macros but shows a Message Bar so you can enable them for a file you recognize.
The second is the Mark of the Web, and this is the one that catches people out. Files downloaded from the internet, received by email, or pulled from SharePoint, OneDrive or Teams carry a hidden tag recording that they came from outside. Microsoft blocks VBA macros in tagged files by default, and when that block applies you get a red banner saying the source of the file is untrusted, with no Enable Content button to click. Changing your Trust Center setting does not help, because the block is not coming from there.
| Symptom | Cause | Fix |
|---|---|---|
| Yellow bar offering Enable Content | Normal macro notification | Click Enable Content if you trust the sender |
| Red bar, source of this file is untrusted | Mark of the Web on a downloaded file | Close Excel, right click the file, Properties, tick Unblock, reopen |
| No banner and nothing runs | Macros disabled without notification | Change the Trust Center macro setting to disable with notification |
| Macro vanished after saving | Workbook saved as .xlsx | Save as .xlsm and re-create the macro |
| Macros button greyed out | Workbook protected or opened read only | Unprotect the workbook or open a writable copy |
For a folder of files you use constantly, the tidier answer than unblocking each one is a Trusted Location, also in the Trust Center. Files opened from a folder you have designated as trusted skip the block entirely. Designate a specific working folder rather than an entire drive.
Can you undo a macro in Excel?
No. Running a macro clears the undo history, so Ctrl+Z will not reverse it and cannot recover the state before it ran. This is the single most important practical fact about macros and it is the one most tutorials mention last, if at all.
The habit that follows is simple: save before you run a macro for the first time, or test it on a copy of the file. If a recorded macro deletes the wrong column across 4,000 rows, your only route back is the last saved version. Excel's AutoRecover will not help, because nothing crashed.
Are Excel macros safe?
A macro you recorded yourself is as safe as the actions you recorded. A macro in a file someone sent you is code that runs on your machine, and VBA is capable of far more than editing a spreadsheet, which is why macro-laden attachments were a favorite delivery method for malware for years and why Microsoft now blocks them from the internet by default.
The workable rules: enable macros in files you created or that came from a colleague who told you they were sending one; never enable them in an unexpected attachment, however plausible the covering message; and if you need to inspect a file, press Alt+F11 to read the code before running anything. Code you cannot read is a good reason not to proceed.
Do I need to know how to code to use macros?
Not to start. The recorder is designed for people who have never written a line of anything, and a formatting routine recorded in three minutes will save real time without you ever opening the editor. Most people who use macros regularly never write VBA from scratch.
You will hit the ceiling eventually, and it usually arrives in one specific form: your data changes size. A macro recorded on 200 rows will still be acting on 200 rows when the file has 900, because the recorder wrote the range it saw. Fixing that is a one line change once someone shows you the pattern, and it is the moment most people learn just enough VBA to be dangerous in a good way.
When a macro is the wrong tool
Macros automate steps inside a workbook. They are a poor answer to two adjacent problems that look similar.
The first is bad input. If your monthly file arrives with amounts stored as text, merged header cells and blank rows scattered through it, a macro that patches all that up every month is treating a symptom. It is usually better to fix the source: get a cleaner export, or use a converter that keeps numbers numeric in the first place so you are not writing code to undo the damage. That is the reasoning behind our notes on converting text to numbers and on removing blank rows after a conversion. If the files themselves are the bottleneck, a batch converter deals with a folder of documents in one pass, which no amount of VBA inside a single workbook can do for you.
The second is moving data between systems. Plenty of macros exist only to reshape an export from one application so another can ingest it, and that is a brittle way to solve an integration problem. When the repetitive work is really about keeping apps, APIs and databases in sync rather than reformatting a sheet, the macro is a workaround for a pipe that should exist. And when the goal is that documents flow into an accounting system without anyone opening Excel at all, that is a different category of product again, which is what our comparison of data entry automation software and our Nanonets alternative page get into.
Used for what it is good at, though, a macro remains one of the highest-return things a spreadsheet user can learn. The barrier is genuinely low, the payoff arrives the first time you run it, and you can go a long way before you ever need to understand what the code actually says. Start with a task you do every week, record it carefully on a copy of the file, and go from there. If you want another safeguard on the same data, data validation catches bad entries before a macro ever touches them.