Switching to Sheets from Microsoft Excel

5. Use macros and add-ons

In this section:

5.1 Automate tasks with macros
5.2 Convert Excel macros to Google Sheets
5.3 Do more with add-ons

5.1 Automate tasks with macros

" "

Excel: 
Individual messages

Sheets: 
Macros and Google Apps Script

Use macros in a message in 2013 version

Excel 2013 and 2010

Automate repetitive tasks with macros in Sheets. Or, if you need custom functions, menus, or windows, you can create them with Google Apps Script.

Review macro best practices

  • Limit the number of actions in a macro for optimum performance.
  • Use macros for frequently repeated operations that don’t need much configuration.
  • Use unique macro shortcuts. You can have up to 10 shortcuts per sheet. Open additional macros from Toolsand thenMacros.
  • Reduce macro duplication by applying a macro created for a single cell to a range of cells by selecting the full range of cells and then activating the macro. 
  • Macro scripts are specific to individual sheets and can only be used in Sheets—they won’t work in Google Docs, Forms, or Slides.

Record a macro:

  1. In Sheets, open a spreadsheet and click Toolsand thenMacrosand thenRecord macro.
  2. Select the type of cell reference to use.
  3. Complete the task you want to record and click Save.
  4. Name the macro and click Save.
  5. Click Toolsand thenMacrosand thenyour macro to run the macro.
Find the "Record macro" option  in a sheet by clicking "Macros" under "Tools"


Create a script:

  1. Click Toolsand thenScript editor.
  2. Create your script.

For more information, see Overview of Google Apps Script, or read more about custom functions and macros.

5.2 Convert Excel macros to Google Sheets

" "

Excel:
Edit macros

Sheets:
Recreate macros in Sheets

Excel 2010 and 2013

You can convert macros in Microsoft® Excel® spreadsheets to Google Sheets by re-creating them using Google Apps Script. Apps Script powers macros in Sheets, just like Microsoft® Visual Basic for Applications®  does for Excel. 

Re-create and edit a macro in Sheets using Apps Script:

  1. Make a note of the macros in your original Excel spreadsheet that you need to re-create in Sheets.
  2. In Sheets, open a spreadsheet and click Toolsand thenMacrosand thenRecord macro.
  3. Select the type of cell reference to use and click Save.
  4. Complete the task that you want to record and click Save.
  5. Enter a name for the macro and, optionally, a shortcut number and click Save.
  6. Click Toolsand thenMacrosand thenManage macros to edit your script.
  7. Next to the macro that you want to edit, click MoreMoreand thenEdit script.
  8. In the macros.gs section, make your changes to the code. If needed, use the Sheets Apps Script documentation or search online for the JavaScript® concept that you need.
  9. Click Save macro Save and close the tab to return to your spreadsheet.
  10. Repeat steps 2–7 for any additional macros that you want to re-create from your original spreadsheet.
Activate a macro in Sheets
  1. Click Toolsand thenMacrosand thenyour saved macro. You can also run your macro by using its keyboard shortcut.
  2. If it’s the first time you run the macro, allow authorization.

Tip: For more about how the Apps Script library can mimic common Visual Basic for Applications (VBA) functions, go to Simplifying Migration from VBA to Google Apps Script

5.3 Do more with add-ons

" "

Excel:
Add-Ins

Sheets:
Add-ons

Add-ins in 2013 version

Excel 2013
 

Add-ins from 2010 version

Excel 2010

Use ready-made add-ons to do more with Sheets. Here’s a few things you
can do:

Download add-ons:

  1. Click Add-onsand thenGet add-ons.
  2. (Optional) To see a description of the add-on, point to it or click it for a full description.
  3. Click the add-on you want to install and click Free.
  4. If needed, review the access message and click Allow.

Get add-ons

Was this helpful?
How can we improve it?