So you’ve learned TypeScript programming nerds can use Excel Office Scripts to write programs that automate Excel workflows. But did you know that to use Office Scripts you no longer be a programmer? That’s right! Read along to explore how to record an Office Script to introduce advanced automation in your Excel workbooks.

Office Scripts is a powerful feature in Excel that allows you to automate your repetitive tasks and save time. With it, you can record your actions in Excel and replay those actions whenever you want. Moreover, you can edit your scripts using TypeScript code for more advanced scenarios if you know coding.

In this Excel tutorial, I’ll demonstrate recording Office Scripts code in Excel and everything else you need to know in a step-by-step manner. Let’s get started!

Prerequisites To Record an Office Script

Firstly, you’ll need Microsoft 365 Business Standard or a better subscription to use this premium Microsoft Excel feature.

If you or your organization have recently upgraded to enable Office Scripts on Microsoft Excel, you still may not see the feature on your Excel desktop app. Microsoft automatically upgrades your Excel for the web app interface, so you can always access Office Scripts online from the Automate tab.

Now, for the Excel desktop app, you must enable the Automate tab. To do this, press Alt + F + T on your keyboard while on the Excel desktop app to bring up the Excel Options dialog.

On Excel Options, click on the Customize Ribbon category on the left and checkmark the Automate tab on the right-side menu below the Customize the Ribbon column.

Click OK on Excel Options to save the changes you’ve made.

Now that you’ve ensured you can access Office Scripts, here’s what more you need to efficiently record an Office Scripts script in Excel:

• Access to Microsoft OneDrive to store the Office Scripts you record.

• Clear outline or workflow of the task you want to record on Microsoft Excel.
• A dataset you can use to perform tasks on Excel.

• Sign in to the appropriate Microsoft 365 account, especially if you’re working from home and use different Microsoft 365 accounts for personal and business needs.

You’re all set to create your first Office Scripts program without coding a single line of code. By the way, Office Scripts uses the TypeScript programming language which is easy to learn in a few days.

How To Record an Office Script

Office Scripts is suitable for automating those tasks that you repeat each day for routine data organization, analysis, visualization, and report creation.

Consider a scenario where you need to daily download a CSV file containing sales data of the previous day from a point-of-sale (POS) software or server.

Now, import the CSV database to an Excel worksheet. After importing, you get rid of unnecessary columns and rows.

Now, you need to convert the dataset into a table where anyone can apply filter or sort functions to gain data insights. Or, you most likely need to create a visualization using an appropriate chart.

Instead of repeating these steps each day, you can record the actions in an Office Script and execute the script thereon. Also, you can add a button on the worksheet so anyone else can create the report in your absence. You don’t need to explain the process to the person who’s filling in for you.

The above is an example of a sales dataset that might very much look the same as you’ve got on your Excel worksheet. It has 25 columns and 3000+ rows of data.

From it, I need to create a sales report by year, format it, and create a visualization that my manager can easily read. I need to do this every day when I start my workday, so I’ve created an Office Script for this task. If you also want to do something similar to this, here’s what you should do.

Open your input dataset, for example, the CSV file as an Excel worksheet. From now on, you must start recording each step you do on the worksheet space using the Record Actions feature of Office Scripts.

To do that, go to the Automate tab on the Excel ribbon and click on the Record Actions button located in the Scripting Tools block.

You should see the Record Actions navigation panel on the right side of the Excel app. This panel would show all the steps being recorded.

From now on you must carefully execute the steps on the worksheet. If you make any mistake that Excel can undo, use the Ctrl + Z keys to revert the changes. For example, if you’ve deleted a few columns and later find out that you need those, get back the columns using the Excel undo feature.

As you keep making changes in the dataset, like deleting unnecessary columns and rows, formatting text, formatting cell background, create objects (tables or visualizations), make sure that those activities are being recorded in the Record Actions panel.

Once you’re done recording the steps, hit the Stop button on the Record Actions dialog.

The Office Scripts navigation will show a spinning icon. When the script is ready, you’ll see the Code Editor panel with a generic script name, like Script 106.

Click on the generic name to give the script a special name you can remember, like Create a line chart. Click anywhere else on the Code Editor to save the new name.

To add a narration for the script, click below the Description section and start typing what the script is for, what it does, and so on. Make it informative so anyone in your team or organization can understand the scope of the script.

The script you record gets saved automatically in Automate > Office Scripts.

The next day when you need to create the same report from fresh sales data, simply open the CSV file in a new worksheet. The Excel workbook could be the same or new. It won’t matter as long as you’re logged in from the same Microsoft 365 account.

Go to Automate > Office Scripts and click on the Create a line chart script. Now, hit the Run button to perform all the recorded tasks in a flash.

The script shall work effectively if the new dataset has the same structure as the previous dataset. However, the dataset inside the columns can be different. Column and row numbers should also be the same as in the previous CSV file.

Tips On Recording Excel Office Scripts

The following tips and tricks will make you more efficient when using Office Scripts with the Record Actions command:

Sharing an Office Script

So, you want to create an Office Script in a data entry workbook so your data entry operators can easily perform repetitive tasks with 100% accuracy.

For this, you must share the script with them. Though you can copy and paste the Office Script from the Code Editor and share the code with anyone, it requires advanced Excel skills to reuse the code.

Instead, you can perform a simple trick that I often perform myself. Open the workbook and access the worksheet in which the data entry agents would run a script.

Now, open the Office Script that you want them to run. Expand the Script access section.

Highlight a cell within the worksheet. Then, click on the Add in workbook button.

Now, anyone who can access this workbook from their Microsoft 365 account, will see and be eligible to run the script on their Excel desktop or web app.

Changing Office Scripts Recording Mode

The default Office Scripts recording mode is the Absolute mode. This mode records cell by cell. If you edit A5, the same cell will be edited the next time someone executes the script.

However, if you like to make the script more flexible, you can turn on the Relative mode.

On your worksheet, start an Office Script recording session. Click on the Absolute mode drop-down on the Record Actions dialog and choose Relative mode.

📒 Read More: How to Add Images, Comments, URLs, and More to Excel Using VBA and Office Scripts

Conclusions

If you’ve just started to use Office Scripts in Excel or planning to use this powerful feature, start by recording actions. As you gain experience in the tool, you can edit the recorded scripts to inject more advanced actions if you’re aware of TypeScript programming.

This article helps you with basic to intermediate knowledge of Office Scripts recording in Excel. Give the Record Actions tool a try and comment below if the article helped you gain a foothold in the vast landscape of Excel Office Scripts.