Excel Macros: All You Need to Know

Macros are a set of programming instructions written in VBA, which automate a repeated and standardized process in Excel and other Microsoft Office Applications, like Access, PowerPoint, Word & Outlook.

What Is a Macro in Excel?

According to Microsoft, “A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes. After you create a macro, you can edit it to make minor changes to the way it works”. 

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

Sample Data

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

sample data

Turn on Developer Tab

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

  • Right-click on the ribbon, anywhere and select (by clicking) Customize the ribbon.

developer

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

developer

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

Record a Macro

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

  • On the Developer tab, go to Code group and click Record macro.

record excel

The Record Macro dialog box will appear. Give your macro a name and assign the shortcut which will activate the macro. Click Ok.

record

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

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

record

Once you are done, go back to the developer tab and click stop recording.

Your macro has recorded the steps, and you can now perform the same steps with the shortcut key you have assigned in the Record Macro dialog box.

Free Course: Introduction to MS Excel

Master the Fundamentals of MS ExcelEnroll Now
Free Course: Introduction to MS Excel

Add a Button to Run Your Macro

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

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

dev tab

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

form control

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

shapes

  • Right-click the shape and select Assign Macro.

shapes

Select the corresponding macro from the window and select (by clicking) Ok.

Consequently, whenever you select (by clicking) that shape, Microsoft Excel will activate and run your recorded macro.

recording

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Conclusion

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

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

This Business Analytics certification course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.

Do you have any questions for us? Feel free to ask them in this article’s comments section, and our experts will promptly answer them for you! 

About the Author

Aryan GuptaAryan Gupta

Aryan is a tech enthusiast who likes to stay updated about trending technologies of today. He is passionate about all things technology, a keen researcher, and writes to inspire. Aside from technology, he is an active football player and a keen enthusiast of the game.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.