Excel Macros for Accounting: A Gentle Intro

Q: What is the Personal Macro Workbook?

A hidden workbook (`PERSONAL.XLSB`) that Excel opens automatically every time it starts. Macros stored here are available in every workbook you open. Useful for general-purpose macros (format, print settings) that you want to use regardless of which file is open.

Last updated: 27 June 2026

A macro is a recorded sequence of Excel steps that replays with one click — no coding required. For a small business owner who formats the same invoice, copies the same monthly data, or prints the same report every week, a single macro turns a 10-minute routine into a 3-second button press. This guide shows you how to record, save and run your first useful accounting macro.

Key takeaways

Excel's Macro Recorder captures your clicks and keystrokes and converts them into a reusable button — you never see code unless you want to.
The most useful macros for small business: format-and-print invoice, copy this month to archive, apply standard headers/footers, reset a template to blank.
Save macro-enabled workbooks as .xlsm (not .xlsx) — Excel will warn you if you forget.
Macros are stored per workbook by default; store in "Personal Macro Workbook" if you want them available across all files.
Macros do not require Microsoft 365 — they work in Excel 2010 and later.
Security: only enable macros in workbooks you created or trust. Disable "Enable all macros" in settings; use "Enable all macros with notification" instead.

Fact box. You do not need to know Visual Basic for Applications (VBA) to use macros. The Macro Recorder writes all the code for you. Most small-business accounting tasks — formatting, printing, copying data between sheets — are fully recordable without touching a single line of code.

What is a macro and why should an accountant care?

A macro is a saved sequence of Excel actions. When you record a macro, Excel watches what you do and stores each step. When you run the macro later, Excel replays every step instantly.

For accounting work, that means:

You format a GST invoice the same way every time — different amounts, same layout? One button.
You archive last month's transactions by copying them to a new sheet — manually that takes 5 minutes. A macro takes 3 seconds.
You print a payslip with specific page settings every pay day — record it once, run it every month.

The payoff is not just time. It is consistency. A macro applies exactly the same steps every time, which means fewer formatting mistakes, fewer missed print settings, and a cleaner audit trail.

How do I enable the Developer tab?

The Macro Recorder lives on the Developer tab, which Excel hides by default.

Steps to show the Developer tab:

Click File → Options → Customize Ribbon.
In the right panel, check the box next to Developer.
Click OK.

The Developer tab now appears in your ribbon between View and Help.

How do I record my first macro?

Here is a practical example: recording a macro that formats an invoice sheet for printing.

Before you record, set up the exact state you want. For a print macro, have the sheet open and unformatted, ready to receive the steps.

Steps:

Go to Developer → Record Macro.
Name the macro — no spaces, use underscores: Format_Invoice_Print.
Assign a shortcut key if you like (e.g. Ctrl+Shift+P).
Choose where to store: This Workbook (just this file) or Personal Macro Workbook (available in all files).
Add a short description: "Formats invoice sheet and sets print area to A1:I40".
Click OK — recording starts.
Perform every step: set column widths, bold the header row, set the print area (Page Layout → Print Area → Set Print Area), set orientation to Landscape, set margins.
Click Developer → Stop Recording.

Your macro is saved. Test it: press Ctrl+Shift+P (or go to Developer → Macros → Run). Excel replays every step in under 2 seconds.

Fact box. A macro recorded for formatting and printing an invoice typically runs in under 3 seconds what a trained user takes 8–12 minutes to do manually — header formatting, column widths, print area, orientation, margins. Over 20 invoices a month, that is 2–4 hours saved.

What are the most useful macros for small-business accounting?

Here are five macros worth recording for any Indian SMB:

Macro name	What it does	Time saved
`Format_Invoice_Print`	Formats and prints the active invoice	8–12 min → 3 sec
`Archive_Month_Data`	Copies this month's rows to a new sheet named by month	5–10 min → 5 sec
`Reset_Invoice_Template`	Clears variable cells (amounts, dates, customer) and resets to blank	2–3 min → 2 sec
`Apply_Standard_Headers`	Adds company name, GSTIN and page numbers to every sheet's header	5 min → 3 sec
`Highlight_Unpaid_Invoices`	Applies red fill to all rows where the Status column says "Unpaid"	3–5 min → 2 sec

Start with the one that wastes the most of your time each week. Record it, test it, then build the rest.

How do I run a macro?

Three ways, from fastest to slowest:

Keyboard shortcut — if you assigned one during recording, press it any time.
Quick Access Toolbar — right-click the macro in Developer → Macros → Options → add to QAT. A button appears at the very top of Excel.
Developer → Macros → select → Run — the full menu route, useful when you have many macros and want to pick carefully.

For daily use, assign a shortcut or add the macro to a button on the sheet itself (Developer → Insert → Button → assign macro).

How do I edit or fix a recorded macro?

Go to Developer → Macros → select your macro → Edit. This opens the VBA editor showing the recorded code.

Common small fixes you can make even without knowing VBA:

Change a sheet name: find the old name in quotes ("January") and type the new one ("April").
Change a range: find Range("A1:I40") and update the column or row reference.
Add a message box: type MsgBox "Invoice formatted and ready to print." at the end of the macro — Excel shows that message when the macro finishes.

Do not delete lines you do not understand. Make one change, save, and test.

How do I save a macro-enabled workbook?

When you close a workbook containing macros, Excel asks whether to save with macro support.

Choose Yes and save as .xlsm (Excel Macro-Enabled Workbook).
.xlsx format strips all macros. If your file is saved as .xlsx, the macros are gone.

Rename your working accounting file: FY2026-27-Business.xlsm. If you share a read-only copy with your CA (who does not need the macros), export or save-as .xlsx for that copy.

Are macros safe? What should I watch out for?

Macros are safe if you follow one rule: only enable macros in files you created or received from someone you trust.

Malicious macros (macro viruses) are real but rare. They arrive inside .xlsm files from unknown sources. Excel's default security setting shows a yellow bar asking "Enable content?" — click Enable only for your own files.

Recommended security setting: File → Options → Trust Center → Trust Center Settings → Macro Settings → "Disable all macros with notification". This is the default and the right setting. Do not change it to "Enable all macros".

How Ankeshan helps: Ankeshan includes pre-built automation for the most common accounting tasks — invoice formatting, GST summary generation and month-end archiving — all as one-click actions inside Excel, without needing to record anything. It is launching soon; join the waitlist.

Frequently asked questions

Do I need to know programming to use Excel macros? No. The Macro Recorder writes all the code for you. You perform the actions; Excel records them. You only need to look at code if you want to edit a step — and even then, small edits are straightforward.

Why won't my macro run on someone else's computer? The most common reason is macro security settings. The other person's Excel is set to block macros by default. They need to click "Enable Content" when they open the file, or you need to share it as a trusted file. If the macro references a sheet name or path that does not exist on their machine, it will also fail.

Can I record a macro that works across multiple sheets? Yes. While recording, simply click onto another sheet and perform steps there. Excel records the sheet switches as part of the macro. Test carefully — if the sheet names differ on another machine, update the macro.

What is the Personal Macro Workbook? A hidden workbook (PERSONAL.XLSB) that Excel opens automatically every time it starts. Macros stored here are available in every workbook you open. Useful for general-purpose macros (format, print settings) that you want to use regardless of which file is open.

My macro ran but did not do what I expected — how do I fix it? Open the VBA editor (Developer → Macros → Edit) and step through line by line using the F8 key. Each press executes one line and highlights the next. You can see exactly where it goes wrong.

Sources

Microsoft Support — Record or run a macro (support.microsoft.com)
Microsoft Support — Macro security settings in Excel

General information only, not professional advice. Reviewed by a Chartered Accountant; last updated 27 June 2026.