The Repetitive Task Problem
You work in accounting. Every single morning at 9:00 AM, you download a raw CSV file from your company’s banking portal. To make the data readable for your boss, you have to perform the exact same sequence of actions: highlight row 1 and make it bold, change the background color to blue, delete column C because it is useless, format column D as currency, and apply a thick border around the entire table.
This process takes 15 minutes of mindless clicking. Over a year, you spend 60 hours doing the exact same manual formatting. This is a massive waste of human potential.
Microsoft Excel is designed to automate repetitive tasks, and the ultimate tool for this is the Macro. A macro is essentially a tape recorder for your mouse and keyboard. You press record, perform your 15 minutes of formatting once, and press stop. Excel translates your clicks into hidden code (VBA). The next morning, instead of clicking for 15 minutes, you simply press a single button, and Excel instantly replays the recording, formatting the entire document perfectly in a fraction of a second.
Enabling the Developer Tab
Because macros involve running code, Microsoft hides the feature by default to protect beginner users. You must first reveal the Developer tab.
- Right-click anywhere on the empty grey space of your main Excel ribbon at the top of the screen.
- Select Customize the Ribbon…
- A window will open. In the right-hand column (Main Tabs), scroll down and check the box next to Developer.
- Click OK.
You will now see a new “Developer” tab at the top of your screen, next to View and Help.
Recording Your First Macro
Before you hit record, have a clear plan. Excel records every single click, including mistakes, scrolling, and clicking the wrong cell.
- Open your raw, unformatted data.
- Click the Developer tab.
- Click the Record Macro button (it looks like a small spreadsheet with a red dot).
- A dialogue box appears. Give your macro a name with no spaces (e.g.,
DailyFormat). - Optional but recommended: Assign a keyboard shortcut (e.g., CTRL+SHIFT+Q).
- Click OK.
The red dot turns into a blue square. You are now being recorded.
- Perform your actions exactly as you want them repeated. Click Row 1. Make it bold. Make it blue. Delete Column C. Format Column D as currency.
- When you are completely finished, go back to the Developer tab and click Stop Recording.
Running the Macro
The next morning, open your new, raw CSV file. Do not touch the mouse. Instead, press the keyboard shortcut you assigned (CTRL+SHIFT+Q).
You will see the screen flash for a millisecond, and instantly, the bold text, the blue background, the deleted column, and the currency formatting will appear. You just saved 15 minutes of your life.
If you forgot to assign a shortcut, you can always run it manually by clicking the Macros button on the Developer tab, selecting DailyFormat, and clicking Run.
Saving Macro-Enabled Workbooks
There is one crucial catch. A standard Excel file (.xlsx) cannot save macros for security reasons. If you try to save the file, Excel will yell at you and delete your recording.
To save a file containing a macro, you must go to File > Save As, and change the file type from “Excel Workbook” to Excel Macro-Enabled Workbook (*.xlsm).
Stop performing repetitive data entry and formatting. By enabling the Developer tab and learning how to record simple Excel Macros, you can automate hours of tedious, repetitive work and execute massive formatting sequences with a single keystroke.