HOW TO CREATE MACROS IN GOOGLE SHEETS IN A MINUTE?

Table of Contents

INTRODUCTION

How many times you repeated the same steps while creating some report in GOOGLE SHEETS?

It is for sure that such routines come in the way of each and everyone of us while working in GOOGLE SHEETS.  For such situations, GOOGLE SHEETS provides us with an option called MACRO.

MACRO is  a system of recording the steps which we have performed in GOOGLE SHEETS.

Suppose there are a number of steps in any calculation . For example, suppose we are given a task in which we need to do the following operations.

Firstly, adding the first and third cell, then subtracting the third cell from the fourth one, then adding the first with the four and so on.

The number of operations is big say 100.

Now if we have to repeat  such operations twice or more than that and we start doing all those manually, we can imagine how much time it would take.

Or we can just create a MACRO, that is record all the operations and just click a button next time , and all the work is done with in a second.

 So, MACRO is again one of the very important FUNCTION of GOOGLE SHEETS.

In this article we’ll focus on the following.

  • What is Macro
  • How to Create Macro
  • How to Run Macro
  • A fully described Example of creating and running Macro

WHERE TO FIND THE BUTTON LOCATION OF MACRO IN GOOGLE SHEETS ?

You can find the MACRO button or option under the EXTENSIONS MENU>MACROS>RECORD MACRO

MACRO BUTTON LOCATION IN GOOGLE SHEETS

STEPS TO CREATE A MACRO IN GOOGLE SHEETS

MACROS CREATION is quite simple. Here are the steps for creating MACROS in GOOGLE SHEETS.

  • Go to EXTENSIONS MENU > MACROS> RECORD MACRO.
  • As we click RECORD MACRO, a small window will open.
MACRO RECORDING WINDOW

  • The Macro will start recording your steps. 
BEFORE STARTING ANY PROCEDURE , CHOOSE THE ADDRESSING SCHEME FROM ABSOLUTE REFERENCES OR RELATIVE REFERENCES.
  • Start PERFORMING THE STEPS WHICH YOU WANT TO RECORD.
  • After you are done, click SAVE.
  • A small dialog box will open.
NAME THE MACRO

  • Click SAVE after giving the proper name to the macro. You can also give a shortcut key CTRL+ALT+SHIFT+ ANY KEY OF YOUR CHOICE. [ It is optional. ]
  • The MACRO IS SAVED.
  • Let us try to create one macro for learning purpose.



EXAMPLE 1: CREATE A MACRO IN GOOGLE SHEETS

Similarly to the steps discussed above, let us create one macro.

FOLLOWING IS THE PROCESS TO BE AUTOMATED:

We have taken two values in the adjacent cells.

The process will show the result in the next column to the VALUE 2 and the result will be divided by 10 and the result will be placed in the two cells down to the RESULT 1.

Let us create a macro for this process.

FOLLOW THE STEPS TO CREATE A MACRO:

  • Put two values in any two adjacent cells. For our example, we put 645 and 321.
  • Go to EXTENSIONS MENU> MACROS> RECORD MACRO.
  • The small window will open and already started recording the steps.
  • Choose USE ABSOLUTE REFERENCES radio button, so that we can reuse the macro again for any pair of values.
  • As we are ready, select the next cell and enter the formula = CELL CONTAINING 645+CELL CONTAINING 321.
  • Go to second cell below the selected cell.
  • Enter the formula as =CELL CONTAINING SUM RESULT/10.
  • CLICK SAVE.
  • A small window will open asking you for the name of the MACRO. Give the name of your choice, we used EXAMPLE 1.
  • You can also give a shortcut key.
  • The following picture shows the complete process.

ANIMATED EXAMPLE OF CREATING A MACRO

RECORDING A MACRO IN EXCEL



STEPS TO RUN A MACRO IN EXCEL

Use the following steps to run a MACRO.

STEPS:

  • After a macro has been created it is stored for future use.
  • Before running a macro, select the cell from where you have to run the stored automated process.
  • For our example, we’ll place the immediately after the second cell containing the value.
  • To run a macro, go to EXTENSIONS MENU > MACROS > Your Macro from the list.
  • The Macro will run.
FOR THE FIRST TIME RUNNING, THE MACRO WILL OPEN A WINDOW ASKING FOR THE PERMISSION. ALLOW THE PERMISSION TO RUN A MACRO.
  • Following Dialog BOX will open.
MACROS DIALOG BOX
  • In the picture above, you can see that we took any two cells [ As our macro is using RELATIVE REFERENCES ], selected the third cell adjacent to the second value and run our macro.
  • The macro calculated the sum in the cell immediately and divided the sum by 10 in the second cell below the SUM RESULT cell, absolutely same as we did while creating the MACRO.

  • In this article, we learnt about creating and using macros in GOOGLE SHEETS.
  • Let us go through a few confusions and additional information through FQAs.



FQAs

CAN YOU RUN MACROS ON GOOGLE SHEETS?

Yes, Google Sheets allow us to create and run macros, but not EXCEL MACROS. We need to create separate macros for google sheets.



HOW TO DELETE MACROS IN GOOGLE SHEETS ?

You can delete the macros in google sheets very easily.

FOLLOW THE STEPS TO DELETE OR REMOVE THE MACROS IN GOOGLE SHEETS:

  • Go to EXTENSIONS MENU>MENU>MANAGE MACROS.
  • A small window will open with a complete list of the available macros.
  • Go to the relevant macro , you want to delte and click on the THREE DOTS [ options] on the right side of the macro name.
  • Choose REMOVE.
  • Click UPDATE. [ Never forget to click UPDATE button, otherwise no macro will be removed. ]
  • The macro is removed.



WHAT LANGUAGE DO GOOGLE SHEETS MACROS USE?

Google Sheets macros are written in APP SCRIPT which is based on the JavaScript.



MY MACRO IN GOOGLE SHEETS IS NOT RUNNING?

You can try a few solutions:

In the first run, google will ask for the permissions .

Kindly allow the permission.

If it is still not running, check if any steps have been stored or not.

You can remove the macro and create again.

Kindly check if the Google Account is logged in properly. You need the owner’s permission to run any macro.