Macros

Key Terms:

• Absolute reference: A reference that consistently refers to a specific data range

• Relative reference: A reference that can change depending on user input

• Macro: A recorded sequence of actions that can be applied to multiple ranges of data

What are macros, and when can we use them?

Macros are a powerful tool that enable the user to record a series of actions for later. For example, let’s say you are a hiring manager for a small company, and your job is to assign new employees a company email address. One day, you receive a spreadsheet with the employees’ full names in a single column. Rather than memorize how to perform the process of deciding each user’s email address and formatting it in a readable way for the new employees, you can record a macro to do the process once, then simply run the macro any time you get new employees. As a brief example, we might use the SPLIT function to split each employee’s name into two columns for first and last, then concatenate those in some fashion and attach the company email domain at the end, then add informative column titles so that employees can easily read the sheet. This is just one application of using a macro, but note its limitations: it should be used for repetitive tasks, since it will perform the exact same sequence every time. Regardless, if your workflow necessitates this type of task, then macros can be a good choice to save some time.

How to use macros

Macros can be found under the “Tools” tab in Google Sheets. Once there, click the option “Record macro”. From here, select either absolute or relative references, depending on your goal: absolute references target the same range every time the macro is run, while relative references are based on the range you select with your cursor. Once recording has begun, perform all the operations on the sheet as you normally would. You will notice that each action is transcribed in real time at the bottom of the screen. Once finished recording, you will be able to save the macro, name it something descriptive, and even assign a keyboard shortcut!

Ultimately, macros are as useful as your sequence of operations; you can basically record any sequence of actions that you already know how to do. Macros cannot, however, come up with new sequences or suggest operations; that part is up to the user. If you use macros with a strong plan of what you want to accomplish, and that plan can be repeated over and over with no variation, then you may find macros particularly useful in your Excel workflow.