How Custom Excel Macros Can Streamline Your Workflow

Use Macros to Speed up Your Routine Data Tasks

Businessman using laptop and computer to perform repetitive tasks

 FS Productions / Getty Images

An Excel macro is a set of programming instructions stored as a procedure. Macros eliminate the repetition of steps for commonly performed tasks and can be triggered by a keyboard shortcut, toolbar icon, or a button added to the worksheet itself.

The information in this article applies to Excel versions 2019, 2016, 2013, 2010, and Excel for Mac.

When to Use a Macro

Macros can make your spreadsheets easier to work with by automating repetitive tasks. These tasks include everything from complex calculations that require the use of formulas to simple formatting tasks. One example of a repetitive task is adding number formatting to new data. Another example is applying cell and worksheet formats such as borders and shading.

Screenshot of Excel showing macro buttons

Other repetitive tasks for which macros are useful include:

  • Adding or removing rows and columns
  • Protecting or unprotecting worksheets
  • Selecting a range of cells
  • Adding the current date to a worksheet

Macros vs. Templates

Using macros can be a great time saver for repetitive tasks. If you routinely add certain formatting features or content (such as headings or a company logo) to new worksheets, create and save a template file containing these items rather than recreating them each time you start a new worksheet.

Excel's Macro Recorder

Excel includes a built-in macro recorder that records a series of steps using the keyboard and mouse. Excel then converts this series of steps into VBA code. The Macro Recorder and Macro Manager can be found on the View tab. Adding buttons to your worksheet requires adding the Developer tab.

Add the Developer Tab to Excel

The steps for revealing the developer tab in Excel are straightforward and can be followed whether you have a Mac or a Windows PC.

Screenshot of Excel showing the Developer tab option
  1. Windows: Select File.

    Mac: Select the Excel menu.

  2. Windows: Select Options.

    Mac: Select Preferences.

  3. Windows: Select Customize Ribbon.

    Mac: Select Ribbon & Toolbar.

  4. On the right-hand side of the window, under the Customize the Ribbon section, locate the Developer option and add a checkmark to its adjacent box.

  5. Select Save to close the window and display the Developer tab in Excel.

Create a Basic Macro in Excel

Let's create a basic Excel macro that fills a cell with the color red, centers the text, and adds bold formatting to the text. Even though the Record Macro option is available in the Developer tab, we will use the View tab to make the process easier.

Screenshot of Excel showing the recording of a macro
  1. Select View.

  2. Select Macros > Record Macro.

  3. Under Macro name, enter a name for the macro. Feel free to follow our lead for this example and call it Emphasize.

  4. Under Store macro in, select This Workbook to keep the macro attached to the current file.

  5. To create a shortcut key, enter a letter or number that, when pressed in combination with the other noted keys, will run the macro. For this example, we use the letter E.

  6. Type a description of the macro. We will leave it blank for this example.

    The description reminds you of what the macro does when it runs. If you plan to create several macros, fill out the description so you can quickly select the right one for the task.

  7. Select OK.

  8. The macro begins recording. Any changes made to your spreadsheet are recorded.

  9. Select a cell. This is the cell that will be changed on the spreadsheet and where the macro will be recorded.

  10. From the Home tab, select Bold to emphasize the cell text, select Center to align the cell contents, and select Fill Color to change the cell background to red.

  11. Select View and select Stop Recording. Your macro is created and stored in the workbook.

Run a Created Macro in Excel

When a macro is assigned a shortcut key combination, apply the macro by selecting a cell and pressing the keyboard combination. If you didn't choose a shortcut combination, select View and select View Macros. Any macro can be run by selecting it and selecting Run.

Create a Macro Button in Excel

Macros can be run with a keyboard shortcut. Or you can create a custom button for your spreadsheet that, when pressed, runs the specified macro.

Screenshot of Excel showing a macro button

Follow this example to create a basic macro button in Excel for the previously created Emphasize macro.

The Developer tab must be enabled. Use the steps previously mentioned in this article to add the Developer tab to Excel.

  1. Select Developer.

  2. In the Controls group, select Insert > Button.

  3. Select the cell on your worksheet where you want to place the button. The button can be moved and altered after it is added to the worksheet.

  4. Select the macro from the list that you wish to run when the button is pressed. For our example, select the Emphasize macro.

  5. Select OK.

  6. A button appears on your worksheet that you can move or resize. To change the text shown on the button, double-click it then enter the desired text.

  7. Select a cell and then select the button to run the macro.