What are Scripts in Excel and how do I record one?

Dec 7, 2023 | Excel Hints and Tips

If you have used macros in Microsoft Excel before then you will find that Scripts are similar in a lot of ways.  Both are used to automate repetitive tasks but where macros are desktop based, scripts are cloud based.  Scripts are only available to users with a Microsoft 365 licence and can be used with either the desktop or online version of the software.

Where do you access scripts?

Scripts are available from the Automate tab from the Microsoft 365 version of Excel.

Automate Tab image

How do you create a script?

There are 3 options:

  1. Scripts can be recorded.  This requires no previous programming knowledge.
  2. A new script can be written using the programming language TypeScript.  This is similar to JavaScript so Java programmers should be able to transfer much of their knowledge across.
  3. There are a small selection of sample scripts already created to be used out of the box.

This blog will look at recording a Script only.

Recording a Script

A script can record most of the actions that you would perform in Microsoft Excel.

  1. Go to the Automate Tab
  2. Click on the Record Actions icon
Recording image

3. Perform the steps you need the script to complete

4. When finished click the Stop icon in the Record Actions pane on the right

5. The heading for the record actions pane has now changed to Code Editor

Code editor image

6. To see the code that has been recorded click on Edit.  If you are confident with TypeScript you can make changes without having to rerecord the script from scratch if you have made a mistake

7. The back button to the left of Script Details take you back

8. To repeat the code that was recorded click on the Run icon

9. The ellipses next to the Run and Edit icons allows you to Delete or Rename the script

10. A Description can also be added to explain what the script does in more detail in the Code Editor pane

Where are scripts saved and how do I share them?

Office Scripts by default are stored on your OneDrive, in Documents/Office Scripts folder as an .osts file.  Only the person who recorded the script can run it at this point.

If you want to be able to share the file with others the Script must be added to the workbook.  It can be added with or without a button.

  1. Add in workbook will add the script to this workbook with a button
Add in workbook image

2. The drop down beside Add in workbook gives the option of Add without button

3. In both these cases everyone with access to the workbook can now run the script

4. If a button is added it will be called Script 1 by default.  To edit the text on the button to something more meaningful right click on the button first then left click to edit.

5. To run the Script using the button click on it.   A yellow bar will appear which initially says the script is preparing to run then that the run has succeeded.

NOTE: To remove a script from a workbook click on the drop down beside Add in workbook and choose Remove from workbook.  The script will now only be able to be run by the person who created it.

Further Reading

If you’ve enjoyed reading about Scripts in Excel and how to record one, there are some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.