Traditionally, the way to automate tasks in Excel, was to use VBA.
See this post for how to turn on the developer tab and access this amazing tool.
The new kid on the block in terms of automation in Excel is Office Scripts. Both allow you to record your actions, so no real programming knowledge is needed, BUT you need to be very careful to check that what has been recorded, and will therefore be done when you run the code, is what you expect.
Advantages of VBA
- Is available to everyone. Some version/subscriptions have access to Office Scripts and some do not (yet).
- Can access ‘things’ from outside of Excel, folders, other files, other programs etc. Office Scripts only work within Excel, but are easy to share for others to use.
Disadvantages of VBA
- Doesn’t work on Excel on the web. Office Scripts are designed to work in an online environment.
- Will be replaced by Office Scripts at some stage. Office Scripts are the future and can be used in combination with Power Automate to overcome the “access outside of Excel” issue.
Where to get information.
There is a wealth of courses and information available on Office Scripts. Start here.
So, if you have a task that you do often, consider using some Automation in Excel to speed up your work. Choose the tool that best suits!