Macros in Google Sheets

Macros in Google Sheets
Reading Time: 4 minutes

Why Need Macros in Google Sheets

Let’s suppose you have a very repetitive task in Google Sheets at your hand and you want to somehow make it easier and less cumbersome for you. Macros can help you in this condition. We can easily record macros in Google Sheets.

Google Sheets Macros record your movement and changes and keep a track of all the changes you’ve done. Then you can just playback those changes in different scenarios. We will be scratching the surface with macros in this article and see its potential.

Basic Steps to Record and Use Macros in Google Sheets

Let’s say, we want to make some changes to a cell and we wanted to it repeatedly. We’ll first need to record the actions for a sample cell.

  • Select the cell you want to record the actions for
  • Extensions->macros->Record Macro
Recording Macros in Google Sheets
Figure 1: Recording Macros in Google Sheets
  • Here you’ll be provided with two options, namely Absolute Reference and Relative Reference. For now, choose Relative reference, we will later compare both of them.
Macro Recording Screen
Figure 2: Macro recording Screen
  • Now make whatever changes you want to this cell. For the current demonstration, I will make the background color Green, text color white, and make the text bold. Click save when you’re done.
Recording the changes in Macro
Figure 3: Recording the changes
  • Now you’ll be asked to enter a name for your macro, and optionally you can also create a shortcut for the same. Click Save when you’re done.
Saving the Changes
Figure 4: Saving the changes
  • Congratulations! You created your first macro. 
  • To use your macro, simply select another cell, then Extensions-> Macros-> (Name of the macro)
Applying the macro
Figure 5: Applying the Macro
  • If you’re using macro for the first time you might need to provide permissions for the same. Give all the necessary permissions and try again.
Applying Macro for the first time, permission required
Figure 6: Applying Macro for the first time, permission required
  • Congratulations, you have successfully learnt how to use macros.Alternatively you can also use the shortcut you created to make the changes. Additionally, you can select multiple cells at once to apply changes to all of them in one go. These macros are by default usable in different worksheets so you need not define macros again.

Absolute vs Relative references 

You must have noticed that we selected relative references earlier. So What exactly are absolute and relative references?

Let’s take another example, I’ll record another macro that paints by background orange. This example however would be using absolute referencing. Note that the cell we have used to create the macro is B2.

Recording in Absolute Reference
Figure 7: Recording in Absolute Reference

We have already seen how relative reference works, let’s see how absolute works. Here we selected B8 and tried to run the absolute referenced Macro(orange bg) but instead of making changes in B8, it changed the B2 cell(the one we recorded the macro on earlier). The absolute reference takes the absolute coordinates of the cells you are making your changes in.

Result of Absolute Reference
Figure 8: Result of Absolute Reference

Using macros in Google Sheets for a table

We earlier used a macro to change the font of a single cell, but we can also record a macro on multiple cells, such as a table. Let’s take an example.

  • Suppose we have a table for sales during each month for 2020. And we want to color the table for easy accessibility.
Sample Table to apply Macro
Figure 9: Sample Table to apply Macro
  • Now let’s start recording a macro. To alternate colors go to Format->Alternating colors
Applying Alternating Colors to a Table
Figure 10: Applying Alternating Colors to a Table
  • Save the macro recording. Now let’s head over to the Sales table for 2021.
Applying the Macro to Another Table
Figure 11: Applying the Macro to Another Table
  • Voila! Now our table for sales-2021 is also colored.
Result after Applying the Macro
Figure 12: Result after Applying the Macro

Conclusion

Herein we saw how to use macros in Google Sheets to record and play all of your repetitive work. We also compared absolute referencing with relative reference.

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

Google Sheets: BINOMDIST Function: Understand how to use the BINOMDIST function in Google Sheets 

Numbers of Days, Months and Years between two dates: Number of Days, Months and Years between two dates