Record tasks in Excel with Automate

Q. I noticed there is a new tab in my Excel 365 called Automate. What does this do?

A. There is an Automate tab available in Microsoft 365, Excel for Microsoft 365 for Mac, and Excel for the web. The Automate tab allows you to create and run Office Scripts, which allow users to automate tasks in Excel. Basically, you can record a task in Excel with a script, then run that script any time to have Excel repeat the same steps. You can also share scripts with others so they can perform the same task on their device.

Although scripts may sound similar to macros, there are some differences. Macros, which are primarily intended for desktop use, are optimized for interacting with a user’s local environment, but they lack a straightforward method for internet communication. Office Scripts, on the other hand, use JavaScript and can access the internet. Office Scripts could, for example, communicate automatically with ChatGPT over the internet but macros could not. Another difference is that the web version of Excel supports Office Scripts but doesn’t support macros. Office Scripts can use Power Automate, while macros cannot; Power Automate allows you to instruct Office Scripts to run automatically at certain times or following certain triggers, without having to manually command it to do so.

Let’s look at an example of how to record a script to complete a routine task. It is not uncommon for accounting professionals to receive reports from colleagues or from a reporting system that need a bit of formatting or adjusting to make them compatible for our use. For example, I receive the spreadsheet in the following screenshot weekly.

The loan information changes each week, but I have to fix the formatting and add a Loan Amount column every time I receive the report. So, I am going to create a script to do these steps.

To follow along, download the Excel spreadsheet that I made for this demonstration. Note that this walkthrough and the accompanying video (available at the bottom of this page) were done using Microsoft 365 for a PC. Other devices may work differently.

Start by clicking on the Automate tab on the Ribbon. Here, you are able to create scripts, by either recording them or writing them. We will just focus on recording, which requires no coding knowledge.

When you are ready, click the Record Actions icon in the Scripting Tools group. Now, everything you do to the Excel workbook will be recorded so you can repeat those same steps later. My steps included selecting the headers and making them bold and underlined, adding a new column H, titling it as Loan Amount, adding a formula to subtract Down Payment from Selling Price, and finally, auto-sizing all columns to fit the contents.

Once you are done recording your steps, click Stop in the Record Actions pane. You now have a script. It will be given a generic name by default, so you should rename it something descriptive. I named my script Formatted Loans. Optionally, you can give the script a description. See the following screenshot for my formatted loans spreadsheet.

Each time you receive the unformatted report, simply open the worksheet, go to the Automate tab on the Ribbon, and select the script you created from the group Office Scripts. Select Run from the Code Editor pane.

Now you may be thinking, “Hey, I could have done all this with a macro.” That’s true, and I chose a simple example to walk you through the basics. What Office Scripts can do that macros can’t is communicate with other applications over the internet. I showed an example of this in my April Technology Q&A item “Build ChatGPT into Excel.”

In that case, I went to openai.com, logged in, and generated an API key that I inserted into the script. Afterward, running that script in Excel automatically logs me in to openai.com without my having to manually go to the website. I can then ask a question in Excel and receive an answer from ChatGPT.

Hitting the Submit button triggers the Office Script in the ChatGPT example, but you can use other triggers. For example, you can set Office Scripts to run at a certain time (e.g., 9 p.m. every Tuesday) or in response to certain emails.

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.