What Is a Macro in Excel?

An Excel macro is an action or a set of actions that can be recorded, named, saved and executed as many times as required and whenever desired. By using macros, we are able to automate repetitive tasks associated with data manipulation and data reporting that must be accomplished repeatedly. 

Using Excel Macros can speed up work and save you a lot of time.

One way of getting the VBA code is to record the macro and take the code it generates. However, that code by macro recorder is often full of code that is not really needed. Also macro recorder has some limitations. So it pays to have a collection of useful VBA macro codes that you can have in your back pocket and use it when needed.

While writing an Excel VBA macro code may take some time initially, once it’s done, you can keep it available as a reference and use it whenever you need it next.

In this massive article, I am going to list some useful Excel macro examples that I need often and keep stashed away in my private vault. I will keep updating this tutorial with more macro examples. If you think something should be on the list, just leave a comment.

You can bookmark this page for future reference.

Now before I get into the Macro Example and give you the VBA code, let me first show you how to use these example codes.

Using the Code from Excel Macro Examples

Here are the steps you need to follow to use the code from any of the examples:

1. Open the Workbook in which you want to use the macro.
2. Hold the ALT key and press F11. This opens the VB Editor.
3. Right-click on any of the objects in the project explorer.
4. Go to Insert –> Module.
5. Copy and Paste the code in the Module Code Window.

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.

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.

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

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.

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

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. 

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.

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.

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

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

• Right-click the shape and select Assign Macro.

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.

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 for Strategic Decision Making with IIT Roorkee 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!