Far too often, it goes like this: You download data from your apps—your PayPal transaction data, say, or an exported email list from your old newsletter app—and open it in a spreadsheet. Then you move data around, resize columns, format text, delete rows, add headers, and finally add formulas and graphs to actually use the data and make it understandable.
Make your Google Sheets work for you
Automate Google Sheets
Spreadsheets are powerful tools—and they're also tedious, frustrating nightmares of confusing formulas, hidden features, and messy data. Excel has long included a macro tool to automate those tedious tasks away—and now, with Google Sheets' new macro tool, you can build your own automations in Google's online spreadsheet app, too.
By recording your steps into a macro, you can teach Google Sheets how to do them at the click of a button, and never have to manually do it again. And as a bonus, if you want to add a custom keyboard shortcut for any one menu item in Google Sheets, you can now do that in Google Sheets with a quick macro.
Here's how to use macros in Google Sheets.
How to Make a Macro in Google Sheets
Macros are spreadsheet functions that can automatically do anything in your spreadsheet, faster. They can remove or add formatting, insert extra rows and columns, fill in tricky functions, clean up data, and more. As long as there's a button or menu option for something, a macro can run that along with anything else you want. You just have to teach your spreadsheet what you want it to do, then press the button or keyboard shortcut to run the macro.
Macros are handy to speed up your own work, and even more useful when you need to hand off work to someone else. Instead of needing to tell them exactly how to set up the spreadsheet and which functions to add, tell them to run your macro and the spreadsheet can do it for you.
All you need to do is first build the macro. In Google Sheets, open your spreadsheet, then click Tools → Macros → Record Macro.
That will open a small Recording New Macro box in the bottom of your spreadsheet. Whatever you click or type in Google Sheets now will be recorded—and done again in that same order when you run that macro.
There’s one option to note. On the bottom of the dialog, you can choose to Use absolute references or Use relative references. The former will record the exact cell, column, and row you click—so if you click cell
Tip: Be sure to plan what you want to happen in the macro from the cell, column, or row you start the macro from, as the macro recorder watches everything you do—even selecting a different cell.
Once you’ve done everything you want this macro to repeat for you in the future, click Save. You can then add a name to the macro along with a number, for easy access to up to 10 macros. Then, every time you want to run that macro, you’ll press
Let’s pull it all together. Say you regularly get a contact list with names combined in one cell, along with the contact’s email—and you’d like to get their first and last name in their own columns along with their company name. A few Insert Rows and Split Text functions later, and you can teach Google Sheets how to clean up that spreadsheet. Then the next time you get a similar contact sheet, run the macro and it will do the cleanup work for you.
With Google Sheets Macros you can:
Anything you routinely do in Google Sheets, you can have a Macro do in a fraction of the time, on its own.
Add Custom Keyboard Shortcuts for Any Google Sheets Feature
Often what makes spreadsheets difficult to use is that the one feature we need is hidden in some menu, and we never seem to remember where it is when we need it. That’s how the Split Text tool is for me—I need it just rarely enough that I always forget where it lives.
You can’t add custom keyboard shortcuts in Google Sheets—or at least, there wasn't an obvious way to do this before Macros give you a workaround.
Just start the macro recorder, and record you clicking that one Google Sheets feature you need—and perhaps selecting the option you need in it as well (as in the Split Text animation above). Then save and add a custom keyboard shortcut for that macro.
The next time you need that feature, press its keyboard shortcut—which is always
Add Macros to New Spreadsheets
There’s only one problem: Google Sheets Macros only work on the spreadsheet where you make them. You’ll need to recreate them on other spreadsheets if you want to use them elsewhere.
Or, you could copy them over. To do that, first open the spreadsheet with your macro, click Tools → Macros → Manage Macros, then click the menu beside the macro you want to copy, and select Edit Script.
Each macro is powered by a Google Apps Script that you can copy over to use in any other spreadsheet. All you need to do is copy this script and use it in the other spreadsheet. So, select all the text in the editor, and copy it.
Now, open your new spreadsheet, record a macro, and save it—only here, you can literally start the macro recorder and click save without doing anything else as all we need is a blank, placeholder macro. Repeat the steps above to edit this new macro, and this time select all the text in the Google Apps Script editor and paste in your copied macro script from the first spreadsheet instead.
Go back to your new spreadsheet and run the macro, and you’ll have the same features from your first spreadsheet without all the work of remaking it.
Tip: Have a Google Apps Script you’d like to use more easily? You can turn it into a macro by pasting the script into a macro’s script editor, too, as above.
And that’s how to use Google Sheets Macros to teach Google Sheets how to do your hard work for you. It’s a handy way to do routine tasks in your spreadsheet without needing to click a dozen times.
Why can't I find macros in Google Sheets?
Tip: An alternative option to enable a macro is to press Ctrl + Alt + Shift + 1. In case you haven't found any list of macros, this means that there are no recorded Macros on that Google Sheet. Let's see how you can create your own macros in a few simple steps.
How do I enable macros on Google Sheets?
Create a macro.
On your computer, open a spreadsheet at sheets.google.com..
At the top, click Extensions Macros. Record macro..
At the bottom, choose which type of cell reference you want your macro to use: ... .
Complete the task you want to record. ... .
Name the macro, create a custom shortcut, and click Save..
Where is macro in Google Sheets?
In the Google Sheets UI, select Extensions > Macros > Manage macros. Find the macro you want to edit and select more_vert > Edit macro. This opens the Apps Script editor to the project file containing the macro function. Edit the macro function to change the macro behavior.
Why does my Google Sheets not have script editor?
If the Tools --> Script Editor and the Add-ons menu options are missing when you open the file, it probably isn't actually a Google Sheets file. Make sure to navigate to File --> Save As Google Sheets. Then the Script Editor option in the Tools menu and the Add-ons menu will be available.