Excel VBA Basics: Automate Repetitive Tasks Easily

If you run the same five steps on a report every Monday — bold the header, gray the top row, autofit the columns, set a number format — you’ve already described a macro. VBA is just the language Excel writes when you record those steps, and the editor where you can tidy them up afterward. You don’t start by learning to program; you start by recording, then reading what Excel wrote. This walks from your first recorded macro to a short loop you wrote yourself, which is further into VBA than most people ever need to go.

Turn on the Developer tab and record

The macro tools live on the Developer tab, which Excel hides by default. Switching it on is a one-time setup, and from there recording is three clicks.

1. Go to File → Options → Customize Ribbon, tick Developer on the right, and click OK.
2. On the Developer tab, click Record Macro, give it a clear name like FormatReport, and click OK.
3. Perform your steps normally — every click and keystroke is captured — then click Stop Recording.

That’s a working macro, no code typed. The recorder is the single best way to learn VBA, because it shows you the exact code for an action you already know how to do by hand. Name your macros for what they accomplish, not Macro1 and Macro2 — six months from now “FormatReport” tells you what it does and “Macro3” tells you nothing. Microsoft’s getting-started guide to VBA is the reference once you want the full language.

Read what the recorder wrote

Press Alt + F11 to open the Visual Basic Editor, find your macro in the Modules folder on the left, and you’ll see something like this:

Sub FormatReport()
    Range("A1:D1").Font.Bold = True
    Range("A1:D1").Interior.Color = RGB(217, 217, 217)
    Columns("A:D").AutoFit
End Sub

It reads almost like English once you know the shape. Every macro is a Sub … End Sub block — the container for one routine. Inside, each line follows the pattern object . property = value: take a Range, reach into its Font, set Bold to true. Range("A1:D1").Interior.Color is the cell fill; RGB(217, 217, 217) is a light gray. You don’t have to memorize this vocabulary — record the action once and the editor hands you the exact words for it, which is how the recorder doubles as a phrasebook.

One quirk to expect: the recorder is wordy. It tends to record a selection and then act on it across two lines — Range("A1").Select followed by Selection.Font.Bold = True — because that mirrors your literal clicks. You can collapse those into the single Range("A1").Font.Bold = True form shown above, which is shorter and runs without jumping the screen around. Tidying the recorder’s .Select lines is usually the first real edit anyone makes, and doing it teaches the object-property pattern faster than any tutorial.

Run it, and wire it to a button

A macro you have to dig through menus to launch won’t get used. Give it a one-click trigger so replaying those Monday steps costs a single press.

1. Insert a shape or a Form Control button from the Developer tab onto your sheet.
2. Right-click it, choose Assign Macro, and pick FormatReport.
3. Or assign a keyboard shortcut from Developer → Macros → Options.

Now the whole sequence runs from one button or keystroke. This is where the time actually comes back: a five-step formatting job that took a minute by hand becomes instant, and it runs identically every time — no skipped step, no forgotten format. Pair a few of these with the keyboard shortcuts worth memorizing and a surprising amount of routine spreadsheet work stops being manual at all.

Edit safely in the editor

The recorder gets you 90% there; small edits in the VB Editor close the gap. Editing is low-risk as long as you change one thing at a time and run the macro after each change to see the effect.

Sub FormatReport()
    Range("A1:D1").Font.Bold = True
    Range("A1:D1").Interior.Color = RGB(31, 119, 180)
    Range("A1:D1").Font.Color = RGB(255, 255, 255)
    Columns("A:D").AutoFit
End Sub

Here two lines were tweaked — a blue fill instead of gray, plus white header text — by editing the values the recorder produced. That’s most early VBA editing: record the rough version, then adjust the literal numbers and add a line copied from another recording. If an edit misbehaves, press Ctrl + Z in the editor or just re-record the step; nothing you do here touches your data until you run the macro, so the editor is a safe place to experiment.

Relative recording: the reuse switch

By default the recorder hard-codes exact cells — record formatting on row 1 and it will always format row 1, even if you meant “the current row.” The fix is the Use Relative References toggle on the Developer tab, pressed before you record.

Write a loop to handle many rows

The one thing the recorder can’t do is repeat across a range — for that you write a loop, and it’s the natural next step. To color every overdue date in a column red:

Sub HighlightOverdue()
    Dim cell As Range
    For Each cell In Range("C2:C100")
        If cell.Value < Date Then
            cell.Interior.Color = RGB(255, 199, 206)
        End If
    Next cell
End Sub

The For Each ... Next block walks every cell in the range, and the If applies the format only when the date is past (Date is VBA’s today). This is genuine programming, but a gentle kind — you’re describing “for each of these, if this, then that,” which is how you’d explain the task aloud anyway. Loops are where macros pull ahead of formulas: a formula computes a value in a cell, but a loop can reformat, move, or clean a thousand rows in one pass. Run it by pressing F5 inside the editor, or from Developer → Macros on the sheet itself.

Save as .xlsm and stay safe

Start with the recorder, read the code it produces, wire your favorites to buttons, and reach for a loop only when you need to repeat across a range. That progression — record, read, edit, loop — takes you from never having opened the editor to automating real work, without a programming course in between. The repetitive jobs you do the same way every time are the ones worth recording first; once they run on a button, you get that minute back every single time, and the macro never skips a step the way a tired human does. For automating data cleanup specifically, more macro tips and tricks build on exactly these foundations.