How to Create a Macro in Excel (2 ways)

This page is an in-depth guide on how to create a Macro in Excel, with step-by-step instructions, detailed explanations, and practical examples. Creating a macro in Excel helps automate repetitive tasks, improving efficiency and accuracy. Whether applying formatting, generating reports, or executing complex calculations, macros streamline workflows and minimize errors. Excel offers built-in tools for macro creation, including the Macro Recorder for capturing actions and VBA for writing custom scripts.

What is a Macro in Excel?

A macro in Excel is a powerful tool that automates repetitive tasks by recording a series of actions and executing them with a single click or shortcut. It helps streamline tasks like formatting, applying formulas, generating reports, and managing data efficiently. Below are the key points of a macro:

• Automates Tasks – A macro in Excel helps you do repetitive work automatically.
• Records Steps – You can record your actions in Excel and run them later with one click.
• Saves Time – The Macro saves time by completing the tasks like formatting, calculations, and data.
• Can Be Edited – You can change or update macros to match your needs.
• Security Warning – Since macros can run scripts, Excel may block them to prevent harmful code.
• Quick Access – You can link macros to a button or a keyboard shortcut for faster execution.

How to Create a Macro in Excel

You can create a macro in 2 different ways:

• Using the Macro Recorder (No Coding Required)
• Using VBA (For Advanced Users)

Method 1: How to Create a Macro in Excel using Macro Recorder

The Macro Recorder is the simplest way to create a macro in Excel without entering any code. Follow the below steps to create a macro in Excel using macro recorder:

The Macro Recorder is the simplest way to create a macro in Excel without entering any code. Follow the below steps to create a macro in Excel using macro recorder:

Step 1: Enable Developer Tab

• To add the developer tab, go to File → Options → Customize Ribbon.
• Tick the box for Developer and click OK. Ignore, if the Developer tab is already ticked and added in Excel.

Step 2: Record Macro

• Go to the Developer tab and click Record Macro.

• Give your macro a name (e.g., Macro1).
• You can define a shortcut key for the macro along with Ctrl key, which is optional.
• Under Store macro in, you can select where to store it:
  • This Workbook: for use in the current Excel file only.
  • New Workbook: for use in a new Excel file.
  • Personal Macro Workbook: for all Excel files.

• You can update the description of the macro, which is optional.
• Click OK and start updating the repetitive tasks such as format, font, text, formula etc., which you want to automate.

Step 3: Stop Recording

• Complete the desired tasks for the Macro.
• Go to the Developer tab and click Stop Recording Macro.

Step 4: Run Macro

• Press the shortcut key (if assigned) to run the macro.
• Or go to Developer tab, click on View Macros under Code section.

• Select the macro name which you want to use.
• Under Macros in, select from the dropdown where to apply the macro:
  • All Open Workbook, or
  • This Workbook.
• Then click Run button to run the macro.

Method 2: How to Create a Macro in Excel using VBA Code

Step 1: Open VBA Editor

• Go to Developer tab and click Visual Basic under Code section to open the VBA (Visual Basic for Applications) editor.
• Or, press the keyboard shortcut key Alt + F11 to open the VBA editor.

Step 2: Insert Module

• In VBA editor, go to Insert tab and click Module to insert.
• You can insert multiple modules in VBA editor.

Step 3: Write a Macro

• Write the VBA code in the module, which starts with Sub and ends with End Sub.
• Below is a easy and sample module with VBA code.

Step 3: Save Macro File

• Click the Save button or press Ctrl + S to get the Save As dialog box.
• Update the file name and in the Save as type, select Excel Macro-Enabled Workbook (.xlsm) from the drop down list.

Step 4: Run Macro

• Go to Developer tab, click on View Macros under Code section and click Run button.
• Or press Alt + F8, select the macro, and click Run.

Tips for Creating Effective Macros in Excel

• Find Repetitive Tasks: Look for actions you do often and consider automating them with macros.
• Create Multiple Macros: Make different macros for different tasks to save time.
• Use Correct References: Use absolute references (example: $A$1) to keep a fixed cell or relative references (example: A1) to move based on selection.
• Keep a Record: Write down macro names, what they do, and their shortcut keys for easy access.
• Name Macros Clearly: Use meaningful names so you can quickly understand their function.

Shortcut Keys

Below are the keyboard shortcut keys to use Macro in Excel:

Conclusion

The Macros in Excel help automate repetitive tasks, making work faster and reducing mistakes. You can create a macro by recording your actions or using VBA code. This is useful for tasks like formatting data, creating reports, or running calculations, and all with just one click. Learning and using macros properly can save time and improve how you manage data in Excel.

Learn More