If you are new to the Office Scripts universe and want a quick overview of its capabilities and differences when compared to Excel macros and VBA, check out this post for a write-up and video showcasing these topics.
Excel VBA vs Office Scripts
What is Office Scripts in Excel?
Office Scripts are a powerful feature in Excel that allows you to automate repetitive tasks and streamline your workflows. They work by recording the steps you take in Excel and converting them into a script—a small piece of code that can be reused and shared.
- Action Recorder: This tool captures your actions as you perform them in Excel, turning them into an automated script. No coding experience needed!
- Code Editor: For those with some coding knowledge, the Code Editor lets you customize or build new scripts from scratch using JavaScript.
- Easy Sharing: Once your script is ready, you can share it with your team. Anyone with access to the workbook can run the script with a single click.
Office Scripts are perfect for automating time-consuming tasks like data entry, formatting, or generating reports. They help teams work more efficiently by reducing manual work and minimizing human error.
When to Use Office Scripts in Excel
Tired of doing the same tasks over and over in Excel? Office Scripts can help you automate those repetitive actions, saving you time and effort. Here’s how it works:
If you regularly perform tasks like opening a .csv file, deleting columns, formatting tables, or creating Pivot Tables, Office Scripts can be your Excel automation for the entire process.
Simply use the Action Recorder to capture your steps once, and then run the script with a click.
You can even combine it with Power Automate to create seamless workflows across different apps. Check out this post to see how this works.
Creating Your First Excel Script
Ready to automate your tasks in Excel? Let’s walk through how to create your first Office Script in just a few steps.
Step 1: Get Started with Office 365
Log into your Office 365 account and open an Excel file. For this example, we’ll use a file with three sheets, each containing a small table of Position and Salary data.
Step 2: Understanding the Data
Each sheet has a different number of items—one has 5, another has 8, and another has 3.
Plus, these tables aren’t in the proper Excel Table format, but just simple lists. We’ll fix that and automate the process using Office Scripts.
Step 3: Recording Your First Script
Here’s how to get started:
- Click on the Automate tab.
- Select Record Actions.
💡 Tip: If you don’t see the Automate tab, your network admin may have disabled it. Or your account might not be part of Microsoft 365 Business or Enterprise.
The Record Actions tool works like the Macro Recorder in desktop Excel, capturing each step as you perform it. When you start recording, a panel will open on the right, showing your actions.
Step 4: Creating a Chart
Let’s create a chart to visualize the data:
- Select the data range (A3:B8) on the first sheet.
- Go to the Insert tab and choose Clustered Column chart.
Featured Course
Unlock Excel VBA & Excel Macros
Step 5: Customizing the Chart
Now, let’s make the chart more informative:
- Change the bar colors to medium-dark gray.
- Remove the legend and Y-axis.
- Add data labels to the ends of the bars.
- Change the chart title to “Salary by Department”.
- Set the gridline color to light gray.
- When you’re done, stop the recording by clicking STOP in the Record Actions panel.
- Rename the script to “Create Column Chart”
Step 6: Testing the Script
Now, let’s test your new script:
- Select a different sheet (e.g., Dep 2) in the workbook.
- Click RUN.
You’ll notice that the script only works for the original range (A3:B8). Don’t worry, we’ll fix that to make the script dynamic!
Step 7: Making Your Script Dynamic
To make your script work for different table sizes, we’ll need to modify the code. Here’s how to access the script and make the necessary changes:
- Click on the ellipsis (three dots) next to the script name in the Automate tab.
- Select Edit (or Advanced Edit, depending on your version).
Once you’re in the editor, follow these steps to make the script dynamic:
Dynamic Chart Titles
First, we need to capture the contents of cell A1 and store those contents in a variable. We will name our variable “myTitle”.
- After the first LET line of code, add the following lines of code to perform the text capture step.
// Get the Chart Title
let myTitle = selectedSheet.getRange(“A1”).getText();
- Change the line that sets the chart title to display the value in cell A1 of each sheet
chart_1.getTitle().setText(myTitle)
Dynamic Chart Range Selection
Create a variable to capture the dynamic range instead of the fixed range A3:B8:
// Get Dynamic Chart Range
let myRange = selectedSheet.getRange(“A3”).getSurroundingRegion().getAddress()
If we delete the existing chart and re-run the script, we see the updated chart results.
Observe the dynamic title taken from cell A1 and the expanded data range (A3:B11).
Testing the script on a smaller dataset that occupies cells A3:B6, we get the following results by clicking our listed script on the Automate ribbon and select RUN.
Why Office Scripts Beat VBA for Portability
One of the key advantages of Office Scripts is their portability. Unlike VBA, which is tied to individual Excel files, Office Scripts are stored in your OneDrive.
This means you can easily run them across multiple files with similar structures—without having to rewrite or reattach the code every time.
With Office Scripts, you can:
- Reuse Scripts Across Files: Use the same script in different workbooks without extra setup.
- Work on the Web: Office Scripts run directly in Excel for the Web, making them perfect for cloud-based work.
- Collaborate Effortlessly: Share scripts with your team, allowing everyone to automate tasks without needing to install or understand VBA.
Compared to VBA, which often requires saving code within each Excel file, Office Scripts give you more flexibility, making it easier to scale your automation.
Where Are Office Scripts Saved?
Great question! Office Scripts are saved in your OneDrive, separate from your Excel files. This means they’re always available in the cloud, making them easy to access and use across multiple workbooks.
Here’s why this is a game-changer:
- Cloud-Based Access: Since your scripts are stored in OneDrive, you can run them from anywhere, whether you’re on your desktop or using Excel for the Web.
- No File Attachments Needed: Unlike VBA, which stores code within individual Excel files, Office Scripts are saved separately, so you can reuse them across different workbooks without any hassle.
- Easy Collaboration: Share your scripts with your team, and they’ll have access to the same automation tools, no matter which file they’re working on.
To find your scripts, open your OneDrive and look for the OfficeScripts folder under “My Files.”
From there, you’ll see your saved scripts, ready to be used anytime you need them!
Changing Excel Script Options
Back in Excel, clicking the Options button (three dots) reveals several administrative options that can be exercised on the script.
If you select SHARE as an option, those users who have been granted access to this workbook will also have access to the script.
You can also leverage the power of Power Automate to create a flow and automate the execution of the script.
Edit Mode Options
When viewing the underlying code, you can select the same Options button to display the available scripting options.
The LOGS option is useful when debugging code problems.
There are also options behind Editor Settings for customizing the look and behavior of the Code Editor.
Running an Office Script in Microsoft Teams
If we switch to Microsoft Teams, browse to our Files section, and open the Excel workbook, we can select the Automate tab just like in Excel for the Web and execute the “Create Column Chart” script.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
