Record Macro Google Sheets – Easy Tips and Tricks for 2024

The record macro Google Sheets functionality is quite powerful. They automate your repetitive tasks so you can get to the meat of your spreadsheet work much more efficiently.

Setting up macros in the first place can be a little daunting, but never fear. In this tutorial, I will cover all you need to know about recording and using macros in Google Sheets, as well as some Google Sheets macros examples where this can be useful.

Before we dive into how to create a macro in Google Sheets, let’s learn a little more about what a macro is.

What is ‘Macro’ in Google Sheets?

A macro is a piece is of code in the backend of Google Sheets (don’t worry, it isn’t complicated at all).

This macro (a piece of code) is a series of steps that you have defined, and as soon as you use Google Sheets to run this macro, it will automatically follow all these steps.

To give you an example, suppose you get a dataset where you have to do three things:

1. Remove all the duplicate entries
2. Remove all the extra spaces in between words
3. Give a border to the entire dataset

Now you can do all these three things in Google Sheets manually (one step at a time).

But what if you have to do this over and over again every day or multiple times every day. In that case, you can quickly record a macro and automate these steps. So the next time you have a dataset, all you have to do is run the macro, and it will do these steps.

So let’s get started and record our first macro.

How to Create a Macro in Google Sheets

Creating macros in Google Sheets can be simple, lets take a look a look at the steps involved:

1. Select cell A1 in the worksheet
2. Enter the text Hello in it
3. Color the cell yellow

Here are the steps to record this macro in Google Sheets:

1. Click the Tools tab.
2. Hover your cursor over the Macros option, it will show some additional options.
3.  Click on the Google Sheets Record Macro Button. This will enable macro recording and also show a dialog box.
4. In the Macro dialog box, select the ‘Use absolute references’ option (this option is explained later in this tutorial).

The above steps start the macro recording. From this point onwards (until you stop the macro recorder), Google Sheets is going to keep a track of what all you do in it and convert your steps into a code in the backend.

Now since Google Sheets is recording every step of our work, let’s do the three things that we want to automate:

1. Select cell A1 in the worksheet
2. Enter the text “Hello” in it
3. Give a yellow color to the cell (use Fill color option from the toolbar)

Once you have done these three things, click on the Save option in the Macro dialog box.

This will open the Save New Macro dialog box, where you need to specify the name of the macro. It’s advisable to keep it short yet descriptive enough so that you know what this macro does. In this example, I will name this ‘Color Hello cell’

Once you save the macro, it may take a few seconds to save it in Google Sheets. At this moment, Google Sheets is converting your macro into a Google Sheets Script (something that Google Sheets understands).

Once it’s done saving the macro, you will see a notification in the bottom left of the Google Sheets document. It also shows the Edit Script option, and if you click on it, if you follows all the steps of how to create a macro in Google spreadsheets, it will open the Google Apps Script editor and show you the code it has recorded.

Running the Macro in Google Sheets

Once you have recorded the macro, you can run it whenever you want and it will follow the steps you showed it while recording the macro.

There are a number of ways you can run a macro in Google Sheets:

1. By using the Macro Option in the Tools tab
2. By using a keyboard shortcut
3. From the Google Apps Script Editor
4. By Assigning it to a Shape

Let’s quickly go through each of these methods of running a macro in Google Sheets

Record Macro Google Sheets Options

Once you have recorded a macro, you can easily run it by locating it in the Tools tab.

Click on the Tools tab and hover your cursor over the Macros option.

In the additional options that appear, you would see all the macros listed at the bottom (after the first three options).

As soon as you click on any of the Macro names, it will be executed immediately.

Google Sheets is not as fast as other spreadsheet tools (such as Excel). So when you click on any of the macro names, it may take a few seconds to get it done. It also depends on the Google Sheet automation you’re trying to achieve with the macro. If there are many steps to it, it may take more than a few seconds.

Using the Keyboard Shortcut

When you are recording a macro in Google Sheets, it also asks you to specify a keyboard shortcut (optional step). This keyboard shortcut can be assigned in the dialog box where you’re giving you macro a name.

Once the keyboard has been set, you can simply use the shortcut and Google Sheets will instantly run the macro.

Again, it may take a few seconds depending on how many steps are there in the macro.

From Google Script Editor

When you record a macro in Google Sheets and save it, the steps are automatically saved in the Google Apps Script Editor.

Now if you want to execute a macro, you can also do this from the GAS editor.

To do this, you first need to open the GAS editor (by clicking on the Tools tab and then on Script Editor).

When the script editor opens, select the macro that you want to run (from the Select function drop-down) and click on the play button in the toolbar.

By Assigning the Macro to a Shape

You can also insert a shape in the worksheet and then assign your macro to that button.

To insert a button, click the Insert tab and then click on Drawing.

This will open the Drawing dialog box.

Click on the Shapes icon in the dialog box and insert the shape you want to use as a button to run the macro.

When you click on any shape, you will need to draw it in the area provided. Once you draw it and click on Save and Close, the shape will be inserted in the worksheet.

To assign a macro to this shape, select the shape and click on the three dots that appear at the top-right.

From the options that appear, click on ‘Assign script’.

The above steps will open the Assign Script dialog box, where you need to enter the name of the script that you want to run. Unfortunately, at the time of writing this tutorial, there was no way to get a list of all the names of macros right in this dialog box. You need to know the exact name and enter it here.

Now, whenever you will click on the shape, it will instantly run the macro.

Absolute Vs Relative Cell Referencing While Macro Recording

When you use a cell reference in Google Sheets, it can be absolute or relative.

• Absolute cell reference: $A$1
• Relative cell reference: A1

Absolute cell references mean that if you copy and paste these in formulas, these references will not change. These are absolute. For example, let’s say you have some values in cell A1:A10 and have the formula =SUM($A$1:$A$10) in cell B1.

This formula uses the absolute cell references. This means that if I copy and paste this formula from cell B1 to B2 (or any other cell in the worksheet), it will not change the cells it refers to.

On the contrary, let’s say I have the following formula in cell B1:  =SUM(A1:A10). Since this uses relative cell references, when I copy and paste the formula from cell B1 to B2, the formula would change to =SUM(A2:A11).

This happens as using the relative cell reference does not lock the cell references and is relative to the position where it’s used. If I copy this formula to cell C1, it would change to  =SUM(B1:B10). This is because I shifted the formula one column to the right (from B to C), the reference in the formula also shifted by one column.

Now coming to macros in Google Sheets, you can record a macro with Absolute or Relative cell references. This option appears as soon as you start recording the macro.

When you record a macro with absolute cell referencing, it will remember the cells or ranges you have selected and use the same cells when you run the macro. For example, if you record a macro to enter the text ‘Hello’ in cell A1, when you run this macro again, it will always go back to cell A1 and enter the text ‘Hello’ in it.

But with relative cell references, it remembers the position where you started and then moves relative to this position. For example, if I start recording the macro when I have cell B1 selected and I first select A1 and then enter the text ‘Hello’ in it, Google Sheet’s macro will remember that I moved one cell to the left.

So the next time you run this macro and have cell K1 selected, it will enter the text in cell J1 (which is one cell left to the selected cell).

Advantages of Using a Macro Recorder

Here are the advantages of using a Macro Recorded:

1. Macro recorder gives you an easy to use interface that allows you to show Google Sheets the exact steps that you want to automate. Once you’re done, Google Sheets creates a code for the steps you followed. Now you can run the macro without ever touching that code.
2. In case you’re creating a code yourself and feel stuck, you can use the Macro recorder and go through the code it creates. For beginners learning Google Apps Script, a macro recorder can be a great guide

What a Macro Cannot Do in Google Sheets

While macros are amazing, these have some limitations:

1. Can’t create custom functions: One of the most frequent uses for me to use Google Apps script is to create custom functions. Unfortunately, you can’t do that with a macro recorder. To create a custom function, you will have to write the code manually and specify the kind of result you want that function to return.
2. Can’t create a macro in Google Sheets to run based on events: Suppose you want a macro to run whenever a user opens a workbook or selects a specific cell or makes a change in the worksheet, You can not do this using a macro recorder. These are events and since you want a macro to trigger when the event occurs, you will have to manually specify the trigger event and the macro code.
3. Can’t run the macro without selecting the object: When you start recording a macro in Google Sheets, it simply follows all the steps you take and creates a code based on it. To do this, it’s necessary for you to show the macro the exact steps to follow. This means that the macro can only record what it sees on the screen, and can not go and do changes in the other sheets to documents that are not selected. You can, however, do this when you write the code manually.
4. Can’t create loops: Let’s say you want a macro to loop through each cell in a range and delete row if the value in the cell is ‘No’ and keep it if it’s ‘Yes’. You can not do this with a macro. Sure there are long ways to get this done (such as apply a filter and delete the cells), but you get the idea. With a macro recorder, you can not loop through cells, which you can do when you manually write the code.
5. Can’t analyze conditions and take action based on it: With the macro recorder, since you can not loop through the cells in a range, it’s also not possible to analyze conditions and take actions based on it. For example, suppose I want to loop through all the worksheets in the Google Sheets document and get a list of all the worksheet names in the active sheet. This can not be done with a macro recorder and you have to write the code manually for it.

How to Edit and Remove Google Sheet Macros

You can edit and remove macros by following these simple steps:

1. Navigate to Extensions>Macros>Manage macros
2. Edit the macro or to delete, tap the more icon and click Remove
   
   
3. Click Update

How to Schedule a Macro

Sometimes you may want to set up a macro to only run at certain times or in reaction to triggers. You can do this using Google Scripts by following these steps:

1. Navigate to Extensions>Script editor
2. Click Edit>Current projects triggers
   
   
3. A new window will open, click on Add trigger in the bottom right corner
   
4. Select the trigger settings
   You can use Select event source to decide whether you want to macro to trigger with an action in the spreadsheet, based on time, or from the calendar. Each of these options will change the available selections in the Select event type dropdown menu.

Google Sheets Macros Best Practices

Here are a few tips to get the most out of your macros:

• Check that your macro recorded correctly before moving on
• Check the Script language against what you want to achieve with the macro
• Name your macros so they’re easy to activate and deactivate when you need to
• Import existing macros into your sheet to save time

Macros In Google Sheets FAQ

Are Google Sheets Macros Globally Available

No, they aren’t but you can easily import a macro into a new sheet.

What Is Recording a Macro in Sheets?

Recording a macro is recording a set of tasks that you have to repeat often so that you don’t have to manually do them every time. The spreadsheet will do it automatically with one click.

What’s Next?

Now that you know how to record macro Google Sheets has plenty of other Google Sheets automation in the form of scripts. So, learning more about Google Scripts should be the next step to becoming a master of Google spreadsheets.

You may also like the following Google Sheets tutorials:

• Does Google Sheets have Macros?
• How to Get the Sheet Name in Google Sheets (Formula)