Automation›Pro· 45 min read

Automate Repetitive Work: Macros & VBA

Record a sequence of clicks once and replay it with a button — then peek at the VBA code Excel wrote, so a daily 10-minute clean-up becomes a 1-second job.

What you will learn

Explain what a macro is and when to use one
Record, run and save a macro
Read and lightly edit the VBA code behind it

Teach Excel to repeat your steps

A macro is a saved recording of the actions you take in Excel — clicks, typing, formatting — that you can replay any time with one button. If you do the same clean-up every Monday (delete a column, format some cells, add a total), a macro does it all in an instant, exactly the same way each time. This is automation: you teach Excel the steps once, and it repeats them perfectly forever.

Behind every macro is a little program written in VBA (Visual Basic for Applications), Excel’s built-in programming language. The wonderful part for a beginner is that Excel writes the VBA for you as you record — so you can automate real work before you understand a single line of code, then learn to read it gradually.

Watch out: Macros live in Excel for Windows and Mac desktop, not in Google Sheets (which uses a different language). A file with macros must be saved as a special .xlsm workbook, and you should only enable macros in files you trust — see the security note at the end.

Recording your first macro

Imagine you receive a sales sheet each week and always do the same three things: make the header row bold, widen the columns, and add a SUM total at the bottom. Let us record that once. The macro recorder is on the View tab (or the Developer tab) under Macros > Record Macro.

Click View > Macros > Record Macro. A dialog opens.
Name it FormatReport (names cannot contain spaces), optionally set a shortcut key, and press OK. Excel is now recording every action.
Do your steps normally: select the header row and click Bold; select the columns and AutoFit their width; click the total cell and type a =SUM(...).
Click View > Macros > Stop Recording. Your steps are now saved as the macro FormatReport.

Note: Output: Nothing visibly changes yet — but Excel has quietly written a VBA program capturing each action. Next week you run the macro and all three steps happen at once.

Running the macro

To replay it, open a fresh sheet, then choose View > Macros > View Macros, pick FormatReport, and click Run. Every recorded step happens in a blink. If you set a shortcut key (say Ctrl + Shift + F), pressing it runs the macro instantly.

Replay all the recorded formatting steps with one click

View  >  Macros  >  View Macros  >  FormatReport  >  Run

Note: Output: The header row turns bold, the columns resize to fit, and a SUM total appears at the bottom — instantly. A task that took a careful minute by hand now takes a fraction of a second, identically every time.

Looking at the VBA Excel wrote

Now the fun part: see the code. Press Alt + F11 (or Developer > Visual Basic) to open the VBA Editor, then double-click your module to read what was recorded. It looks something like this — and you can almost read it as English:

The VBA program the recorder wrote for your three steps

Sub FormatReport()
    Rows("1:1").Font.Bold = True
    Columns("A:D").AutoFit
    Range("B10").Formula = "=SUM(B2:B9)"
End Sub

Note: Output: Running this Sub does exactly what you recorded. Line by line: - Sub FormatReport() starts the macro (a “Sub” is a block of actions) and End Sub ends it. - Rows("1:1").Font.Bold = True makes row 1 bold. - Columns("A:D").AutoFit widens columns A to D to fit their contents. - Range("B10").Formula = "=SUM(B2:B9)" puts a SUM total in B10. Each line names a thing (a row, some columns, a cell) and sets a property or calls an action on it — that is the whole pattern of VBA.

A tiny edit by hand

Once you can read it, small edits are easy and powerful. Say you want the header text coloured as well as bold. You do not re-record — you just add one line. ColorIndex = 3 is VBA’s code for red:

Added one line to also colour the header row red

Sub FormatReport()
    Rows("1:1").Font.Bold = True
    Rows("1:1").Font.ColorIndex = 3
    Columns("A:D").AutoFit
    Range("B10").Formula = "=SUM(B2:B9)"
End Sub

Note: Output: Now running the macro makes the header row bold AND red, then resizes and totals as before. You extended a recorded macro with a single typed line — the first step from recording macros to genuinely writing them.

Term	Plain meaning
Macro	A saved, replayable recording of your actions
VBA	The programming language a macro is written in
Sub	A named block of VBA actions you can run
.xlsm	The file type that can store macros
VBA Editor	The window (Alt+F11) where you read/edit the code

Tip: The best way to learn VBA is to record a macro, then read what Excel wrote. Want to know the code for some action? Record yourself doing it once and inspect the result. It is a free, personalised VBA tutor built into Excel.

Watch out: Macros can run powerful actions, so they are a security target. Only enable macros in files from people you trust, keep Excel’s Trust Center macro warnings on, and save your own macro files as .xlsm so colleagues are warned they contain code.

Q. What is the simplest way to get the VBA code for a sequence of Excel actions when you are a beginner?

Answer: The macro recorder writes correct VBA for you as you click. Recording the steps and then reading the generated code in the VBA Editor (Alt+F11) is the easiest way to both automate work and start learning VBA.

✍️ Practice

Record a macro called FormatReport that makes the header row bold and autofits the columns, then run it on a fresh sheet.
Open the VBA Editor (Alt+F11), read the generated Sub, and add one line to colour the header row red.

🏠 Homework

Pick a repetitive task you do in Excel (formatting, adding totals, cleaning a column). Record it as a macro, run it on new data to confirm it works, then open the VBA and explain each line in your own words. Save the file as .xlsm.