Get the Office Scripts Practice Workbook
Want the file without building it from scratch? Download the ready-to-use workbook and follow along with every step in this tutorial.
- Practice recording and running your first Office Script
- See the dynamic chart title and dynamic range examples already set up
- Use the same script across similar files without rebuilding the workflow
Enter your email below for instant access.
What Are 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.
If you’re deciding between Office Scripts and Python in Excel for automation, see our Python in Excel guide for a comparison of when each tool is the right fit.
When to Use Office Scripts (Instead of VBA or Macros)
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.
For a deeper technical comparison of VBA and Office Scripts (including side-by-side code examples), see our VBA vs Office Scripts breakdown.
How to Record Your First Office Script (Step by Step)
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.
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.
Office Scripts vs. VBA: Portability and Cloud Access
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.
Want the full side-by-side breakdown? Our VBA vs Office Scripts comparison covers syntax differences, platform support, Power Automate integration, and when to use each one.
Where Are Office Scripts Stored? (OneDrive)
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 the practical benefit::
- 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!
How to Edit, Share, and Manage Scripts
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.
How to Run 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.
Download the Free Practice File
Get the workbook with the code and instructions to automatically create column charts.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
