How to Make a Macro in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Excel is packed with great ways to save time like keyboard shortcuts, templates, the fill handle, and so on…

… but there’s no better time-saving tool than a macro!

In short: a macro is a sequence of instructions that Excel execute when you tell it to.

And that opens up a huge number of possibilities.

In this tutorial, you learn what a macro is, how to create one of your own, and how to run it.

In just a few minutes, you’ll be ready to start making your own macros!

Kasper Langmann, Co-founder of Spreadsheeto

What are macros in Excel?

A macro is simply a series of instructions. After you’ve created a macro, Excel will execute those instructions, step-by-step, on any data that you give it.

For example, we could have a macro that tells Excel to take a number, add two, multiply by five, and return the modulus.

Now, whenever we tell Excel to run that macro, we don’t have to manually do each step; Excel will do them all.

You can record almost anything in a macro. Numerical operations, text operations, formatting, moving cells—the choices are nearly unlimited.

Kasper Langmann, Co-founder of Spreadsheeto

It might not seem like a big deal at first, but macros can save you a huge amount of time if you do the same series of steps on a regular basis.

It might be formatting raw data, filtering and sorting information, or applying the same series of functions and operations to your sheets.

And they’re great for sharing, too—because macros can be stored in Excel spreadsheets, you don’t need to worry about sending additional files to your colleagues. You can just write a macro, send the spreadsheet over, and let them work with it.

If you work with spreadsheets on a regular basis, there’s a good chance you could save a lot of time by working with macros.

Let’s get started by recording an example macro.

Get your FREE exercise file

If you’d like to follow along and practice creating macros, you can use this exercise file.

It contains all of the data that we’ll be working on so you can try your hand at creating macros.

Download it below and let’s get started!

Download the FREE Exercise File

Download exercise file
Download free exercise file

How to record a macro in Excel

To start automating your Excel actions with macros, you’ll need to “record” a macro.

Recording a macro is how you tell Excel which steps to take when you run the macro.

And while you can code a macro using Visual Basic for Applications (VBA), Excel also lets you record a macro by using standard commands.

Let’s take a look at a basic example. In our spreadsheet, we have a list of names and a corresponding list of their sales for the month:

sales-spreadsheet

We’ll record a macro that sorts the sales from highest to lowest, copies the information of the most successful salesperson, and changes the formatting to make that information stand out.

Before we get started, we’ll need to make sure that the Developer tab is visible.

Head to File > Options, and select Customize Ribbon in the sidebar. Then, in Main Tabs, make sure that Developer is checked:

developer-tab

Click OK, and open up that tab in the main window. You’ll see a button labeled Record Macro.

To start recording a macro, just click that button.

record-macro-button

You’ll be asked to name your macro (we’ve named ours “HighSales”), and enter a shortcut key if you choose.

Keep in mind that there are already a lot of Ctrl-based shortcuts, so try not to overwrite any of those that you use regularly.

save-macro

You can also choose to save the macro in one of three places: the current workbook, a new workbook, or your personal macro workbooks.

The first two are self-explanatory, but the third requires a bit of explanation.

Kasper Langmann, Co-founder of Spreadsheeto

Your personal macro workbook (personal.xlsb) is a place where you can store macros to run on any workbook that you open in Excel. It’s a centralized storage location for all of your Excel macros.

If you’re recording a macro that will only be applicable to the current spreadsheet, save it there. But if you think you may want to reuse that macro on another workbook, keep it in your personal macro workbook for easier access.

After you click OK, Excel will record everything you do.

Here are the steps we’re going to take:

  1. Enable filtering
  2. Sort column B from largest to smallest
  3. Copy cell B2
  4. Paste in cell E2
  5. Copy cell A2
  6. Paste in cell E3
  7. Make cells E2 and E3 bold

All we need to do is click Record Macro, take those actions, and then click Stop Recording.

The Stop Recording button replaces the Record Macro button when you start recording.

Kasper Langmann, Co-founder of Spreadsheeto

All of those steps can be done in the normal way; clicking the Filter button, sorting with the dropdown filter arrow, pressing Ctrl + C to copy the cell, and so on.

Here’s what it looks like when we’re done:

highest-sales

And that’s all you need to do to record a macro! Just hit record, take some actions, and then stop recording.

How to run a macro in Excel

After you’ve saved your macro, you can run it in a number of ways.

First, you can run it from the Ribbon. In both the View and Developer tabs, you’ll see a button labeled Macros. Click that button to see your macros.

macros-button

From the resulting window, you can select and run any macro that you’ve saved. Just select it and hit Run.

Excel will repeat the steps that you took during recording.

To see if it worked in the example spreadsheet, unbold and delete cells E2 and E3, then sort the names alphabetically.

When you run the macro, you should get the same result.

Kasper Langmann, Co-founder of Spreadsheeto

If you designated a shortcut key when you saved your macro, you can also run it that way. Hit Ctrl and the key you put in the save box.

If you want to add a shortcut key later, just go to View Macros, select the macro, and click Options. You’ll be able to designate a new shortcut key.

Kasper Langmann, Co-founder of Spreadsheeto
macro-options

If you have a complicated macro that you run often, or you’re sharing your spreadsheet with other people, you can create a button on the spreadsheet that will run the macro.

Here’s how to do that.

First, create a shape; for our example, we’ll use a rectangle with rounded corners.

insert-shape

Add text to the shape indicating what it does by right-clicking and selecting Edit Text:

edit-shape-text

Now, right-click the shape and select Assign Macro.

assign-macro

Select the corresponding macro from the resulting window and click OK.

Now, whenever you click on that shape, Excel will run your recorded macro!

run-macro-button

Finally, you can run macros from the Quick Access Toolbar.

You’ll need to add the View Macros button to the Quick Access Toolbar first, though.

Head to File > Options and select Quick Access Toolbar in the left pane:

qat-view-macros

Scroll to the bottom of the list, select View Macros, and click Add >> to add it to the Quick Access Toolbar.

Click OK.

Now, when you want to run a macro, just click the macros button at the top of the Excel window:

qat-view-macros-button

Start saving time with macros

While there are numerous steps involved, recording and running Excel macros becomes second nature once you’ve done it a few times.

And once you’ve created a few macros that you can fall back on, you’ll find that you’re able to work with your spreadsheets much faster.

2019-05-16T11:30:05+00:00