Excel Office Scripts: The Future of Automation, Replacing VBA
Excel Office Scripts have ushered in a new era of automation, gradually replacing Visual Basic for
Applications (VBA/macros) as the preferred method for automating Excel tasks in both the desktop and online.
VBA’s limitation is it’s tied to the desktop. VBA cannot and never will be able to execute in Excel online.
With Office Scripts, users can record and replay actions, eliminating the need for complex VBA coding and making automation more accessible to a
wider range of users.
Another way to write Office Scripts is to get ChatGPT to do it for you!
Join me as we explore the exciting world of Excel Office Scripts and witness the future of automation unfold, saying goodbye to cumbersome code and embracing a user-friendly and efficient way to automate BORING Excel tasks.
The Office Scripts tools are available on the new Automate tab of the ribbon:
On the Automate tab you can record scripts using the ‘Record Actions’ button or write a new script by hand, using the ‘New Script’ button. This opens the Code Editor pane:
In the gallery you’ll find a selection of sample scripts to try, as well as any scripts you’ve recently created.
The ‘All Scripts’ button opens the Code Editor pane where you can navigate to recent scripts, scripts saved in this workbook and samples:
If you don’t see this tab, check your version of Excel meets the requirements above.
Office Script Scenario
A table of contents with hyperlinks to each sheet can make large files easy to navigate for your users.
However, they can be time consuming to create and if you change the name of any sheets, you need to manually edit the hyperlinks.
With Office Scripts we can automate the creation of tables of contents and update them for any changes in the file with one click.
Using ChatGPT to Write Office Scripts
I could have recorded the script, but I would have had to edit it to handle deleting any existing tables of contents in the file before I could update it with a new one.
And with zero JavaScript skills, that’s way beyond my capabilities, but not for
ChatGPT.
The key with using ChatGPT to write Office Scripts is to give it a very clear description of what you want.
The ChatGPT prompt I used was:
Write an Excel Office Script to generate a new Table of Contents sheet called "TOC" that contains a hyperlink to each sheet in the current file. If there is already a sheet called "TOC", delete it before creating the new sheet. Start the table of contents hyperlinks in cell B3. Format the hyperlinks in blue
font with an underline. Place a heading in cell B1 of the "TOC" sheet with the text "Table of Contents" in black bold font, size 18. Hide the gridlines on the “TOC” sheet. Annotate the script to explain what each step in the script does.
It got a little stuck on the font formatting, but nothing that it couldn’t correct once I fed the errors back into ChatGPT.
The end result is this code:
Inserting the Office Script into Excel
To use the Excel Office Script returned by ChatGPT, follow these steps:
Open the Excel file you want to add the Table of Contents to.
On the "Automate" tab
Click on "New Script" to open the Office Scripts
editor.
Replace the default script with the provided script.
Click the "Run" button or press "Ctrl + Enter" to execute the script.
Once you’re happy the script runs successfully, click the ‘Save Script’ button to the right of the ‘Run’ button.
Note: to rename
the script, click on the name in the Code Editor to bring up the Script name edit box:
Re-using Office Scripts
By default, scripts are saved to your OneDrive account and are available in the script gallery or your script library via the ‘All Scripts’ button on the ribbon.
To attach the script to
the current file and share it with anyone who has edit access to the file, you can link to it via a button in the current file.
Go to the sheet you want the button placed on
Automate tab > All Scripts
Click the ellipsis for the script you want to link to in the file > + Add in workbook:
This adds a green button to the current sheet with the name of the script. When clicked, the script will execute:
Go ahead and give Office Scripts a go. Try recording your own and for more complex scripts, reach out to ChatGPT
for help.
Please Share
If you liked this please share this tutorial with your friends and colleagues.
Dashboards provide insight, analysis and alerts by displaying key trends, comparisons and data in
interactive reports. The reader can filter data and change views to find answers to their questions and problems.Read More
Microsoft’s latest business intelligence (BI) & data visualization tool enables you to quickly develop stunning interactive dashboards. Embed Excel charts, tables, Pivot Tables and dashboards in your Power BI dashboards, then
securely share your dashboards to any device.Read More
Get quickly up to speed with Excel's functions. This course covers the fundamentals for each function, and less obvious uses for them. It's these more advanced techniques that will really set you apart from the crowd.Read More
Make huge efficiency gains by using dynamic named ranges to automatically update formulas, Pivot Tables and charts. Automate formatting like banded rows or columns; sorting and filtering; and totals and statistics. Everything you need to work with Tables in less than 1 hour.Read More
This short course will quickly have you building your own Pivot Tables with confidence. Start with the ideal data layout for creating Pivot Tables. Then move on to formatting, aggregation, pivot charts, filters, slicers and much more.Read More
Get data from almost any source (a database, the web, Excel, Sharepoint, Salesforce, OData etc), then clean, transform, merge and append data to your query. Load the data into Excel or Power Pivot for analysis, report preparation or export.Read More
Import millions of rows of data, create relationships between different data sources, and build interactive reports. Quickly transform huge volumes of data into meaningful information to get the answers you need.Read More
Designed to take you from zero to hero so you can confidently list 'Advanced Excel Skills' on your resume/CV. Starting with the essentials, then covering must-know skills like best workbook design, advanced formulas, pivot tables, conditional formatting, macros and much more.Read More
Learn the core concepts of the three primary financial statements : Income Statement, Balance Sheet and Cash Flow Statement. By understanding core concepts you can avoid common mistakes when working with these three statements. This course breaks down the modelling process into small segments that are easily digestible.Read More
Ideal for those involved in managing the daily operations of a business. Covering tasks such as calculating breakeven, optimizing shipping, allocating funds to maximize NPV/profit, or managing staff scheduling. Includes an introduction to Excel Solver and how to create Gantt Charts.Read More
Learning to use Pivot Tables will allow you to quickly summarise and analyse large amounts of data - with just a few clicks of your mouse. Quickly transform data into meaningful information, and create reports that are easy to update and change.Read More
Choose just one of these, or choose any combination of the three. Learn the core software tools any professional needs in a modern office environment.Read More
Use Excel's existing tools to make business decisions under uncertainty by performing Monte Carlo simulations, using Decision Trees and Forecasting with the Data Analysis Toolpak. Ideal for executives, analysts and all levels of business school students.Read More
Designed for professionals working in customer service or call center environments. Efficiently and accurately collect and analyze data. Create forecasts and generate clear, meaningful charts and reports. Boost your skills in handling customer service and call center information.Read More
Leverage Excel tools for financial analysis including Time value of Money functions, What-If analysis, Goal Seek, Amortization, Depreciation, and Solver Ideal for accountants, executives, analysts and all levels of business school students.Read More
Statistics for the non-statistician. Quickly and efficiently calculate and report the statistics of data and make decisions based on statistical output. Use the Excel Analysis Toolpak to generate descriptive statistics, histograms, correlations, regression, rank & percentile, random numbers, and sampling.Read More
Check Out Our Courses
