How to Use Excel Macro Recording to Automate Tasks

The Formatting Chore

Every Monday morning, your vendor emails you an inventory report. It is a raw, ugly CSV file. To make it readable for your manager, you perform the exact same ten steps every week: you highlight the top row, make it bold, change the background to dark blue, change the text to white, freeze the top row, auto-fit all the columns, and format column C as currency. It takes you three minutes of mindless clicking.

What if you could press a single button and have Excel perform all ten of those steps instantly, in less than a second?

You can, using a feature called Macro Recording. You do not need to know how to write code. Excel includes a built-in recorder that watches your mouse clicks and keyboard strokes, translates them into Visual Basic for Applications (VBA) code in the background, and saves them as a “Macro” that you can replay infinitely.

Enabling the Developer Tab

By default, Microsoft hides the tools necessary to record macros to avoid confusing casual users. You must unhide them first.

1. Right-click anywhere on the blank grey space of your Excel ribbon (the toolbar at the top).
2. Select Customize the Ribbon…
3. A settings window will open. On the right-hand side, look through the list of Main Tabs.
4. Find the checkbox for Developer and check it.
5. Click OK.

You will now see a new “Developer” tab at the top of your screen. This is your automation control centre.

Recording Your First Macro

Before you hit record, ensure your spreadsheet is open and ready. Do not perform any of your formatting steps yet.

1. Click the Developer tab.
2. On the far left, click the Record Macro button (it has a small icon of a spreadsheet with a red dot).

A setup dialogue box will appear.

• Macro Name: Give it a strict name with no spaces (e.g., FormatInventoryReport).
• Shortcut Key: You can assign a keyboard shortcut to trigger it (e.g., Ctrl + Shift + M).
• Store macro in: Crucial step! By default, it says “This Workbook.” If you choose this, the macro only works on this specific file. If you want the macro available every time you open a new email attachment, change this dropdown to Personal Macro Workbook. This creates a hidden, universal library of macros that loads every time you open Excel.
• Click OK.

Performing the Actions

The moment you clicked OK, the “Record Macro” button changed into a square “Stop Recording” button. Excel is now actively watching everything you do.

Perform your formatting steps exactly as you always do. Highlight the top row. Make it bold. Change the colour. Freeze the panes. Do not rush; the recorder does not record how fast you move, only the logical steps you take. If you make a mistake and undo it, the mistake and the undo will both be recorded in the final code, so try to be precise.

When you have finished all your formatting steps, immediately click Stop Recording in the Developer tab.

Running the Macro

Your robot is now programmed. To test it, open a raw, unformatted version of the inventory report.

1. Go to the Developer tab.
2. Click the Macros button (it has an icon of a spreadsheet with a play symbol).
3. A list of your saved macros will appear. Select FormatInventoryReport.
4. Click Run (or simply press the keyboard shortcut you assigned earlier).

Instantly, the spreadsheet will flash, and all of your formatting rules will appear perfectly applied. Three minutes of boring work is reduced to a single click.

If you perform the exact same sequence of clicks in Excel more than twice a week, you should not be doing it manually. By using the Macro Recorder, you can easily train Excel to automate your most tedious formatting and data-cleaning chores, saving you hours of mindless repetition.