Excel Macro Tutorial: How to Record, Run and Choose the Right Tool

Key Takeaways

• An excel macro records a sequence of actions you perform so you can replay them with a single click, saving hours of repetitive work.
• Recording a macro takes just a few steps: name it, perform your actions and click Stop Recording.
• Use a macro when you receive data you can't control; use a template when you create the spreadsheet and control data entry.
• Following security best practices (like saving as .xlsm and only enabling macros from trusted sources) keeps your files safe.

This excel macro tutorial walks you through everything you need to know to record, run and manage macros so you can stop wasting time on repetitive spreadsheet tasks. Whether you format the same report every month or clean up vendor data that arrives in a different layout each time, a macro can do the heavy lifting for you.

A lot of Excel users are confused about when to use macros and when to create templates. A macro is a recording of formatting changes and other steps that can be replayed quickly. A template is a pre-formatted spreadsheet with headers and formulas—awaiting your data. I find that whichever one you know how to create, is the one you tend to use! But just like you might be able to use a shoe for a hammer, it's better to have the right tool for the job!

The goal of both is the same: to restructure the worksheet to fit your specific needs. For example, Ben receives monthly invoices from three different vendors. He then reformats them into one worksheet and imports that into an Access database. Some of the formatting changes include deleting blank rows, inserting a date column, naming a range, changing cell styles, removing duplicates and separating text into columns. This is tedious work and Ben wants a faster way to do it.

The question is should he use a macro or a template? This article covers both options in depth, starting with what a macro actually is, then walking through how to record a macro step by step and finishing with a clear framework for choosing the right tool. You can also check out more in-depth instruction on macros here.

What Is an Excel Macro and Why Should You Use One?

A macro is a recorded set of instructions that automates repetitive tasks in Excel. When you record a macro, Excel writes the steps in VBA (Visual Basic for Applications) behind the scenes, but you do not need to write a single line of code to get started. You simply perform your actions while Excel watches, and it saves those actions so you can replay them whenever you need to.

Macros are saved in macro-enabled files with the .xlsm extension. Once saved, you can run the macro with a single click and complete in seconds what used to take minutes or even hours.

Here are some of the most common use cases for macros:

• Formatting cells, rows and columns to match a standard layout
• Deleting blank rows or columns from imported data
• Inserting new columns (such as a date column) across multiple sheets
• Removing duplicates from large data sets
• Splitting text into separate columns
• Generating recurring reports with consistent formatting

If you find yourself performing the same sequence of steps more than once or twice, a macro is likely the right solution.

How to Record and Run a Macro in Excel

This is where the real time savings begin. Recording a macro is as easy as recording a voicemail message! Below is a detailed walkthrough so you can follow along in your own workbook.

1. Go to the View Tab

Open the workbook where you want to automate a task. Navigate to the View tab on the ribbon, click Macros and select Record Macro from the dropdown.

2. Name Your Macro

In the Record Macro dialog box, give your macro a descriptive name. The name must start with a letter and cannot contain spaces. Use underscores to separate words (for example, Clean_Vendor_Data).

3. Assign a Keyboard Shortcut (Optional)

You can assign a shortcut key so you can run the macro quickly. Choose a Ctrl + letter combination that does not conflict with built-in Excel shortcuts.

4. Choose Where to Store It

Select a storage location from the Store Macro In dropdown. Your options are This Workbook, New Workbook or Personal Macro Workbook. Choose Personal Macro Workbook if you want the macro available every time you open Excel.

5. Click OK and Perform Your Actions

Once you click OK, Excel begins recording. Perform every step you want to automate, such as deleting blank rows, changing cell styles or inserting a column. Excel captures each action in order.

6. Click Stop Recording

When you have finished, click the Stop Recording button on the status bar at the bottom of the screen. Your macro is now saved and ready to use.

How to Run Your Macro

When it's time to play the macro, go back to the View tab, click Macros and select View Macros. Select the one you want and click Run. All the steps run in order!

You can also run a macro using the keyboard shortcut you assigned during recording. For even faster access, add a macro button to the Quick Access Toolbar so it is always one click away.

When you create a macro with lots of steps, you might consider creating several little macros and one final macro to make all the previous macros run in order. This keeps each macro focused and easier to troubleshoot if something goes wrong.

Best Practices and Common Pitfalls

Following a few best practices will help you avoid headaches and keep your files secure:

• Save your file as .xlsm before you start recording. A standard .xlsx file cannot store macros and you will lose your work.
• Use relative references when your macro needs to work on data that may start in different cells. Toggle this option under View > Macros > Use Relative References.
• Break complex automations into smaller macros. Smaller macros are easier to test, debug and reuse.
• Always test on a copy of your data first. A macro cannot be undone with Ctrl + Z once it runs.
• Only enable macros from trusted sources. Macro-enabled files from unknown senders can contain malicious code, so keep your Trust Center settings strict.

These precautions address the most common disadvantages of macros in Excel, including security risks and difficulty debugging long recordings.

When to Use a Macro vs. a Template

Now that you know how to record and run a macro, the next question is whether a macro is actually the right tool for your situation, or whether a template would serve you better. Here is a side-by-side comparison:

Here is a good rule of thumb! Create a template when you create the spreadsheet and control the data entry. If you already have the spreadsheet with all the formatting and structure, but it's full of data, simply delete the data and under File, Save As, save the file as a template. When it's time to create the new worksheet, go to File, New and find the template under Personal.

Use a macro when you don't have any control over how the data is originally formatted. Think back to Ben's invoice scenario: his three vendors are NOT going to change their invoice structure for one customer. A macro lets Ben automate the cleanup, no matter how the data arrives.

Choose a macro if:

• You receive data from external sources in inconsistent formats
• You need to repeat the same formatting or cleanup steps on different files
• You want to chain multiple actions into a single automated workflow

Choose a template if:

• You build the spreadsheet from scratch and control the layout
• You want consistent headers, formulas and formatting every time you start a new file
• You do not need to transform or restructure incoming data

Bottom line is if you create and update the worksheets, use a template to create the structure. If you receive the worksheets regularly and they are not set up the same, use a macro to restructure them. That way you can have the right tool for either job!

Pryor Learning offers hands-on courses that cover both Excel macros and templates in depth. Whether you are just getting started or ready to dive into VBA, Pryor has training designed to meet you where you are.