How to Make Excel Macros

Here’s how to create Microsoft Excel macros. These are powerful tools for automation, especially when you use them for repetitive tasks. Below, I’ll discuss what they are, how to insert them, and share some common macros you can use to streamline your workflow.

What Are Excel Macros?

Macros in Excel are essentially a sequence of instructions that automate repetitive tasks within Excel. Instead of performing the same set of actions over and over again, you can set up a macro that does this for you.

When you assign a macro to a button in Excel, it works kind of like a shortcut key. Just press the button and your automated task gets done.

The biggest benefit of macros in Excel? You don’t need to be an expert with any programming language. The macro automatically records what you do, then it duplicates your actions to automate repetitive tasks.

Immediate Feedback on Making Macros in Excel

Want to ask a specific question about your macros? You can use our spreadsheet assistant below.

How to Make Excel Macros

These instructions can be activated either by a button you create or directly from the Developer tab.

So where’s the developer tab? What if you don’t see it in the ribbon?

No problem. I’ll explain step-by-step below.

Enabling the Developer Tab

The first thing you need to do to create macros is to make sure the Developer tab is enabled in your Excel workbooks. Here’s how you can do that:

1. Go to File.
2. Select Options.
3. Under Customize Ribbon, make sure the Developer tab is checked.

This will allow you access to the macro recording and editing features.

Let’s start by adding a button that sorts your data. I’ll use the same example I have in my video (below). It shows a dozen lines of sales data, and we want to sort it so the highest amounts show up at the top.

Creating a Simple Sorting Macro

Here’s a demonstration you can follow along with. Note that it also appears in my video (below) in case you want to watch it happen. You don’t need to use a visual basic editor to create these macros. Instead, you’ll just make a few mouse clicks!

Step 1: Preparing Your Data

Let’s begin with some raw sales data. Assume you receive this data every few days and want to automate the sorting of this data from highest to lowest.

Step 2: Recording the Macro

1. Go to the Developer tab in your toolbar.
2. Click Record Macro.
3. Enter the name of the macro (e.g., “Sort”) in the macro dialogue box, then click OK.

Once you hit OK, Excel starts recording every action you perform. Think of it as the “record macro button”. When you click it, it starts recording every keystroke and action you take in Microsoft Excel.

When you click “stop recording”, your macro is finished.

So here’s what we want to do for the sorting task:

Step 3: Performing the Sorting Task

1. Go to the Data tab.
2. Select Sort and choose to sort by the desired column (e.g., amount) from largest to smallest.
3. Click OK to sort your data.

Step 4: Stopping the Recording

Return to the Developer tab and click Stop Recording. Your macro is now saved.

Running Your Macro

To run the macro:

1. Go to the Developer tab.
2. Click Macros.
3. Select the macro you recorded (e.g., “Sort”) and click Run.

Creating a Button for Your Macro

You can make running your macro even easier by creating a button:

1. Insert a shape (e.g., a rectangle) and label it (e.g., “Sort”).
2. Right-click the shape and select Assign Macro.
3. Choose the macro you created (e.g., “Sort”).
4. Now, whenever you click this shape, your data will be sorted automatically.

Creating a Formatting Macro in Excel

Step 1: Recording the Macro

1. Return to the Developer tab.
2. Click Record Macro and name it (e.g., “Format”) in the Macro name box.
3. Click OK to start recording.

Step 2: Formatting Your Data

1. Delete any unnecessary data.
2. Change header colors (e.g., make headers blue and text white).
3. Add borders to the cells.
4. Highlight important data.

Step 3: Stopping the Recording

Return to the Developer tab and click Stop Recording. Your formatting macro is now ready.

Assigning a Button to the Formatting Macro

1. Insert another shape and label it (e.g., “Format”).
2. Right-click this shape and select Assign Macro.
3. Choose the macro you recorded (e.g., “Format”).

Now, whenever you press this button, your data will be formatted as you’ve specified.

Combining Macros in Excel

Though we’ve created separate macros for sorting and formatting, you can combine these tasks into a single macro. Doing so can be useful if you want to automate more complex workflows.

As an example, you may want to consider using a macro to remove dashes. That’s a type of formatting, but it’s more complex than just adding a color. When you make it a macro, you only need to click one button to complete the task on every worksheet in your workbook.

Benefits of Macros in Excel

The biggest benefit of Excel macros? Time savings.

Anytime you find yourself repeating a task in your workbook, consider adding an automation. You can do this, as I mentioned above, by clicking the record button and performing your tasks a single time. Then, click “stop recording” and set up your macro.

You can now use it anytime you want to repeat that task. . . regardless of how complex it is.

When it comes to productivity, that makes macros just as valuable as Excel keyboard shortcuts.

Video Guide: Making Macros with Microsoft Excel

You can also watch this on my YouTube channel.

Video Transcript: Introduction

Hi there! In today’s brief video, I will explain how to create macros in Excel, give a general overview of what they are, how to insert them, and then show some common macros that you can use to help you save time.

Macros in Excel are essentially just a sequence of instructions that automate a repetitive task within Excel that you might have to do frequently. Instead of performing these tasks over and over again by using formulas, you can set up a macro— a set of instructions activated usually by a button you create, or you can run the macro directly from the Developer tab.

The first thing to remember about macros is that you need to have the Developer tab available within Excel. To do that, go to File. You’ll see it in my version; I already have the Developer tab there. But if you don’t see it on your version, go to File, then Options. Next, go to the Customize Ribbon selection and make sure the Developer tab is checked.

Let’s go over a few different macros that you can use. Here is some raw sales data. I’m going to put it on this tab, right here.

Related: Google Sheets Sales Templates

Sorting Macros

Let’s say you wanted to create a macro that sorts the highest amount for this simple sales data from highest to lowest, and you get this data sent to you every few days. To do that, go to the Developer tab. The Developer tab allows you to record a macro.

Click on Record Macro, and it will allow you to name the macro. I’m going to name this one “Sort.” Once you hit OK, you are essentially pressing record. Now, Excel is recording every instance of what I do—whether I use a keystroke, click a button, click my mouse, or enter text, it records all of that.

I want to sort this data. I will go to Data, Sort, and sort it by amount, from largest to smallest.

I’ll hit OK, and you’ll see it sorts the product sales data. That is the macro. Now, go back to the Developer tab and stop recording. The macro has been created. If you click on the Macros screen, you can now see that the “Sort” macro is right there.

I’ll put the old data back in so you can see the macro in action. Here is our sales data. To run this macro, go to Macros, select “Sort,” and run it. It runs the macro. I will put the previous data back in and show you how to use a button to run that macro, so you don’t have to click on the Macros button all the time. To insert a button, insert a shape and name it “Sort.”

Here is our “Sort” button. Right-click on the shape and assign the macro “Sort.” Now, each time you press this button, it will sort your data for you. Let’s put the raw data back in.

Formatting Macros

Now, let’s say I wanted to make a macro that formats this data nicely to present. Go to the Developer tab again and record a macro named “Format.” Now it’s recording everything I do. I want to delete this, make the headers a different color (let’s select blue), make the text white, and add some borders.

This isn’t the most aesthetically pleasing table, but it shows the point of the macro. I also want to highlight certain cells.

Now, stop the macro. If you go to Macros, you will see the “Format” macro is there. Let’s create a new shape for a new button, and we will call it “Format.” Now, I’ll put the previous data back in. If I want to sort it, I press “Sort” because we’ve assigned this button to the sort macro.

we need to assign the “Format” macro to the “Format” button. Assign the macro “Format.”

So, let’s sort it, and then format it—just like that.

You could actually create one single macro that does all of this, but I wanted to show you a couple of different ways macros can help you.

Other ways macros can help you include creating a report from the raw data. A macro can be as simple or as complex as you want it to be.

That is a brief overview of macros. For more advice, check out SpreadsheetPoint.com, and don’t forget to like and subscribe.

Common Questions

Here are a few things that you may want to consider when working with a macro-enabled workbook.

How do I change my macros’ settings?

In the developer menu tab, access your macros. From there, you can view all your existing options or add new macros for automation. This is also where you go to make changes.

How can I stop my coworkers from changing my macros?

Just edit the macros security settings, and you can make sure you’re the only one who can adjust your macros.

How do I add macros in Google Sheets?

I’ve actually got a whole guide on adding macros in Google Sheets. It’s a little different than how it works in Excel. Note that Excel macros don’t require VBA code. It’s an automatic process that happens with the push of a button.

Conclusion

Macros in Excel are a powerful way to save time by automating repetitive tasks. Whether you’re sorting data, applying formats, or creating complex reports, macros can help you achieve your goals efficiently. For more Excel tips and tricks, check out SpreadsheetPoint.com and don’t forget to like and subscribe to our channel.

Happy Exceling!