Excel Macros Tutorial: Automate Formatting, Reporting, and Data Entry

Excel macros represent one of the most valuable yet underutilized tools in Microsoft Excel's powerful arsenal. If you've ever found yourself performing the same sequence of actions repeatedly like formatting data, creating charts, or generating reports, then macros can transform your Excel workflow by automating these tasks with just a single click or keyboard shortcut.

Macros work by recording our actions as we perform them in Excel, creating a reusable sequence that executes those exact steps whenever we need them. For junior data practitioners, mastering macros often marks an important step toward greater productivity and confidence in handling larger datasets. 

Common applications include automating regular reports, standardizing data formatting across multiple sheets, creating custom data entry forms, and performing complex data transformations that would be tedious to execute manually. 

While the term "macro" might sound technical, Excel's built-in macro recorder makes creation accessible even to those with no programming background.

In this article, we'll look at how to create, use macros in Excel, and run macros efficiently, along with examples. If you’re looking for hands-on exercises to improve your Excel skills, check out our Excel Fundamentals track. 

What Are Macros in Excel?

Macros in Excel are essentially recorded sequences of commands and actions that automate repetitive tasks in our spreadsheets. Think of them as mini-programs that remember exactly what we did and can repeat those steps perfectly every time. This automation capability transforms how we work with data, especially when dealing with recurring tasks. When you’re learning Excel from scratch, macros can be incredibly useful. 

Unlike manual processes where we might click through dozens of menu options and commands, macros condense these actions into a single button click or keyboard shortcut. The importance of Excel macros lies in their ability to execute complex, multi-step processes consistently without introducing human error.

For example, if you regularly format sales reports by applying specific number formats, inserting formulas, creating pivot tables, and generating charts, then a macro can perform all these steps automatically. What might take you 15 minutes manually can be reduced to seconds, and the results will be identical every time.

Macros work through VBA (Visual Basic for Applications), Microsoft's programming language, which is built into Office applications. However, we don't need to know VBA to create basic macros. Excel's macro recorder translates our actions into code behind the scenes.

This automation capability is particularly valuable for data practitioners who handle large datasets or perform repetitive analyses. By eliminating tedious manual work, macros allow us to focus on data interpretation and decision-making rather than mechanical spreadsheet manipulation.

In the next section, we’ll look at how to enable macros in Excel.

How to Enable Macros in Excel

Before creating your first macro, you'll need to enable macros in Excel's settings. Microsoft disables macros by default as a security measure, since malicious macros can potentially harm your computer. However, enabling them safely for your own use is straightforward.

Excel provides several security levels that control how macros function in your workbooks. These settings can be found in the Trust Center, Excel's security and privacy control panel. Here's how to access and configure these settings properly:

1. Click the File tab in the Excel ribbon
2. Select Options at the bottom of the left panel

3. In the Excel Options dialog box, select Trust Center from the left sidebar
4. Click the Trust Center Settings button

5. Select Macro Settings from the left panel
6. Choose your preferred security level:
• Disable VBA macros without notification: Blocks all macros silently
• Disable VBA macros with notification: Blocks macros but shows a security alert allowing you to enable them for trusted files (recommended)
• Disable VBA macros except digitally signed macros: Only allows macros that have a digital security certificate
• Enable VBA macros: Allows all macros to run (not recommended due to security risks)

For most data practitioners, the "Disable VBA macros with notification" setting offers the best balance between security and convenience. With this option, Excel will alert you when opening a workbook containing macros, giving you the option to enable them for files you trust.

Another important step is enabling the Developer tab in the Excel ribbon, which provides direct access to macro tools. To display this tab:

1. Go to File > Options
2. Select Customize Ribbon from the left panel
3. In the right column under "Main Tabs," check the box for Developer
4. Click OK

While enabling macros increases Excel's capabilities dramatically, it's important to be cautious about security. Only enable macros in workbooks from trusted sources, as malicious macros can potentially harm your system or compromise your data. Creating your own macros is always the safest approach, ensuring both security and functionality tailored to your specific needs.

Creating and Using Macros in Excel

In this next section, we’ll look at how to create and use macros in Excel.

How to create macros in Excel

Creating macros doesn't require coding knowledge, thanks to Excel's built-in macro recorder. This tool observes your actions and translates them into VBA code automatically. Let's explore how to record your first macro with a simple step-by-step example:

1. Prepare your workflow: Before recording, practice the steps you want to automate to ensure smooth recording.
2. Start the recorder: Click the Developer tab in the ribbon, then select Record Macro in the Code group.

3. Configure macro settings: In the Record Macro dialog box that appears, you'll need to:
• Enter a name for your macro (no spaces allowed)
• Assign a keyboard shortcut (optional but recommended)
• Choose where to store the macro (This Workbook, Personal Macro Workbook, or New Workbook)
• Add a description to help you remember the macro's purpose

4. Record your actions: Once you click OK, Excel starts recording all your actions. Perform the tasks you want to automate, working deliberately and precisely.
5. Stop recording: When you've completed all steps, return to the Developer tab and click Stop Recording.

That's it! Your macro is now saved and ready to use whenever needed. Let's look at a practical example:

Suppose you frequently need to format raw data exports with the following steps:

• Add bold formatting to headers
• Apply alternating row colors for readability
• Adjust column widths to fit the content
• Add filters to all columns

Instead of performing these steps manually each time, you can record them as a macro and execute the entire sequence with a single shortcut. This simple automation can save several minutes per report—time that quickly adds up when working with multiple datasets daily.

When recording macros, it's best to use relative references for operations that should adapt to different positions in your spreadsheet. To enable this, click Use Relative References in the Developer tab before recording.

Relative references ensure that macro actions are performed relative to the currently active cell rather than always at the exact same cell address.

For more complex tasks, you might need to break down your workflow into smaller, focused macros rather than trying to record one massive automation. This modular approach makes your macros easier to troubleshoot and more adaptable to different situations.

Next, let’s see how to use macros in Excel.

How to use macros in Excel

Once you've created your macros, executing them is straightforward. Excel offers multiple ways to run your saved automation, allowing you to choose the method that best fits your workflow. Here are the primary approaches to running macros in Excel:

Running macros from the Developer tab

The most direct way to access all your macros is through the Developer tab. This method gives you a complete view of available macros and is ideal when you need to run occasional automations.

1. Click the Developer tab in the ribbon
2. Select Macros in the Code group (or press Alt+F8 as a shortcut)
3. Choose the macro you want to run from the list
4. Click Run

This approach lets you see all macros available in your current workbook, personal macro workbook, and any open workbooks. The dialog also provides access to edit, delete, or create options for your macros.

The Personal Macro Workbook stores macros that you can use in any Excel workbook, while choosing 'This Workbook' limits macros to the current file.

Running macros with keyboard shortcuts

For macros you use frequently, keyboard shortcuts offer the fastest execution method. Having an Excel cheat sheet handy can be incredibly beneficial when learning or using macros and their associated shortcuts. 

If you assigned a shortcut key when creating the macro (such as Ctrl+Shift+F), simply press that key combination anywhere in Excel to run the associated macro instantly. 

When creating keyboard shortcuts for macros, try to:

• Choose combinations that are easy to remember
• Avoid overriding Excel's built-in shortcuts
• Use consistent patterns for related macros (e.g., Ctrl+Shift+1, Ctrl+Shift+2)

Be aware that Excel shortcuts can differ between Windows and Mac versions, potentially leading to conflicts or unintended behaviors.

Adding macros to the Quick Access toolbar

For visual access without using the Developer tab, you can add your most-used macros to Excel's Quick Access Toolbar:

1. Right-click the Quick Access Toolbar and select Customize Quick Access Toolbar
2. In the dialog box, select Macros from the "Choose commands from" dropdown
3. Select your macro and click Add
4. Click OK

Once added, your macro appears as a button in the Quick Access Toolbar for one-click access.

Creating macro buttons in your worksheet

For the most user-friendly approach, especially when creating workbooks for colleagues, you can create clickable buttons directly in your worksheet:

1. Go to the Developer tab
2. Click Insert in the Controls group
3. Select the Button control under Form Controls
4. Draw the button on your worksheet
5. When prompted, select the macro to assign to this button
6. Right-click the button to customize its text or appearance

This method is particularly useful when creating worksheets for users who might be unfamiliar with macros, as it provides clear visual cues for automation features.

By choosing the right execution method for your needs, you can seamlessly integrate macros into your Excel workflow and maximize the efficiency gains from your automation. Next, let’s look at some examples.

Excel Macros Examples

Now that you understand how to create and use macros in Excel, let's explore some practical examples that demonstrate their power for data practitioners. These examples will show you how macros can automate common tasks and save you significant time in your daily work.

Automating formatting tasks

One of the most common uses for macros is to standardize the appearance of your data.

Formatting Macro Example:

This macro applies several formatting operations in one go:

• Adds bold formatting to headers
• Applies alternating row colors for better readability
• Adjust all column widths to fit the content
• Adds filters to all columns for easier data analysis

For junior data practitioners who frequently receive raw data exports, this single macro can transform plain spreadsheets into analysis-ready reports with just one click.

Creating a simple data entry form

Data entry forms can significantly improve accuracy and efficiency when collecting information.

A data entry form macro creates a user-friendly interface that:

• Collects information in a structured format
• Validate entries to prevent errors
• Transfers the data to the appropriate worksheet
• Clears the form after submission for the next entry

This approach reduces data entry errors and speeds up the process, especially when working with team members who may not be Excel experts.

Automating report generation

Report generation macros can save hours of repetitive work when creating recurring reports.

A report generation macro might:

• Format raw data into a structured table
• Calculate key metrics and summaries
• Generate relevant charts based on the data
• Apply consistent branding and formatting
• Add headers, footers, and timestamps

For aspiring data practitioners, this type of automation demonstrates valuable technical skills while dramatically increasing productivity. Next, we’ll look at how to manage and edit the Excel macros.

If you want to learn data analysis with Excel, check the course, Data Analysis in Excel. 

Managing and Editing Excel Macros

After creating macros, we'll need to know how to manage, edit, and, if necessary, remove them. Let’s see how to do these operations.

How to disable macros in Excel

There are occasions when you might need to temporarily disable macros in Excel, particularly when working with workbooks from untrusted sources or when troubleshooting issues. Excel provides several options for controlling macros execution. Let’s look at the steps to disable macros in Excel.

Steps to Disable Macros in Excel:

1. Click the File tab in the Excel ribbon
2. Select Options at the bottom of the left panel
3. In the Excel Options dialog box, select Trust Center from the left sidebar
4. Click the Trust Center Settings button
5. Select Macro Settings from the left panel
6. Choose one of these security levels:
• Disable VBA  macros without notification: Blocks all macros silently
• Disable VBA macros with notification: Blocks macros but shows an alert (default)
• Disable VBA macros except digitally signed macros: Only allows macros with digital certificates. Digitally signed macros require obtaining a digital certificate, typically used by organizations to ensure authenticity.
• Enable VBA macros: Allows all macros to run (not recommended)
7. Click OK to apply your changes

If you need to temporarily disable macros for a specific workbook without changing your global settings, you can hold down the Shift key while opening the workbook. This prevents any automatic macros from running when the file opens.

For data practitioners who frequently work with external files, understanding these security settings is crucial for maintaining both productivity and security. The recommended approach is to use the "Disable VBA macros with notification" setting, which provides a security warning prompt allowing you to enable macros only for workbooks you trust.

Remember that each time you open a workbook containing macros with this setting, you'll need to click "Enable Content" in the security warning bar if you want to use the macros in that file. This extra step serves as an important security checkpoint to protect your system from potentially harmful code. Next, let’s see how to remove macros from Excel.

How to remove macros from Excel

Over time, we may find that some macros we've created are no longer needed or perhaps we want to clean up a workbook before sharing it with colleagues. Knowing how to properly remove macros from Excel is important for maintaining organized and efficient workbooks.

There are several methods for removing macros from Excel workbooks, depending on whether we want to remove specific macros or all VBA code from a workbook. Here's how to handle both scenarios efficiently:

Deleting individual macros from the Macro Manager

When you want to remove specific macros while keeping others intact, the Macro Manager provides the simplest solution:

1. Open the Excel workbook containing the macros you want to remove
2. Click the Developer tab in the ribbon
3. Click Macros in the Code group (or press Alt+F8 as a keyboard shortcut)
4. In the Macro dialog box that appears, select the macro you want to delete
5. Click the Delete button
6. Confirm the deletion when prompted
7. Repeat for any additional macros you want to remove

This approach gives you precise control over which automation you keep and which you remove, allowing you to curate your macro collection as your needs evolve.

Removing all macros from a workbook

If we need to remove all macros from a workbook, perhaps before sharing a file with external stakeholders or when cleaning up a template, then we can do this by saving the file in a macro-free format:

1. Open the workbook containing the macros
2. Click the File tab and select Save As
3. Choose a location for the new file
4. In the "Save as type" dropdown, select Excel Workbook (*.xlsx)
5. Give your file a name (or keep the original name)
6. Click Save

The standard Excel Workbook format (.xlsx) doesn't support macros, so saving in this format automatically strips all macros from your file. The original macro-enabled workbook (.xlsm) remains unchanged unless you overwrite it.

Note that saving your workbook in a macro-free format permanently deletes all macros. Always maintain a backup if you anticipate needing them again.

Security considerations when removing macros

When handling macro removal, it's important to consider security best practices:

• Always save a backup copy of your workbook before removing macros, especially if they contain complex automation you might need in the future.
• If you're removing macros for security reasons, verify the removal by checking the file extension (should be .xlsx) and trying to access the Developer tab's Macro tool.
• After removing macros from a workbook intended for distribution, test the file on another computer to ensure no macro code remains.

By properly managing and removing unnecessary macros, you can maintain cleaner, more efficient workbooks while ensuring appropriate security measures for your Excel files. 

Next Steps: Introduction to VBA for More Advanced Automation

While Excel's macro recorder is powerful for basic automation, understanding VBA (Visual Basic for Applications) opens up virtually limitless possibilities for customizing Excel to your exact needs.

VBA is Microsoft's programming language that is built into Office applications. Unlike recorded macros, which simply replay exact steps, VBA code can make decisions using conditional logic, process data using loops and variables, interact with users through custom forms and dialogs, and connect Excel with other applications like Word or Outlook. 

Even without prior programming experience, many Excel users can learn basic VBA concepts by first recording macros and then examining the generated code. You can learn more about VBA and how to use it with our VBA in Excel tutorial. 

Consider using custom VBA code when you need to process variable amounts of data or your task requires decision-making logic. VBA becomes essential when you want to create custom user interfaces, need to handle errors gracefully, or are automating the same tasks repeatedly with slight variations.

Conclusion

Excel macros transform how data practitioners interact with spreadsheets, dramatically boosting productivity and accuracy. Macros automate repetitive tasks so that we can focus on data analysis and insights rather than mechanical spreadsheet manipulation. 

If you're formatting data, creating reports, or performing complex calculations, then macros offer a pathway to more efficient workflows. Start small with simple formatting macros, then gradually expand your skills to tackle more complex challenges.

Ready to take your Excel skills further? Explore DataCamp's Excel Fundamentals track and get hands-on experience with using Excel, from preparing data to writing formulas and creating visualizations. 

Struggling to choose between PowerBI and Excel? Check this tutorial: Power BI vs Excel: Which Should You Use?