Excel Macro Recording – A Complete Guide with Real-Life Examples

—

by

Himanshu
in

🔍 What is Macro Recording?

Macro Recording in Excel lets you automate a series of steps (like formatting, calculations, copying data, etc.) by recording them into a script using Visual Basic for Applications (VBA).

You don’t need to know coding—just record your actions, and Excel writes the VBA code in the background.

🛠️ How to Start Recording a Macro:

✔ Step-by-Step:

  1. Go to the View tab → Click Macros → Record Macro
    • OR enable the Developer tab via:
      • File → Options → Customize Ribbon → Tick Developer
  2. Give the macro:
    • A Name (no spaces, e.g., FormatReport)
    • (Optional) A Shortcut Key like Ctrl+Shift+F
    • Choose Where to Store:
      • This Workbook (use in current file only)
      • Personal Macro Workbook (use in all Excel files)
  3. Click OK and perform your actions
  4. Click Stop Recording (from the same menu)

Now your steps are recorded!

⚠️ Common Errors and How to Avoid:

IssueTip to Avoid
Recorded steps apply to wrong cellsAlways select cell references carefully (avoid absolute ones like A1 if you need flexibility)
Copy-paste issuesUse Paste Special and be careful with ranges
Macros don’t work on other filesUse relative references or store macros in Personal Workbook
Overwrites existing dataTest on a copy of data first
Macro security blocks codeEnable macro settings via: File → Options → Trust Center → Macro Settings

🧪 Now, Let’s Explore 5 Practical Examples, with Full Instructions:

✅ 1. Format a Monthly Sales Report Automatically

🧾 Use Case:

You get a raw report every month: inconsistent fonts, no borders, unformatted headers.

🔧 Steps to Record Macro:

  1. Click Record Macro, name it SalesFormat
  2. Select the header row → Bold, Center, Fill color
  3. Select entire data → Apply Calibri, font size 11
  4. Auto-fit all columns → Home → Format → AutoFit Column Width
  5. Add borders → Home → Borders → All Borders
  6. Freeze header row → View → Freeze Panes → Freeze Top Row
  7. Click Stop Recording

▶️ How to Use:

  • Next time you open the report, press the shortcut or run the macro from Macros list.
  • Your formatting is applied in seconds.

⚠️ Avoid:

  • Don’t record steps like clicking on random sheets.
  • Avoid selecting specific ranges like A1:D20—use Ctrl+A or Table Format if data size varies.

✅ 2. Create and Save Multiple Invoices from a Template

🧾 Use Case:

You want to generate 50 invoices using a common template, changing name, amount, and saving each separately.

📝 Requirements:

  • One sheet with customer names, invoice numbers, amounts
  • One template sheet with placeholders

🔧 Steps to Record Macro:

  1. Click Record Macro, name it GenerateInvoice
  2. Select the template
  3. Go to cell B5, type =Sheet1!A2 (customer name)
  4. Go to cell B6, type =Sheet1!B2 (amount)
  5. Save As → Choose folder → Name the file as Invoice_001.xlsx
  6. Click Stop Recording

▶️ How to Use:

  • Loop this macro using VBA (or copy manually for few records)
  • Edit recorded macro in VBA to add loop, file name logic, and auto-save

⚠️ Avoid:

  • Don’t hardcode cell values
  • Always test first 1–2 invoices manually before full automation

✅ 3. Merge Multiple Sheets into One Summary Sheet

🧾 Use Case:

You have 10 region-wise sheets and want to combine data into one master sheet.

🔧 Steps to Record:

  1. Start macro: MergeSheets
  2. Create a new sheet Summary
  3. Go to Sheet1 → Select data (excluding headers) → Copy
  4. Go to Summary → Paste at top
  5. Repeat with Sheet2, Sheet3 by pasting data below previous one
  6. Click Stop Recording

▶️ How to Use:

  • Use this macro to consolidate region-wise, branch-wise, or monthly data.

⚠️ Avoid:

  • Don’t copy headers each time
  • Don’t use fixed ranges like A2:D20. Use CurrentRegion or UsedRange

✅ 4. Send Personalized Emails from Excel via Outlook

🧾 Use Case:

You want to send each employee their attendance record.

📝 Setup:

  • Excel sheet with Name, Email, Attendance %

🔧 How to Use Macro (Basic Structure):

  1. Open VBA editor (Alt + F11)
  2. Use this code: vbaCopyEditSub SendEmails() Dim OutlookApp As Object, Mail As Object Dim ws As Worksheet, i As Integer Set ws = ThisWorkbook.Sheets("Sheet1") Set OutlookApp = CreateObject("Outlook.Application") For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Set Mail = OutlookApp.CreateItem(0) Mail.To = ws.Cells(i, 2).Value Mail.Subject = "Your Monthly Attendance" Mail.Body = "Hello " & ws.Cells(i, 1).Value & ", your attendance is: " & ws.Cells(i, 3).Value & "%" Mail.Send Next i MsgBox "Emails sent!" End Sub
  3. Run the macro

⚠️ Avoid:

  • Outlook must be installed and open
  • Avoid sending test mails to real clients—try to test on dummy emails

✅ 5. Clean and Standardize Raw Data Automatically

🧾 Use Case:

You get data with extra spaces, inconsistent casing, blank values.

🔧 Steps to Record:

  1. Click Record Macro → Name: CleanData
  2. Select data column
  3. Use:
    • =TRIM() to remove spaces
    • =PROPER() or =UPPER() for name formatting
    • IF(cell="", "NA", cell) to fill blanks
  4. Paste values → Clear old data
  5. Click Stop Recording

▶️ How to Use:

Run this every time you import new data.

⚠️ Avoid:

  • Don’t record sorting steps unless needed
  • Always back up original data

💡 Bonus Tips:

  • Save macros in Personal Macro Workbook to reuse in any file
  • Use Alt + F8 to view and run macros
  • Learn to edit macros in VBA Editor to add power like loops, conditions

✅ 10 Interview Questions with Sample Answers (Macro Recording & VBA)

🔹 Q1. What is a macro in Excel, and why is it used?

Answer:
A macro is a set of recorded instructions that automate repetitive tasks in Excel. It’s based on VBA (Visual Basic for Applications). Macros are useful for actions like formatting reports, cleaning data, or generating templates, saving time and reducing manual errors.

🔹 Q2. How do you record a macro in Excel?

Answer:
To record a macro:

  1. Go to the View tab or Developer tab → Click Record Macro
  2. Give it a name, optional shortcut key, and choose where to store it
  3. Perform the steps you want to automate
  4. Click Stop Recording
    The macro is now saved and can be run to repeat the same steps.

🔹 Q3. Can you give an example of a task you’ve automated using macros?

Answer:
Yes, I automated the formatting of a monthly sales report. I recorded a macro that:

  • Bolded the header
  • Applied font styles
  • Added borders
  • Auto-fitted columns
  • Froze the top row
    Now, instead of manually formatting every report, I just run the macro in seconds.

🔹 Q4. What are the limitations of using macro recording?

Answer:

  • It records absolute cell references by default, which makes it less flexible for dynamic data
  • It can record unintended steps (like selecting wrong sheets)
  • Not suitable for conditional logic or loops
    For more flexibility, macros can be edited in the VBA editor.

🔹 Q5. What is the difference between relative and absolute referencing in macros?

Answer:

  • Absolute recording always affects specific cells (e.g., A1)
  • Relative recording affects cells based on the current selection
    For example, a macro recorded relatively will apply formatting wherever your cursor is, not just in A1.

🔹 Q6. How can you edit a recorded macro?

Answer:
Open the VBA Editor using Alt + F11, then locate your macro under “Modules.” There, you can edit the generated VBA code—for example, replacing static cell references with variables or adding loops.

🔹 Q7. What is the file format used for saving macros in Excel?

Answer:
Macros are saved in workbooks with the extension .xlsm. Regular .xlsx files do not support macros. If you try to save a macro-enabled workbook as .xlsx, Excel will warn you that your macros will be removed.

🔹 Q8. How do you handle errors in macros?

Answer:
Basic error handling can be added using:

vbaCopyEditOn Error Resume Next
' or
On Error GoTo ErrorHandler

In recorded macros, it’s important to avoid invalid cell selections and test the macro on sample data first. Always use backup files.

🔹 Q9. What is the Personal Macro Workbook?

Answer:
The Personal Macro Workbook is a hidden workbook that opens every time Excel launches. Macros stored here are available across all workbooks, making it useful for reusable automations like formatting or data cleaning.

🔹 Q10. How do you run a macro using a shortcut or button?

Answer:

  • You can assign a macro to a keyboard shortcut while recording or by editing macro settings
  • Or, insert a Form Control Button from the Developer tab, right-click it → Assign Macro → choose your macro

📘 How to Prepare for Interview Questions on Excel Macros

🧠 1. Master Macro Recording (Hands-On Practice)

Practice these:

  • Recording a macro to format a table
  • Automating “Save As” based on cell value
  • Copying data from multiple sheets into one
  • Cleaning data (TRIM, PROPER, etc.)

Use the VBA editor to review what your macro code looks like.

🧾 2. Understand VBA Basics

Even if you don’t write VBA manually, learn:

  • How to edit a macro
  • The meaning of basic VBA commands like .Select, .Copy, .PasteSpecial, Range(), and Cells()
  • How to use loops:
vbaCopyEditFor i = 2 to 100
    Cells(i,1).Value = Trim(Cells(i,1).Value)
Next i

💻 3. Know Real Use Cases

Be ready to explain:

  • What you automated
  • Why it saved time
  • How it improved accuracy
  • Any errors you faced, and how you fixed them

⚠️ 4. Be Prepared for Error Handling Questions

Know:

  • How to test a macro
  • How to avoid hard-coded ranges
  • How to avoid overwriting real data
  • How to disable screen updating during macro (improves speed):
vbaCopyEditApplication.ScreenUpdating = False

📂 5. Carry Demo Files (if allowed)

If you’re in a live interview:

  • Show a file with a working macro
  • Show before-and-after results
  • Show VBA editor and explain code line-by-line

🎓 Summary for Quick Revision

TopicMust Know
Recording MacrosSteps to start/stop, shortcut assignment
Use CasesFormatting, reports, emailing, data cleanup
Editing MacrosOpen VBA editor, basic syntax
Common ErrorsHard-coded cells, test on copy, save as .xlsm
Interview PrepPractice macros, understand VBA basics, real use cases

🎓 Want to Learn Macros, Access, and SQL the Right Way?


If you’re serious about mastering Excel automation—especially using Macros, Access, and real-world MIS reporting—this Complete MIS Training course is a great place to start. It’s packed with hands-on projects, practical simulations, and real corporate use cases. With over 16.5 hours of video lessons and 26 downloadable resources, you’ll learn how to work smarter—not harder.
👉 Currently available at just ₹499 (down from ₹5,499) with a Certificate of Completion included.


Enroll now

and start building the automation skills employers value.

Featured products