Never do the same thing again: How to record a macro in Excel

Think of the macro recorder as an audio recording of your actions in Excel.

• You click “Record”: Excel begins logging every single click, keystroke, and formatting action you perform.
• You perform your work: You complete your repetitive task exactly as you always do.
• You click “Stop”: Excel stops recording and saves the sequence of steps as a small program (in the “VBA” language – Visual Basic for Applications).

From now on, you can play back this “recording” at any time to perform the exact same sequence of actions on a different dataset.

By default, the macro control center in Excel is hidden. We need to make it visible first. You only need to do this once.

• Click on “File” and then on “Options” at the very bottom.
• In the new window, go to “Customize Ribbon”.
• On the right, you’ll see a list of main tabs. Check the box next to “Developer”.
• Click “OK”.

You now have a new tab called “Developer” in your ribbon. This is our command center. You can find more details here.

Let’s imagine we receive a raw data list every month. She always looks like this:

Our recurring task is to format this list as follows:

• The header row (row 1) should be bold with a light blue background.
• The column widths should automatically adjust to the content.
• A filter should be applied to the header row.
• The “Sales” column should be formatted as currency (€).

Now things get interesting. Open your raw sample file.

Starting position: Click in any cell of your spreadsheet, ideally A1.

Start recording:

• Switch to the new “Developer” tab.
• Click the “Record Macro” button.
• Fill out the dialog box:
• Macro name: Enter a descriptive name without spaces or special characters, e.g., Report_Format.
• Keyboard shortcut: You can define a shortcut here. Pro tip: Use Shift+F to avoid overriding default shortcuts. For example, enter Ctrl+Shift+F (for Format).
• Save macro in: For starters, select “ThisWorkbook”. (The “Personal Macro Workbook” option saves the macro globally, making it available in every Excel file—a topic for advanced users.)
• Description: Briefly write what the macro does. For example: “Formats the monthly raw data report.”
• Execute actions (Recording now!):
• Click OK. From now on, every click will be recorded! Work calmly and thoughtfully.
• Step A: Select the entire row 1 by clicking on the “1” at the beginning of the row.
• Step B: Go to the “Home” tab, click on Bold, and select a light blue fill color.
• Step C: Select columns A through D by clicking on the letter “A,” holding down the mouse button, and dragging to “D.”
• Step D: Double-click on the dividing line between column headers D and E. The columns will align perfectly.
• Step E: Click in any cell of the header (e.g., A1). Go to the “Data” tab and click on “Filter.”
• Step F: Select column D, “Sales,” by clicking on the column header “D.” Go to the “Home” tab and select “Currency” from the number format dropdown menu.

Stop recording (Very important!):

• Go back to the “Developer” tab.
• Click “Stop Recording”.

Congratulations! You have just created your first macro.

To experience the magic, undo the formatting or take a fresh copy of the raw data.

Option 1 (The Shortcut): Press your custom keyboard shortcut: Ctrl + Shift + F. Watch as Excel performs all the steps for you in less than a second.

Option 2 (Via the Menu): Go to “Developer” -> “Macros”. Select your macro, Report_Format, from the list and click “Run”.

This is a classic beginner’s mistake. A regular Excel file (.xlsx) cannot save macros.

• Go to “File” -> “Save As”.
• Under “Save as type”, select “Excel Macro-Enabled Workbook (*.xlsm)”.

This is the only way to ensure your recorded macro is preserved for future use.

Conclusion

Today you’ve taken the first step from being a passive Excel user to an active creator. Macros aren’t rocket science, but rather an incredibly powerful tool for saving time, reducing errors, and eliminating tedious routine tasks.

Now think about your daily work routine: What click sequences do you repeat over and over again? That’s your next candidate for a macro!