In this article:

Google Sheets Macros: Everything you Need to Know in 2024

December 5, 2024

What are Macros in Google Sheets?

In the course of processing and analyzing data in Google Sheets, you will find yourself doing a set of repeated actions, clicking on the different options in the toolbar and the main menu. You can automate them by recording a macro. Macros are a programmed set of actions you do in Google Sheets. Recording macros will help you save time and streamline your workflow.

These macros are stored using the Google Apps Script, so if you have some knowledge of how Google Apps Script works you can edit the macro you created later on. 

How to Create Macros in Google Sheets

1. Click Extensions, Select Macros, then Choose Record macro

The macros in Google Sheets can be accessed under the Extensions option in the main menu. Click Extensions, select Macros, then choose Record macro.

Macros google sheets‍

The following small box will appear on the lower-center portion of your sheet. Once it appears, any action you make that changes the sheet will be recorded, so make sure to rehearse what you want the macro to do.

Macros in google sheets

For our example, we want to insert two columns with their respective formulas and then add a header color.

2. Choose the Type of Reference to Make

You can program the macro such that Google Sheets will apply the changes using the absolute references or the relative references.

Use absolute reference if you want the actions you program to be applied on the same exact cells every time. This is best done when you want to apply the changes once to new sheets; for example, adding header color, adding a column with a formula applied to all rows, etc. This is not recommended if you intend to apply the macros repeatedly whenever new rows are added. 

Use relative reference if you want to specify later on where in the sheet to apply the changes. Google Sheets does this by recording the location of the cells you modified adjacent to the first cell you select when recording the macro. This gives you flexibility to apply the macro anywhere in the sheet. However, make sure that, when you choose this option, you select the right cell when you start recording the macro. 

google sheets macros

For our example,  we want to insert two columns with their respective formulas and then add a header color, so we will select Use absolute references

3. Do the Actions you Want to Program to the Macro

Once the macro box appears, it will start recording any action you make that modifies the sheet. 

For our example, here are the following actions we want to program:

1. Add new column headers total cost and profit to cells G1 and H1, respectively. The macro box will change, showing the action number and the nature of the action. They will show up as Set value actions. 

Create macros in google sheets

2. Add formulas for each column, and then autofill. The action of adding the formula will be recorded as Set formula in the macro.

how to create macros in google sheets

Action recorded as macro in google sheets‍

Whenever you add a formula to a cell for the first time, Google Sheets automatically scans the sheet if the cell is part of a table with multiple rows of data. If that’s the case, it suggests an autofill action. Click the check button to Autofill the column. 

Autofill suggestion for formula while making macros in google sheets‍

The autofill is then recorded as Autofill range macro action by Google Sheets. 

Autofill added in macro in google sheets

Do the same for each of the columns you added. 

3. Fill color the header row. To add the header color, select the cells in the header row you want to color, then select the Fill color option in the main toolbar. A color palette will load. Select the color you want to use. 

Select header color for macros in google sheets

The header row becomes colored:

Colored header in google sheets

This action is recorded to the macro as Set format style

Header color recorded as google sheets macro

 

4. Click Save

Once you have done all the steps you want to program to the macro, click Save in the macro box. 

Save macro in google sheets

A box labeled Save new macro will appear. Set the name of the macro by typing in the textbox labeled Name

google sheets create macros

You can also set a keyboard shortcut for the macro. The shortcut takes the format

Ctrl+Alt+Shift+Number

Where you can specify the Number to append to the keyboard shortcut. For our example, we will set it as

Ctrl+Alt+Shift+1

This action is optional.

Set keyboard shortcut for macro google sheets

Once you are done, click Save. A notif saying Saving new macro will appear

Saving new macro notif in google sheets ‍

A message saying Macro saved will appear. 

Macro saved in google sheets

You can now use the macros you created!

How to Run Macros in Google Sheets

1. Click Extensions, Select Macros, then Choose the Macro to Run

The list of macros you created will appear. Select the one you want to run. 

List of macros in google sheets via extensions option in main menu

2. Authorize the Macro (When Running the Macro for the First Time)

If it is the first time you run the macro, you will be asked for authentication to run the associated script. Click OK.

Authorize macro in google sheets

A new window will load. You will be asked to choose an account to run the macro. Select the listed account. 

Choose account to run google sheets macro‍

You will then be asked to grant the required permissions to run the macro. Click Allow.

Permissions needed by google sheets macro‍

You have now authorized the macro to run in your spreadsheet. Run the macro again from the Extensions menu. The macro will finally run, modifying the active sheet!

Macro run in google sheets

How to Edit Macros in Google Sheets

You can edit the actions programmed in the macro by editing its associated Google Apps Script. Here are the steps:

1. Click Extensions, Select Macros, then Choose Manage macros

To edit macros, click Extensions, select Macros, then choose Manage macros.

Manage macros in google sheets‍

A small box labeled Manage macros will load.

List of macros in google sheets

2. Click the Three Dots then Select Edit script

For each macro saved in the spreadsheet, there are three dots on the right of the list. Click it. Select Edit script.

Edit script associated with the macro

3. Edit the Script then Save

Edit the script with the changes you need to  make. Once you are done, click the Save button on the main toolbar. 

Google apps script showing macro

How to Import a Macro to Google Sheets

To import a macro, you need the Google Apps Script, then convert it to a macro. Here are the steps:

1. Copy the Macro Script to Google Apps Script

Find the script of the macro you want to copy. For example, you want to copy the headerformat macro from the following link:

https://github.com/rvbautista/google-sheets-macros/blob/main/headerformat.gs

Macro script to import to google sheets

Copy the code.

2. Paste the Code to Google Apps Script

In the Google Sheets where you want to use the macro, click Extensions then select Apps Script

Extensions, Apps script

A new tab will load for Google Apps Script. Clear the code area then paste the code. You can also name the apps script project. 

Macro script added to google apps script

Click Save.

Save macro script in google sheets

3. Click Extensions, Select Macros, then Choose Import macro

To convert the script to a macro, click Extensions, select Macros, then choose Import macro

Extensions, macros, import macro

4. Find the Function then Click Add Function

A box labeled Import will appear. Find the function you added, then click Add Function below it. In our example, the function is named headerformat. Click the Add Function below it.

List of google apps script functions to import as macros

The Add Function becomes a check icon.

Function imported as macro in google sheets

The function is now listed as a macro!

Imported macro now listed in google sheets

Common Macros in Google Sheets You Can Use

We have prepared a set of common macros you can import to your Google Sheets! Follow the steps in importing macros in Google Sheets, and you are good to go!

  1. Alternating row colors
  2. Convert formulas to values
  3. Format header row
  4. Show all hidden columns in the active sheet
  5. Sort sheet by ascending order, referencing Column A
  6. Sort sheet by descending order, referencing Column B

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->