For decades, Excel macros powered by VBA (Visual Basic for Applications) have been the go-to solution for automating repetitive tasks. From formatting reports to manipulating data, VBA offered a well-stocked toolkit for power users. But as the workplace shifts toward cloud-based collaboration and cross-platform accessibility, it’s time to explore modern alternatives: Office Scripts and Power Query.

NOTE: I am not a coder. I don’t aspire to be a coder. However, I’ve been able to navigate VBA as needed throughout my career. So, this blog is not necessarily “pro-code”, it’s pro-automation and pro-streamlining of workflows. Too much time is spent doing data cleanup and that needs to be reduced to ensure we’re doing more value-added work.

Why Move Beyond Macros?

Traditional macros are tied to desktop Excel and Windows environments. They’re powerful, but they do have limitations—especially in today’s hybrid work culture. Macros don’t run in Excel Online, they’re difficult to share securely, and they lack integration with cloud services like Power Automate. This makes moving away from VBA/Macros an imperative.

Enter Office Scripts and Power Query, two tools designed for the modern Excel user. Together, they offer a future-focused approach to automation that’s scalable, secure, and cloud-native.

Office Scripts: Automation in the Cloud

Office Scripts use TypeScript, a modern programming language that runs in Excel Online. They’re ideal for automating tasks like:

Applying consistent formatting across workbooks

Creating charts, PivotTables, and visual elements

Running conditional logic (e.g., if/else statements)

Integrating with Power Automate to trigger workflows

Unlike VBA, Office Scripts are accessible from any device with Microsoft 365. They support version control via GitHub and can be embedded into broader automation flows—like emailing reports or updating SharePoint lists[5].

If you’re already familiar with JavaScript or web development, Office Scripts will feel intuitive. And if you’re new to scripting, the built-in recorder makes it easy to start without writing code - see our Amateur tip at the bottom of this post.

Power Query: Data Transformation Made Easy

While Office Scripts shine in formatting and logic, Power Query is your best friend for data transformation. It’s a no-code ETL (Extract, Transform, Load) tool that lets you:

Import data from hundreds of sources (web, databases, files, even photos)

Clean and reshape data with intuitive steps

Merge, filter, and pivot data for analysis

Refresh queries automatically with new data

Power Query is especially useful for large datasets and recurring reports. It’s built into Excel and integrates seamlessly with Power BI, making it a cornerstone of modern data workflows[3].

When to Use What?

Here’s a quick guide:

Image created in partnership with Copilot AI

For many organizations, a hybrid strategy works best: keep VBA for legacy models, use Power Query for data prep, and adopt Office Scripts for cloud automation[5].

Making the Transition

Switching from macros to Office Scripts and Power Query doesn’t have to be daunting. Start by identifying your most-used macros and evaluating whether they involve:

Data import and transformation → Power Query

Formatting and logic → Office Scripts

Then, explore Microsoft’s Office Scripts documentation[3] and try converting a simple macro. You’ll be surprised how much cleaner and more flexible your automation becomes.

Final Thoughts

The future of Excel automation is cloud-based, collaborative, and code-optional. By transitioning from macros to Office Scripts and Power Query, you’re not just modernizing your workflows—you’re unlocking new possibilities for efficiency, scalability, and integration.

Whether you’re a trainer, analyst, or business user, now’s the time to embrace the tools that align with how we work today—and tomorrow.

Amateur Tip

If you’re nervous about trying to record an Office Script for the first time, try recording a Macro in Excel Desktop. When you’re done, you may find a window pop up that says an Office Script was also recorded. Keep it and check it out. That’s a great way to get some training wheels using this tool.

Want More Training?

Contact us at support@excelandflourish.com. We’re happy to explore training options with you.

References

[3] learn.microsoft.com

[4] www.jj162.com.au

[5] www.blackstonedatadynamics.com

Content generated in partnership with Copilot AI.