How to Use Macros in Google Sheets [Complete Guide]

In this article, we will learn about how to use Macros in google sheets with examples and use cases.

What are macros? Macro is a piece of code that is being recorded first and then it can be used for what it is recorded for. Assume you regularly do some fixed set of tasks in google sheets, so here using macro you can record those tasks in a macro so the macro will capture the set of tasks and you can use it to auto-perform all the tasks when you want or call the macro. This is a short story about the macros in google sheets. We will learn macros very deeply. We will see examples will see how to use them and what kind of algorithms we can set up using macros in google sheets.

The tasks that can be automated using macros can be:

• Removing duplicates
• Removing whitespaces
• Removing borders from empty cells, and so on

Use Cases of Macros in Google Sheets

Macros are highly interactive and useful visual elements that help us to automate our sheet’s data and actions. Macros make it very easy for beginners who don’t know programming to work programmatically in google sheets. It’s a visual element so you don’t need coding for it you can easily work with it using the UI of the macros. Everyone needs to learn Macros in google sheets to become a master of google sheets. Macros can be assigned tasks and can be done automatically which can save a lot of time and effort if I say you to write hello world after one hour in the cell A1, there are high chances of errors you will miss the timing or sometimes you will the spelling or cell address.

We humans can no meet the accuracy of a computer, so for making our work easy, and reduce the time to complete some fixed tasks, we can use macros in google sheets in four different ways (discuss in this article later). Therefore, we must learn Macros in google sheets.

How to User Macros in Google Sheets

To use macros in google sheets we need to first understand the process which is followed by google sheets macros to run a script or a piece of code. We need to know that macros are first recorded, and we need to perform the actions in the recording mood to tell google sheets what to do when this macro is triggered later. Then we stop recording and practically use the macro to perform the recorded action live in google sheets. Having that said, now let’s move further and see how we can record a macro in google sheets

Macros in Google Sheets – Recording a Macro

Let’s see how a macro is recorded in google sheets. We will record a sample macro to perform a simple task in google sheets, then we will see the post-recording things in the next section.

Step 1

Go to Extensions > Macros > Record macro

Clicking on record macro will directly start recording

Step 2

Now you can control cell references here, choose one cell reference relative or absolute to consider in the macro actions

Step 3

Now perform your action on sheets

Step 4

You can see the recording menu is recording your actions by action names

Step 5

After performing actions, click on the save button in the recording menu

Step 6

It will ask for the Macro name, Assign a name to your macro

Step 7

You can set a shortcut number after Ctrl + Alt + Shift (optional)

Step 8

Click on save to save this Macro

This is how you can simply record a macro

Note that all the actions you perform should be identified by the recorder, if some actions are not identified, it means they will not work. Also, note that all the actions are under one macro until you save the macro.

Macros in Google Sheets – Running a Macro

In this section, we will learn how to run a macro after creating/recording it. We don’t have to access options until we create our first macro, so don’t try to find these options before recording a macro. Follow the below steps to access a macro.

Step 1

Go to Extensions > Macros > Click the Macro (just created) to run

Step 2

It will start running

Step 3

Once completed, a message will be shown “finished script”, and the actions recorded will be performed automatically

Do you see how exciting it is to run a Macro? Let’s see more examples of the macros.

Macros in Google Sheets – Removing Whitespaces using a Macro

In this section, we will record a macro to remove whitespaces from the selected cells. We will remove it once and then every time google sheets will automatically remove all the whitespaces whenever detected by the macro. This way we can automate our sheets to never encounter unwanted whitespace.

Step 1

Go to Extensions > Macros > record a macro

Step 2

Select the entire sheet (Ctrl + A)

Step 3

Write a dummy data with whitespace and remove it to save in the recording

Step 4

Go to Data > Data clean-up > Trim whitespace

Step 5

Now save the macro and the instructions will be saved

Step 6

Name your macro and assign a shortcut if you want to

Step 7

Now add sample data in the sheet with some extra whitespaces

Step 8

Now go to Extensions > Macros > Click on your macro to execute it

Step 9

Script running

Step 10

Script finished, whitespaces have been removed

This is how we can use macros; you see how easily we have made out the entire sheet whitespace-free automatically for the rest of the data that will ever come in this sheet.

Now you can think about more actions you can automate on your sheet.

Now there are many ways to access the macro in google sheets. We have used the default method till now.

Methods to Access a Macro in Google Sheets

In this section, we will see some methods to access/execute the macro after recording.

We normally have four methods to access a macro in google sheets

1. The default Macro menu option
2. Keyboard shortcut (we can set it when saving a recorded macro)
3. Using App Script
4. By adding into a shape

The default method we have already used, and the keyboard method are also not much important to elaborate on, Let’s see how to use it with app script and shapes.

Run a Macro using App Script

Step 1

Go to Extensions > Apps Script

Step 2

Now on the left-hand sidebar select macros.gs

Step 3

Select your macro from the function drop-down

Step 4

Click on Run

Step 5

Macro has been performed accurately.

This is a very good method to access and run the macros.

Now another method to access/execute a macro is by adding it into a visual shape. In this way, the user experience is enhanced and anyone can use it by clicking on a button like an element to execute the macro.

Run a Macro using App Script

We can make a drawing, a custom shape, and link our macro with it. The shape will float over the sheet and by clicking on the shape the macro will be executed. Isn’t it convenient? Yes, it is. Let’s see how we can do it

Step 1

Click on Insert > Drawing

Step 2

Create a shape

Step 3

Click Save and close

Step 4

Click on three dots > Assign a script

Step 5

Write the name of your Macro (without spaces)

Macro 1 will be written as Macro1

Step 6

Now clicking on the button will execute the recorded macro.

This is how useful the shapes are. You can make exciting dashboards and amazing automation features using macros and shapes in google sheets. I hope you find

Notes

Frequently Asked Questions

How to delete a macro?

You can edit or delete a macro by going to the Extensions, Macros, then manage macros, now all your macros will be opened you have a three-dot menu on the right corner click on it to edit or delete a macro, remove button will delete the macro

Can I Use Microsoft Word Macros in Google Sheets?

Yes, you can’t use macros in Microsoft Word when working with Google Sheets. Google Sheets doesn’t support using macros in microsoft word, as they are specific to the Microsoft Office suite. However, Google Sheets does provide its own scripting language, Google Apps Script, which offers similar functionality for automating tasks within Sheets.

When should I use absolute cell references while recording a macro?

The science is the same, when you want to change the cell reference according to the relative cells you can use relative references, other than this situation you should always use absolute cell references. Most of the time the relative cell references are used with macros and it is selected by default as well.

Conclusion

Wrapping up Macros in google sheets. We have learned how to work with macros in google sheets. We first see what are macros in google sheets and how they work, we have seen what are the things to make sure when working with a macro we firstly need to record a macro with clear instructions to sheets understanding the actions we are performing, then we save it and we call it to do those tasks for us automatically just by running a script in the background. then we saw a practical example to use macros in google sheets. We also discussed ways to access macro in google sheets. There are many ways but I would highly recommend connecting your macro with a custom shape and palace it on the screen. This makes a visual element and it makes it very convenient for the users to call the macro function with just one click.

I hope you find this article helpful. Keep learning with Office Demy. Thank you