Macros In Excel With Examples

Excel macros are a versatile tool that can automate repetitive tasks, saving we time and increasing productivity. Whether we're trying to enable Excel macros, record a macro in Excel, or automate specific actions within our spreadsheet, macros are an invaluable feature.

1. Prerequisites for Using Excel Macros

Before learning how to create macros in Excel we 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.

Step 3: Check the Developer box and Click on “OK”

Navigate to Customize the ribbon and place a check on the Developer Checkbox.

2. Enabling Macros on Windows

1. Accessing the Trust Center

Steps:

1. Open Workbook: Launch Excel and open the macro-enabled workbook (.xlsm).
2. Navigate to Options: Go to File > Options > Trust Center > Trust Center Settings.
3. Select Macro Settings: In the Trust Center window, click Macro Settings.

2. Configuring Macro Settings

Steps:

1. Choose Option:

• Disable all macros without notification: Blocks all macros, no prompts.
• Disable all macros with notification: Blocks macros but prompts to enable (recommended).
• Disable all macros except digitally signed: Allows only trusted, signed macros.
• Enable all macros: Allows all macros (use cautiously due to security risks).

2. Save Settings: Click OK twice to exit Trust Center and Options.

3. Enable Content: If a yellow Security Warning bar appears, click Enable Content to run macros.

3. Enabling Macros on Mac

1. Accessing Preferences

Steps:

1. Open Workbook: Launch Excel and open the macro-enabled workbook.
2. Navigate to Preferences: Go to Excel > Preferences > Security.

2. Configuring Macro Settings

Steps:

1. Enable Macros: Check “Enable all macros” (use cautiously due to security risks).
2. Save Changes: Click OK.
3. Reopen Workbook: Close and reopen the workbook if macros don’t activate.

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 our default setting, and then enable macros on a case-by-case basis.

4. 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 we 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.

5. 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:

1. 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 we 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 we 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 we want to assign to our shape and click the OK button. Now the shape would work as a button and whenever we click on it, it will run the assigned macro.

6. How to Run Macro By Clicking a Button

While the shape is something we 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 2: In the Controls group, click on the Button Tool

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 we do this, the Assign Macro dialog box would be opened.

Step 4: Select the Macro we want to Assign and Click OK

Select the macro we 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.

Step 6: Format the Button

The button which appears on the sheet has default settings, and we 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. 

7. How to Create Macro in VBA Editor

While recording macros is a quick way to automate tasks, writing VBA code allows we 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 we 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 we can only see the VB Editor window and not the sheet, then we may not see the changes happening in the worksheet. To see the changes, minimize/close the VB Editor window. we can also edit a macro as per our need.

8. How to View Macros in MS Excel

To view Macros in Excel follow the steps given below:

Step 1: Open Microsoft Excel

Launch Microsoft Excel by clicking the Excel icon on our computer (Windows or Mac). This opens the application, allowing us to access workbooks containing macros, such as a sales report with automation scripts.

Step 2: Click on the Developer Tab

Navigate to the Developer tab on the Ribbon, which provides access to macro-related tools.

Step 3: Click Macros to see a list of available macros in the active workbook

Click the Macros button in the Developer tab to open the Macro dialog box, displaying all macros in the active workbook.

Step 4: Select a Macro to View

In the Macro dialog, click a macro name (e.g., “FormatSalesTable”) to highlight it for review.

Step 5: Run or Edit the Macro

From the Macro dialog, we can run the selected macro to execute its actions or edit it in the VBA Editor to view or modify its code.

Step 6: Close the Macro dialog

Click Cancel in the Macro dialog to close it when finished reviewing or managing macros.

9. How to Save the Recorded Macros in Excel

After creating or recording a macro, save our workbook as an Excel Macro-Enabled Workbook (.xlsm) to keep the macro accessible in the future.

Step 1: Record our Actions

To save Excel macro we have to record it first so if we don't how to record Excel macro follow the steps given above.

Step 2: Click on Stop Recording

Stop the recording Excel Macro when we are done recording a macro in Excel.

Step 3: Save our workbook as “.xlsm” (macro-enabled)

When we will save a macro in Excel then it will automatically saved in the workbook.

Step 4: Excel Macro Saved

Now our macro in excel is saved and we can use it whenever we want.

10. 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.