Macros in Excel with an example

What is a macro in Excel?

An Excel macro is an action or set of actions that can be saved, named, saved, and executed as many times as needed and whenever you want. Using macros, we are able to automate repetitive tasks related to data manipulation and reporting, which must be performed repeatedly.

Sample data

In this example, we’ll add headers and some formatting to our sample data, and then follow the same steps with the macro shortcut key.

Turn on the developer section

The Developer tab is hidden on the default bar. To customize the ribbon, follow these steps:

• Right-click the ribbon anywhere and select (click) Customize the ribbon.

• Navigate to Customize the ribbon and select the Developer checkbox.

FREE course: Introduction to data analysis

The mastery of the basics of data analysis is one click away!Start learning

Write a macro

Now that we have the developer tab on our bar, let’s start saving our first macro in Excel. To save a macro, follow these steps:

• On the Developer tab, go to the Code group and click Save Macro.

The Save Macro dialog box appears. Give your macro a name and specify a shortcut that will activate the macro. Click OK.

Note: Each step you perform from now on will be recorded by the macro.

Let’s now add headers and format them with some colors.

When you’re done, return to the Developers tab and click Stop Recording.

Your macro has saved the steps, and you can now perform the same steps with the shortcut key you specified in the Save Macro dialog box.

Free course: Introduction to MS Excel

Learn the basics of MS ExcelSign up now

Add a button to launch your macro

In Excel, you can create a button that will launch your macro. To do this, follow the steps below:

• Go to illustrations> Shapes and select any shape. We will use a rectangle with rounded corners.

You can also insert a button by going to Developer> Insert> Form Controls.

• Add text to the shape by right-clicking on the shape and selecting to edit text.

• Right-click on the shape and select Assign Macro.

Select the appropriate macro from the window and select (by clicking) OK.

Therefore, when you select (by clicking) this form, Microsoft Excel will activate and run your saved macro.

Gain experience in the latest business analysis tools and techniques with the Master of Business Analytics program. Sign up now!

Conclusion

In this article, you learned about Excel macros. They can be used to perform several steps of a one-click task or keyboard shortcut.

Increase your analytics career with powerful new skills for Microsoft Excel by taking the Excel Business Analytics course, which includes Power BI training

This Business Analysis Certification course teaches you the basic concepts of data analysis and statistics to help you make data-based decisions. This training introduces you to Power BI and delves into statistical concepts that will help you build insights from available data to present your findings using CEO-level dashboards.

Do you have any questions for us? Feel free to ask them in the comments section of this article and our experts will answer you immediately!