- Utility of macros in Google Sheets
- Steps to record and use macros
- Absolute and relative references
- Using macros for a table
Why Use 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 their 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
- 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.
- 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.
- 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.
- Congratulations! You created your first macro.
- To use your macro, simply select another cell, then Extensions-> Macros-> (Name of 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.
- 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.
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.
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.
- Now let’s start recording a macro. To alternate colors go to Format->Alternating colors
- Save the macro recording. Now let’s head over to the Sales table for 2021.
- Voila! Now our table for sales-2021 is also colored.
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