Macros In Excel With Examples: Step-by-Step Tutorial
Last Updated : 10 Apr, 2025
Excel macros are a powerful tool that can automate repetitive tasks, saving you time and increasing productivity. Whether you’re trying to enable Excel macros, record a macro in Excel, or automate specific actions within your spreadsheet, macros are an invaluable feature. In this guide, we will walk you through the essential steps of using macros in Excel, including how to run the macro in Excel, create macros using the VBA editor, and even how to run a macro by clicking a button. We’ll also show you how to view macros in MS Excel and how to save the recorded macros in Excel for future use. By the end of this article, you’ll be equipped with the knowledge to automate tasks and optimize your workflow with ease.
Prerequisites for Using Excel Macros
Before learning how to create macros in Excel you have to turn o the developer mode in Excel. Follow these steps:
Step 1: Open MS Excel
Navigate to the MS Excel icon and give it a click to open it.
Step 2: Right-click on the Ribbon
Go to the menu and and perform a right-click on the ribbon, select Customize the ribbon.
.png)
Step 3: Check the Developer box and Click on “OK”
Navigate to Customize the ribbon and place a check on the Developer Checkbox
.png)
How to Enable Excel Macros
To enable macros in Microsoft Excel, follow these steps:
For Excel on Windows:
- Open the Excel File:
- Open the Excel workbook that contains the macros you want to enable.
- Access the Trust Center:
- Click on the File tab in the top-left corner of the screen.
- Select Options at the bottom of the left-hand menu.
- In the Excel Options window, select Trust Center from the left-hand pane.
- Click on the Trust Center Settings button on the right side.
- Navigate to Macro Settings:
- In the Trust Center window, click on Macro Settings on the left.
- Choose the Appropriate Option:
- You will see several options. Select the one that best meets your security needs:
- Disable all macros without notification: This option disables all macros and does not show any warnings.
- Disable all macros with notification: Macros are disabled, but you are alerted with a security warning when a macro-enabled file is opened. You can then enable macros manually.
- Disable all macros except digitally signed macros: This option will enable macros only if they are signed by a trusted publisher.
- Enable all macros (not recommended; potentially dangerous code can run): This option will enable all macros without any restrictions. Be cautious when using this setting as it can expose you to security risks.
- Save Your Settings:
- Click OK to close the Trust Center window.
- Click OK again to close the Excel Options window.
- Enable Macros for a Specific Workbook (If Prompted):
- When you open a workbook containing macros, you may see a yellow Security Warning bar below the ribbon. Click Enable Content to allow macros to run.
For Excel on Mac:
- Open the Excel File:
- Open the workbook that you want to enable macros for.
- Access the Preferences Menu:
- Click on Excel in the menu bar and select Preferences.
- Select Security:
- In the Preferences window, click on Security.
- Enable Macros:
- Check the box labeled Enable all macros (not recommended; potentially dangerous code can run).
- Click OK to save your changes.
- Reopen the Workbook (If Necessary):
- In some cases, you may need to close and reopen the workbook for the changes to take effect.
Important Security Tip:
- Macros can contain potentially harmful code. It is always best to enable macros from trusted sources only. Consider using “Disable all macros with notification” as your default setting, and then enable macros on a case-by-case basis.
How to Record a Macro in Excel
Recording a macro is one of the easiest ways to get started with Excel Macros:
Step 1: Click on the Developer Tab
Go to the Developer tab located at the ribbon.
Step 2: Click on the Record Macro
In the Code Group, the tool Record Macros is located, to excel macros record you have to click on it.
Step 3: Fill the Record Macro Dialog Box
As macros are selected, a dialog box is opened which contains the following options to fill.
In the dialog box, enter the name of the Excel macro in the Macro name. However, space cannot be used in the Macro name to separate the words, underscore(_) can be used for this purpose. Example: Macro_name.
There are some other options also in the dialog box, i.e., where to store the macro, the shortcut key which can be used to activate the macro, and the description for the macro created. They can be filled accordingly.
- After filling in the dialog box, click on OK.
- As soon as OK is clicked, Excel Macro records the set of operations the user is practicing.
- Once, the work is done, in the Developer Tab, an option “Stop Recording” can be seen. Clicking on which the recording gets stopped and the created macro is saved.
How to Run the Macro in Excel
There are multiple ways to run macros after creating macros in Excel, here we will learn how to run excel macros:
Run the Macro by Clicking on a Shape
It is one of the easiest ways to run a macro to create any shape in the worksheet and use it for running the macro.
Step 1: Click on the Insert Tab on the ribbon
Step 2: Choose any Shape
Go to the Illustrations group and click on the Shapes icon. In the sheet, choose any shape we like and want it to be assigned as the macro.
Step 3: Add the Shape
Click where you want to add the shape in the sheet and the shape will automatically get inserted.
Step 4: Edit the Shape
The shape can be resized or re-formatted accordingly to the way you want. Text can also be added to the shape.
Step 5: Right Click on the Shape and Click on Assign Macro
Then Right-Click on the shape and a dialog box would be opened. Then click on Assign Macro.
After right-clicking on the shape, another dialog box of Macro would be opened.
Step 6: Now Select the Macro
In the dialog box, select the macro from the list you want to assign to your shape and click the OK button. Now the shape would work as a button and whenever you click on it, it will run the assigned macro.
While the shape is something you can format and play with it, a button has a standard format. A macro can also be assigned to a button for easy use and then can run the macro in Excel by simply clicking that button.
Step 1: Go to the Developer Tab on the Ribbon
Step 3: Click on the Excel Sheet and Fill the Details in the Dialog box
Now, click anywhere on the Excel sheet. As soon as you do this, the Assign Macro dialog box would be opened.
Step 4: Select the Macro you want to Assign and Click OK
Select the macro you want to assign to that button and fill in the other options. Then click on OK.
Step 5: Preview the Shape
After that, a rectangular button can be seen on the sheet, and ready to be used.
The button which appears on the sheet has default settings, and you can’t format it like the color, or shape of the button.
However, the text which appears on the button can be changed. Just a right-click on the button and a dialog box would open. Click on Edit Alt Text and change the text.
How to Create Macro in VBA Editor
While recording macros is a quick way to automate tasks, writing VBA code allows you to create more complex macros with advanced functionality. To write custom VBA macros, follow these steps:
Step 1: Go to the Developer Tab on the Ribbon
Step 2: Click on the Visual Basic
In the Code Group, click on Visual Basic and a new window would be opened.
Step 3: Click on Run Macro
Then click on the Run Macro sign, it will open a dialog box listing all the macros of that workbook.
Step 4: Select the Macro from the list
Select the macro you want to run from the list and click Run.
As soon as Run is clicked, the macro would be executed on the excel-sheet. If you can only see the VB Editor window and not the sheet, then you may not see the changes happening in the worksheet. To see the changes, minimize/close the VB Editor window. You can also edit a macro as per your need.
How to View Macros in MS Excel
To view Macros in Excel follow the steps given below:
Step 1: Open Microsoft Excel
Step 2: Click on the Developer Tab
After opening the Microsoft Excel go the Developer tab.
.png)
Step 3: Click Macros to see a list of available macros in the active workbook
Step 4: Select a Macro to View
Step 5: You can run or edit the macro from here if needed.
Step 6: Close the Macro dialog when you’re done
.png)
How to Save the Recorded Macros in Excel
After creating or recording a macro, save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to keep the macro accessible in the future.
Step 1: Record your Actions
To save Excel macro you have to record it first so if you don’t how to record Excel macro follow the steps given above.
Record your Actions
Step 2: Click on Stop Recording
Stop the recording Excel Macro when you are done recording a macro in Excel
Click on Stop Recording
Step 3: Save your workbook as “.xlsm” (macro-enabled)
When you will save a macro in Excel then it will automatically saved in the workbook.
Save your workbook as “.xlsm”
Step 4: Excel Macro Saved
Now your macro in excel is saved and you can use it whenever you want.
Example Macros for Practical Use
Macros can help automate a variety of tasks in Excel:
- Data Formatting: Create a macro to format a dataset automatically.
- Report Generation: Automate the generation of reports with specific parameters.
- Sorting and Filtering: Use macros to quickly sort or filter data.
These examples are just the beginning of what Excel macros and Excel VBA can accomplish.
Conclusion
Now that you have a better understanding of macros in Excel, you can begin using them to streamline your workflow and automate time-consuming tasks. Whether you’re recording a macro in Excel, running it by clicking a button, or creating more advanced macros through the VBA editor, these tools will help you work more efficiently. By learning how to view macros in MS Excel and save the recorded macros, you can manage and reuse your automations for improved productivity. With consistent practice, you’ll be able to fully leverage Excel’s macro capabilities to handle complex tasks effortlessly.
Similar Reads
How to use Copilot in Excel with Examples (Step-by-Step Guide)
As of 2024, Microsoft introduced a new feature called Copilot for Excel, part of the broader Microsoft 365 suite, designed to enhance productivity and streamline data manipulation using AI-powered tools. The introduction of Excel Copilot is transforming how professionals across industries manage and
9 min read
Statistical Functions in Excel With Examples
To begin with, statistical function in Excel let's first understand what is statistics and why we need it? So, statistics is a branch of sciences that can give a property to a sample. It deals with collecting, organizing, analyzing, and presenting the data. One of the great mathematicians Karl Pears
6 min read
How to Enable Macros in Excel (2025): Step-by-Step Guide
How to Activate Macros in Excel - Quick StepsOpen the Excel workbook.Click Enable Content in the yellow security warning bar.Go to File > Options > Trust Center > Trust Center Settings > Macro Settings.Choose the desired macro setting (e.g., Enable All Macros for trusted files).Save the
9 min read
Master Power Query Excel in 2024: Step-by-Step Tutorial
Mastering Power Query in Excel unlocks a powerful suite of tools for data transformation and analysis, enabling users to streamline their workflows and enhance productivity. As a feature integrated into Microsoft Excel, Power Query simplifies the process of importing, cleaning, and transforming data
15 min read
How to Create a Budget in Excel: Step by Step Tutorial
How to Make a Budget in Excel: Quick StepsSet up categories>>Enter Data Use Formulas >>Format the sheetCreate chartsPreview ResultsBudgeting is one of the most effective ways to take control of your finances, whether for personal or business use. Without a clear plan, expenses can quickl
4 min read
OFFSET Function in Excel With Examples
Excel contains many useful formulas and functions that make it more and more useful and at the same time user-friendly. Such a function is the OFFSET() function. In many cases, this function is also used inside another function. This function basically returns a reference of a single cell or a range
4 min read
How To Use MATCH Function in Excel (With Examples)
Finding the right data in large spreadsheets can often feel like searching for a needle in a haystack. This is where the MATCH function in Excel proves invaluable. The MATCH function helps you locate the position of a specific value within a row or column, making it a cornerstone of efficient data m
6 min read
How to Autofill in Excel: Step by Step Tutorial
Feeling stuck typing the same data over and over in Excel, then Autofile features work for you. This feature works like a magic trick that quickly fills in number sequences, dates, or repeated text. Whether you’re working with a big dataset or building a report, learning Autofill can save you tons o
11 min read
How to Automate Repetitive Tasks in Excel Without VBA Macros
Automating repetitive tasks in Excel can save you significant time and effort, even if you're unfamiliar with VBA macros. Excel offers built-in tools and features that allow users to streamline workflows and reduce manual work without any coding knowledge. From using Power Query for data transformat
8 min read
Excel Date and Time Formats With Examples
Excel has a built-in time feature that is simple to use and can save you a lot of time. We can insert current Data and Time into a worksheet cell using Excel's built-in functions. When you enter a date or time into a cell, the date and time are displayed in the cell's default date and time format. T
4 min read