What is an Excel Macro

An Excel macro is like a recording of tasks you do in Excel. You can play this recording back anytime, doing the work for you. This can be anything simple, like typing names into a sheet, or something more complex, like making a custom chart. If you can do it on Excel, a macro can likely automate it for you.

Here’s how to use a macro:

  • Record it by doing the tasks you want automated. Excel will remember these tasks and save them as a macro.
  • Once saved, you can run this macro whenever you need to, doing the tasks again automatically. You can even set a shortcut key for your macro, making it even quicker to use.

Macros work because of a computer language called Visual Basic for Applications (VBA). When you record a macro, Excel is secretly writing your actions in VBA code. Besides recording, you can also create macros directly using VBA code.

This article will mainly talk about recording macros since it’s the easiest way. Then, we’ll touch on how to edit macros using VBA code, with some tips on where to learn more.

How to Record a Macro

Recording a macro is the first step to automating tasks in Excel, and you don’t need to know any coding. Excel makes it really easy to create macros by just doing what you already know.

Think of it this way: if you can follow a series of steps in Excel to get something done, you’re just a few more steps away from making those actions automatic.

If you’ve ever recorded a video on your phone, you’ve got what it takes to make macros.

With Excel’s Macro Recorder, you start recording, do your usual tasks, and then stop the recording. After that, you can make Excel repeat those steps for you anytime, super fast.

Imagine you’re a singer who can only perform live. Singing the same songs repeatedly would get exhausting. Then, imagine if there was a way to record your song and play it back anytime without extra work. You could even share it with others. That’s how macros work.

Let’s dive into an example.

Example: Creating a Chart from Daily Report Data

Suppose we receive a daily report of data that we are required to turn into a specific type of chart with various features and customizations.

The catch with the data is that some days there are more rows of data than other days.

We want to make the process of chart creation completely dynamic, so we don’t have to create the chart from scratch every time we receive a new day’s file.

Starting the Macro Recorder

To record a macro, click either the “Record Macro” button located in the lower-left corner of the Excel window…

… or select View (tab) -> Macros -> Record Macro.

Adding the Developer Ribbon

If you want to record macros the way the “cool kids” do it, you can activate a special ribbon known as the Developer ribbon.  This ribbon contains the various tools for creating and editing macros.

To activate the Developer ribbon:

  1. Right-click on any visible tab and select “Customize the Ribbon…”.
  2. In the Excel Options dialog box, place a check next to the “Developer” entry on the right panel of options, then click OK.

You now have access to all the cool toys.

Recording a Macro

💡Think through all the steps you are going to perform before creating the recording.  Any unwanted steps performed during the recording process will become part of the playback procedure.  If you happen to make a mistake during the recording process, you can always delete the macro and try again. 

Using one of the methods described above, start the macro recorder.  The following dialog box appears.

  1. Each macro requires a name. The name cannot contain any spaces and must be unique to this file.  We will use “Create_A_Report” as our macro name.
  2. You can assign a shortcut key to the macro for easier execution, such as CTRL + R.
  3. You can store the macro in the file or the Personal Macro Workbook.
  4. You can add a description to the macro for documentation purposes.

Click OK to start the recorder.

Every click and everything typed, edited, or deleted will be recorded.

Notice the icon in the lower-left of the Excel window has changed from a “record” button to a “stop” button.

Let’s perform the following steps to creator our chart that will be reproduced by the “Create_A_Report” macro:

  1. Click in cell A3 to establish the upper-left corner of the data. NOTE: DNOT highlight the data range.  If you do, you will force the macro to always select the defined range.  If later you have data that goes beyond that range, it will be ignored.  If later you have less data, you will end up selecting too many cells and have empty areas in your report.
  2. To properly select the data range so it will be dynamic during future executions, press CTRL-Shift-Down arrow then press CTRL-Shift-Right Arrow.
  1. To create the chart, select Insert (tab) -> Charts (group) -> Combo Chart.

A new combo chart has been inserted into the file.

❗Do NOT click away from the chart and reselect it.  If you do, the chart’s name (ex: “Chart2”) will be hard-coded into the macro which will cause a failure later since the next chart will be a different name like “Chart3”.  If you leave the chart selected, the macro code will refer to it as the “active chart” without any specific name.

  1. Perform any cosmetic changes to the chart you like, such as changing bar colors, add data labels, customize the legend, customize the title, etc.
  2. Click the “Stop” button (bottom-left) to stop the recorder.

Featured Course

Unlock Excel VBA & Excel Macros

Automate ANYTHING you need done in Excel with VBA and macros. Go from Beginner to VBA Expert and design automations with confidence.
Learn More
Excel vba and macros course cover

Testing the Macro on a Different Data Set

To test the macro, select a sheet of new data and launch the macro recording from above.

Macros can be launched in many different ways:

  • Pressing the keyboard shortcut assigned to the macro during the recording process (ex: CTRL + R).
  • Selecting View (tab) -> Macros -> View Macros -> select the macro -> Run.
  • Selecting Developer (tab) -> Code (group) -> Macros -> select the macro -> Run.

You can even assign macros to shapes and images that when clicked will execute the macro. 

Notice that in just a couple of clicks or the press of a keyboard shortcut we have a perfect chart of the new data.

Well, almost perfect.

The chart’s title was hard-coded to display “Procurement Dept”.  We want this to be dynamic and display the text located in cell A1 of the selected sheet.

This change needs to be made in the VBA code as it can’t be recorded dynamically.

How to Edit a Macro with the VBA Editor

To open the VBA Editor, either press CTRL + F11 or click Developer (tab) -> Code (group) -> Visual Basic.

The VBA Editor will open to display the recorded steps as code.  If you don’t see any code in the window, you will need to double-click one of the entries labeled “ModuleX” on the left side of the screen in the Project Explorer window.

Scrolling down through the code we see a line of code towards the bottom that reads…

Selection.Caption = “Procurement Dept”

We need to change this to read…

Selection.Caption = Range(“A1”).Value

I said you can use macros without ever working with the VBA code, but it’s a good idea to learn a little bit of code so you can perform basic maintenance and changes to the macro without having to recreate the entire macro.

Improving the Macro

As it stands, our macro assumes that the sheet it is executed against has data.  If it does not, the macro will result in an error.

We can build in a bit of pre-testing of the sheet to determine if the sheet is empty or has data.  This way, we can inform the user of the situation and not attempt chart generation.  This will be a more elegant and professional way to handle the situation.

This type of testing cannot be created via the macro recorder; it must be written manually.

At the top of the code, enter the following text:

If Range(“A3”).Value <> “Position” or Range(“A4”).Value = “” Then
Exit Sub
Else

Scroll to the bottom of the code text and enter the following:

End If

If you would like to learn more about the VBA language and improve your skills at creating amazingly flexible macros, check out my VBA course using the link below.

Unlock Excel VBA & Excel Macros – XelPlus.com

Testing the Updated Code

To test the macro to ensure the chart title is dynamic and the empty sheet detection works, select a different sheet of data and run the macro.

Notice that the chart title mimics the text located in cell A1.

If we select an empty sheet and run the macro, nothing happens because the sheet contains no data.

How to Save Files with Macros

When you save your file that contains any macros, you cannot save the file with a traditional .XLSX file extension.  The file must be saved with an .XLSM file extension to denote a macro-enabled file.

If you opt to save the file as a traditional .XLSX file, any macros and their VBA code will be discarded.

To save the file without losing your macros, perform the following steps:

  1. In the Backstage (click File), click Export
  2. Click “Change File Type”
  3. Click “Macro-Enabled Workbook (*.xlsm)”
  4. Click “Save As”

Save the file to a location and with a name of your choosing.

Working with Relative References in Macros

VBA can work with relative references in Excel.  If you’d like to see an example of such a scenario, click the following link for a post & video demonstration.

Move Data Sheet to Sheet

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.