Tutorials›Excel›Macros Introduction
🟢 Free Demo
Excel TutorialTopic 14 of 31

Macros Introduction

Record and run Excel Macros to automate repetitive tasks without writing code.

✅ What You Will Learn

›What a Macro is and how recording works
›How to record a Macro for a repetitive task
›How to run a Macro using a button, shortcut key, or the macro list
›How to enable Macros safely in Excel
›The difference between absolute and relative recording

A Macro is a recorded sequence of actions in Excel. You press Record, perform a series of steps — formatting, filtering, copying data — and Excel translates every action into code. When you run the Macro, Excel executes all those steps automatically in seconds, no matter how many rows of data are involved.

Macros are stored as VBA (Visual Basic for Applications) code behind the scenes. You do not need to know VBA to record and use basic Macros — the recorder generates the code for you. However, understanding a little VBA lets you edit recorded Macros to make them smarter and more flexible.

Macro-enabled files are saved as .xlsm (not .xlsx). When you open a .xlsm file, Excel shows a security warning asking you to enable Macros — always verify the file source before clicking Enable.

Examples

Example 1Record a Macro to format a report header
Steps:
1. View tab → Macros → Record Macro
   Name it: FormatHeader (no spaces)
   Shortcut: Ctrl+Shift+H
   Store in: This Workbook
   Click OK

2. Now perform your steps:
   - Click cell A1
   - Bold the text (Ctrl+B)
   - Change font size to 14
   - Fill background to dark blue
   - Change font colour to white

3. View tab → Macros → Stop Recording

Now press Ctrl+Shift+H on any sheet to apply the same formatting instantly.
💡

Relative vs Absolute recording: Absolute recording always goes to the same cell (A1). Relative recording applies steps relative to the currently selected cell. Toggle with View → Use Relative References.

Example 2Assign a Macro to a button on the sheet
Steps:
1. Developer tab → Insert → Form Controls → Button
   (If Developer tab is not visible: File → Options → Customize Ribbon → check Developer)
2. Draw the button on the sheet
3. In the Assign Macro dialog, select your Macro
4. Right-click the button → Edit Text → rename it "Format Header"

Now anyone can click the button — no need to know the keyboard shortcut.
💡

Buttons make Macros accessible to non-technical users. Add one to every reporting template so users can refresh, format, and export with a click.

📌 Key Points to Remember

  • ✓Save the file as .xlsm (Macro-Enabled Workbook) or all Macros are lost when closing
  • ✓Macro names cannot have spaces — use CamelCase or underscores: FormatReport or Format_Report
  • ✓Use Relative Recording when the Macro should work from any starting cell, not always from A1
  • ✓Developer tab → Visual Basic opens the VBA Editor where you can view and edit recorded code
  • ✓Never enable Macros in files received from unknown sources — Macros can contain malicious code

🏢 Real-World Application

Reporting analysts at banks and BPOs use Macros to automate end-of-day report generation. A single button click formats the raw data export, applies conditional formatting, generates a summary table, adds the date header, and saves a copy to a shared folder — 40 steps completed in 3 seconds. What used to take 20 minutes of manual work per day is reduced to a single button press.

⚠️ Common Mistakes to Avoid

WRONGSaving a Macro-enabled file as .xlsx — losing all Macros
FIXWhen saving a file with Macros, Excel prompts to save as .xlsm. Always choose "Keep Macro-Enabled Format". If you accidentally save as .xlsx, all Macros are permanently lost.
WRONGRecording with Absolute Reference when you need Relative — Macro always changes A1 regardless of selection
FIXBefore recording, click View → Use Relative References if the Macro should work relative to wherever the cursor is, not always starting from a fixed cell.
WRONGMacro runs but produces wrong results on a different dataset
FIXRecorded Macros are brittle — they record exact cell addresses and values. For datasets of varying size, the recorded Macro needs VBA edits to use dynamic ranges. This is where learning basic VBA becomes essential.
✏️Test Yourself

What file format must you use to save an Excel workbook that contains Macros?

❓ Frequently Asked Questions

Do I need to know programming to use Macros?

No — the Macro Recorder generates VBA code for you automatically. For basic automation (formatting, filtering, copying) the recorder is enough. VBA knowledge lets you edit the recorded code to handle dynamic data and add logic.

Can I run a Macro automatically when a file opens?

Yes — create a Sub named Auto_Open() or a Workbook_Open() event in the ThisWorkbook module. This Macro runs automatically every time the file is opened.

Are Macros compatible across Excel versions?

Generally yes — VBA Macros written in Excel 2016 run in Excel 2019 and Microsoft 365. There are occasional differences with newer features. Macros are not supported in Excel Online.

✏️ Practice Exercise

Record a Macro that: (1) Selects the active sheet data range, (2) Applies alternating row colours (light grey every other row), (3) Makes the header row bold with a dark background and white text, (4) Auto-fits all column widths, (5) Saves the file. Assign it to a button labelled "Format Report". Test it on a different dataset on a new sheet.

← PreviousPower Query TransformsNext →VBA Basics
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.