Ravindra BagaleCourses & study guides मराठी Track your progress

12. Macros and VBA

12.2 Recording a Macro: Absolute vs Relative

By Last updated:

The Macro Recorder writes VBA code while you click. Great for learning and for simple formatting tasks.

Steps in Excel – record a header-format macro

  1. Developer › Code › Record Macro (also View › Macros › Record Macro, or the small record button on the status bar).
  2. Macro name FormatHeader (no spaces) › Shortcut key: press Shift + H so it becomes Ctrl + Shift + H › Store macro in: This Workbook › Description › OK.
  3. Do the actions: select A1:I1 › Bold › fill dark green › font white.
  4. Developer › Code › Stop Recording.
  5. Alt + F11 to see the code in Modules › Module1.

What the recorder writes (absolute references – always A1:I1):

Sub FormatHeader()
'
' FormatHeader Macro
' Keyboard Shortcut: Ctrl+Shift+H
'
    Range("A1:I1").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .Color = 4616993
    End With
    Selection.Font.Color = RGB(255, 255, 255)
End Sub

Relative references: click Developer › Code › Use Relative References before recording. The macro then works relative to the active cell – for example, "format the current cell and the 8 cells to its right":

Sub FormatRowFromActiveCell()
    ActiveCell.Resize(1, 9).Select
    Selection.Font.Bold = True
    ActiveCell.Offset(1, 0).Select
End Sub
Recording mode Code uses Replays on
Absolute (default) Range("A1:I1") Always the same cells
Relative ActiveCell.Offset(…), Resize Cells relative to where you start

About this data: Teaching sample for a fictional shop “NovaCraft Tools”. Figures and names (e.g. Priya Deshmukh) are made up for this lesson on recording a macro absolute vs relative.

Ravindra Bagale's Tip

The recorder records every mistake too – a wrong click, a scroll, an undo – and then many students' macros behave strangely. Before recording, write the steps on paper, practise once, then record. Decide absolute or relative before you start recording; if you toggle in the middle, the code gets mixed.

Worked example — recording a macro absolute vs relative

Why

Aamir Khan in Pune and Sophie Martin abroad both need a clear, repeatable way to use recording a macro absolute vs relative on a Summit Schools sheet without breaking totals.

What

Recording A Macro Absolute Vs Relative is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for recording a macro absolute vs relative.
  2. Enter a small made-up value for Aamir Khan using £ where money appears.
  3. Add a second value for Sophie Martin using £ so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. Check the result still works inside SUM or a simple check cell.

Formula / action

Follow the formula or ribbon steps shown in this lesson (keep values numeric).
Result: The demo cell shows the expected result for Summit Schools's sample row.
Meaning: Once recording a macro absolute vs relative is correct on one row, copy the same pattern down the column.

Practice tasks (10+)

Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.

1. Rebuild the smallest example of recording a macro absolute vs relative on a blank sheet for QuickCart Delivery.

Hint: Use only the steps on this page. Keep numbers numeric.

2. Change one input for Neha Shah and confirm the result updates.

Hint: If the result is stuck, check absolute vs relative references.

3. Repeat the same steps with a £ or € value for Sophie Martin.

Hint: Formatting must not break SUM.

4. Write the result meaning in one plain sentence next to the cell.

Hint: Imagine explaining it to a new joiners.

5. Add a second row and fill the method down.

Hint: Double-click the fill handle if the pattern is consistent.

6. Break the example on purpose, then fix it using this lesson.

Hint: Common break: text instead of a number.

7. Apply recording a macro absolute vs relative to a 5-row made-up QuickCart Delivery table.

Hint: Keep column headers clear.

8. Check your answer with a simple total or COUNT.

Hint: A wrong total usually means a format or key mismatch.

9. Swap ₹ and $ on one column and re-check totals.

Hint: Stored values stay the same; only the display changes.

10. List two mistakes beginners make with recording a macro absolute vs relative.

Hint: Look back at the tip box on this page.

11. Teach the steps out loud in under one minute.

Hint: Why → What → How.

12. Save the sheet as QuickCartDe_practice.xlsx.

Hint: Use a clear file name for class.

Loading practice tasks...

  1. Last used row (Easy): Which row number is the last filled row? The headings are in row 1.
  2. Late orders (Easy): How many rows have Status "Late"?
  3. Total amount (Easy): Add up the Amount column.
  4. Worst delay (Easy): What is the largest number in the Days late column?
  5. Late orders in Pune (Medium): How many orders are Late and in City "Pune"?
  6. Customer for an order (Medium): Find the Customer for Order ID "DL-704".
  7. Money stuck in late orders (Medium): Add up the Amount column for rows where Status is "Late".
  8. Big orders (Medium): How many rows have an Amount of 2000 or more?
  9. Message text (Medium): Build the text "N late orders", where N is the count of Late rows.
  10. Average delay of late orders (Hard): What is the average Days late for rows with Status "Late"? Round to 1 decimal.
  11. Tag for the first row (Hard): For the first row, return "Chase" if Days late is 3 or more, else "OK".
  12. Warning or all clear (Hard): Return "Warning" if any order is 3 or more days late, else "All clear".

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Recording A Macro Absolute Vs Relative?
Card 1: What is Recording A Macro Absolute Vs Relative? · Download PNG
Revision card 2: Tiny example — Recording A Macro Absolute Vs Relative
Card 2: Tiny example — Recording A Macro Absolute Vs Relative · Download PNG
Revision card 3: Checklist — Recording A Macro Absolute Vs Relative
Card 3: Checklist — Recording A Macro Absolute Vs Relative · Download PNG