Don’t forget to share this post

Macros are a great tool to automate repetitive tasks in Excel. By implementing key macros into your spreadsheet, not only do you streamline the production and management of your data through automation, but you also avoid tasking manual inputs, such as dealing with complex formulas. Overall, macros are a great solution to greatly reducing your turnaround and human effort.

In this article, we’ll show you how to use macros in Excel to automate your workflow. You’ll learn how to make, build or create your own macros in Excel, and how to add macros in Excel, so you can get the best custom functionality to automate the processes that will make your work life easier.

What are macros in Excel?

A macro is an action or a group of actions that will complete automatically according to your specifications.

Macros (Microsoft Office Macros) are a very powerful tool for automating predictable, repetitive tasks in your office workflow. A macro is a piece of Visual Basic for Applications (VBA) code that is saved inside your document. Once you know how to use (and create) them, you’ll find you can apply macros to a wide range of tasks to free up time to focus on the deep work that will really set your business apart.

There are many different circumstances in which you’d want to use macros to automate parts of your work. Here are just some examples:

  • Changing the style and formatting of your data.
  • Creating new documents.
  • Linking with other data sources (databases, text files, and more).
  • Altering data and text.

If you’ve never used macros before, then using Layer might be the best alternative for you - Layer is an easy-to-use automation tool that can achieve the same as macros and more in just a few simple steps.

Transfer Data From One Excel Worksheet to Another Automatically

Excel worksheets or workbooks can be linked to automatically update data. Here's how to transfer data from one Excel worksheet to another automatically

READ MORE
Transfer Data From One Excel Worksheet to Another Automatically

How to create macros in Excel?

Before you can start using macros in Excel, you need to create one. Here’s how.

Let’s say you want your workbook to automatically total the data from each row and the format and style to be consistent for all your workbooks. Instead of wasting time on these tedious actions, you can build a macro to do this for you without the risk of human error.

  1. 1. Open your Excel workbook containing your data. In the toolbar, click View > Record Macro.
How to Use Macros in Excel Record Macro
How to Use Macros in Excel - Record macro
  1. 2. Name your macro (one word only, no spaces). You can also choose to assign a keyboard shortcut for your macro and add a description (here, you can add anything such as an overview of what the macro does or when you should use it). Click “Ok”.
How to Use Macros in Excel Name Macro
How to Use Macros in Excel - Name macro
  1. 3. Now carry out all of your desired actions. The macro will record everything you do. Here is an example of the changes I want to make to my Excel; adding a new column for total sales, adding currency formatting to my columns, and formatting the header rows. You can also add formulas and other numbers, text, and cell formatting to your dataset.
How to Use Macros in Excel Create new macro
  1. 4. Once you have applied all of your changes, click “Stop recording” in the “View” menu.
How to Use Macros in Excel Stop recording macro

Please note: An alternative way to stop recording a macro is to click the icon in the bottom or top left-hand corner of your workbook (depending on whether you have the Excel app or the online version).

How to use macros in Excel?

Before you can use the macro you’ve just created, it’s vital that you perform the following steps; delete your cells, then save the file in a macro-enabled format.

You must delete your data before saving as, otherwise, when you run the macro, it will remember this specific data and add it to any future workbooks. Deleting this data now means that the macro will only remember the actions, not the data.

You then have to save the workbook in a macro-enabled format - ideally XLTM. If you save it as another format, like XLTX, the macros won’t be able to run.

  1. 1. Select all of your data and delete your cells. Right-click and select “Delete” - this is the only way to delete all values and formatting.
How to Use Macros in Excel Save macro
  1. 2. Save as a macro-enabled format by heading to File > Save as. Choose the “Excel Macro-enabled Template” (XLTM) or a macro-supportive alternative. Click “Save”.
How to Use Macros in Excel Save macro file XLTM
How to Combine Multiple Excel Files Into One
How to Combine Multiple Excel Files Into One

Discover the most popular methods used to manually or automatically combine multiple Excel spreadsheets and data inputs into one master file

READ MORE

How to run macros in Excel?

Depending on how you assign it, you may be able to run macros by pressing a combination short-cut key, a button on the Quick Access Toolbar, or in a custom group on the control ribbon. You can also assign a macro to run automatically when you open your Excel workbook.

Now that you’ve created a macro-enabled template, it’s time to run it. Here’s how:

  1. 1. Open your newly-created Excel template file. With standard security settings, you will see a warning notification stating that macros are disabled. You should click “Enable Macros”.
How to Use Macros in Excel Enable macros Excel

Please note: You may not always want to enable content. If you receive a file from a third party, for example, they may be running a macro that could damage or delete your data. This one is safe, however, as we created it ourselves.

  1. 2. Import your data into the workbook. You can import from another Excel file or other formats like CSV. In the toolbar, click Data > Get Data and choose the option that best suits your needs.
How to Use Macros in Excel Import data macro excel

In this example, I’ll select “Get Data…” to import data from another Excel file. Once I’ve located my Excel file, I’ll click “Get data”.

How to Use Macros in Excel Macros in Excel
How to Use Macros in Excel - Macros in Excel
  1. 3. Click the checkbox of the sheet(s) you want to import. When finished, click the “Load” button on the bottom-left-hand corner of the screen.
How to Use Macros in Excel Get data macros
  1. 4. Now that your data is in the spreadsheet, go to View > View Macros. Click on the macro you created previously and click “Run”.
How to Use Macros in Excel Run macro Excel

Alternatively, if you added a keyboard shortcut for your macro, press the combination now to run the Macro.

As you can see from the screenshot below, the macro has automatically applied the actions previously recorded. My new data now has a new ‘Total monthly sales’ column, currency formatting has been applied to my columns, and the headers have also been formatted.

How to Use Macros in Excel Build and run macro

How to automate Google Sheets tasks?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

Conclusion

Although macros are not widely known or used by the majority of Excel users, they are an incredibly intelligent feature that could streamline the day-to-day operations of many Excel users. What’s more, we’ve seen that macros aren’t as intimidating to set up and run as they might seem.

Here we’ve shown how you can create/record macros in your spreadsheet, how to save them correctly, and how to run them on other datasets. Finally, we’ve also shown how this process can be even easier and more intuitive with Layer.

Looking for more articles on how to make the most of Excel? Take a look at how to transfer data from one Excel worksheet to another automatically.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Feb 28 2022, Updated Sep 23 2022