What is a Macro in Excel

Updated on: December 30, 2022

You can find some hide tools extremely handy in Excel that you don't know yet. One of them is a "macro," which is a piece of code within Excel that allows you to automate tasks like plugging formulas, formatting cells, highlighting rows or columns, and so on. 

Note: Don't confuse a "macro" with a programming language.

Before setting a macro, you should know you don't need to be a programmer expert to use one. So relax and see how useful a macro can be because it's simpler than you might guess.

How to enable a macro

First and foremost, you need to check if you have access to some buttons and functions that are completely necessary for building a macro.

By default, Excel will hide the "Developer" tab but don't worry if you can't find it in the toolbar; you can enable it with the following steps:

  1. For PC:
    1. Click on "File."
    2. Select "Options."
    3. Pick the "Customize Ribbon" option within the menu.
    4. Check the box for the "Developer" tab.
    5. Hit "Okay" to confirm.
  2. For Mac:
    1. Go to the upper left corner and click on the word "Excel" (next to the Apple logo).
    2. Select "Preferences."
    3. In the Preferences contextual menu, hit "View."
    4. Check the box for the "Developer" tab.
    5. Close the contextual menu pop-up.

Setup a macro

You first must make sure about the categories you need to establish and how you will make them evident. For example, in a massive table with a high amount of data, you may need to highlight with a specific color the cells with the names of vegetarian people or bold the kind of jobs of some members in a big company.

Once you have cleared those things you actually need, you can start inserting a macro: Click in a cell within your table that doesn't belong to the category you want to set, then go to the "Developer" tab and select the "Record Macro" option. 

Afterward, you will get a pop-up window that will let you name your macro, but remember that it doesn't allow you to include any spaces. The second option of the window you can set is the "Store macro," which is the place you want to save the settings.

Tip: Try to keep it in the same workbook you're working on.

In addition to the above, you can establish a shortcut key for that specific macro in that pop-up window. So, for example, if you set a macro named "VegetarianPeople," you set (for mac) Option+Cmd+V (or any letter you want)

Finally, You can write under the description gap the information you must remember for that specific macro. And, once you think you're done entering all that you consider necessary, hit "Ok." You'll see "stop recording" in the "Developer" tab. This means Excel, from now on, is recording your next steps.

Recording a Macro

Here is why you need to start to form a cell that does not have the category you need. Now you must go to the cell you want to change and apply those settings you need (like changing the cell color, bold the names, etc.)

Once you think you're done, hit the Enter key on your keyboard and go back to the "Developer" Tab to click on "Stop recording." Now all the changes you have done are set but unfortunately, just for that cell to become a macro, an excellent formula for your whole table, you need to edit it.

Edit a Macro

To use a macro in other places, you must adjust its range of it by the following steps:

  1. Go to the "Developer" Tab and click on the "Macros" option
  2. In the context menu, select "Step" (in case of having a PC, this option will be named "step into").

This will bring up a new window that will display all the programming languages you currently use when you start recording your macro. So, you need to scroll down until you see the word "Range" and the name of the cell within quotes and a parenthesis. Then, you only have to delete those lines from the code and close that window.

Finally, your macro can be used in every cell you ought. You must simply click on the cell you need and use the keyboard shortcut you did before.

Note: to save this workbook, you must do it as a .xlsm file so the macro can be adequately saved; otherwise, it might not work.

There you have it! Macros are a handy tool for saving formatting in Excel. Try it yourself

Lorena M. Rodas leverages her experience across film, writing, and production to make complex tech concepts accessible through storytelling. With a background spanning sci-fi, AI, and emerging tech, Lorena translates her depth of knowledge into engaging, educational content. She is an expert at decoding high-level topics to reach broad audiences.
Access 2,701+ video lessons. Instantly.
Start Learning Now →
crossmenu linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram