Table of contents
Manually managing data in a spreadsheet is often a tedious and time-consuming task. Is there any way to simplify this? Google Sheets have macro tools that automate the entire process. By registering each step, you can teach Google Sheets how to do it with one click. You can also add a custom keyboard shortcut for any item in Sheets, which will increase your work efficiency.
Macros in Google Sheets – how do you create them?
Macros are spreadsheet functions that can automatically perform specific tasks. Removing or adding formatting, inserting additional rows and columns, using more difficult functions or cleaning the data – there are many possibilities. All you have to do is to teach the spreadsheet how to perform each task, and then press the button or the appropriate keyboard shortcut.
Macros help optimize both individual and team work. Instead of instructing your colleague exactly what to do, just tell him or her to run the macro and the spreadsheet will do the job automatically for them.
Adding a macro is easy. Open Google Spreadsheets and then choose:
Tools → Macros → Record macro
A small window “Recording new macro …” will open. All recorded activities in Google Sheet will be saved and then played back when the macros are run. The “Use absolute references” option saves actions to specific, selected cells. “Use relative references” will select the cells to the left of the one selected when the macro was recorded.
Now just click “Save”. One user can create up to 10 macros. Each of them has a number assigned.
To run it, press the key combination:
- PC: Ctrl + Alt + Shift + number,
- Mac: Command + Option + Shift + number.
Examples of practical application of Google Sheets macros
Using spreadsheet macros, you can:
- use any formatting tools to make a Google Sheets table follow your company’s branding;
- use any options from the Google Sheets toolbar, main menu, or right-click menu;
- use any Google Sheets function to automate computations;
- select any cell, row or column,
- use standard keyboard shortcuts,
- enter any text into the spreadsheet,
- every Google Sheets routine, can be automated with macros.