How to Create Scalable Automations in Excel [4-Step Framework]

Read time - 5 mins

If you're spending too much time doing manual tasks in Excel, this newsletter will help you.

Another shocking statement to kick off this week's newsletter.

I'm not that great with Excel 😱

I've been getting better, but I'm not a Power user yet.

Using AI has improved my usage massively.

Because I like to code, I'm easily sucked into Python when it comes to automating stuff.

But. Not everyone has access to Python at work.

And it's not always the easiest way to automate.

So how do we automate in Excel?...

 
Automating in Excel

Recently, I was blessed to have ANOTHER Microsoft MVP on the podcast.

His name's Mark Proctor and he runs 'Excel off the Grid'.

Mark helps people automate in Excel.

He has an incredibly pragmatic approach to automation.

And this framework is based on his insights from our conversation.

Before we start let's re-iterate some core principles.

1. AI can help support you in writing queries & code.
2. BUT - You will get better results from AI if you have base knowledge & skills.
3. Start by learning from experts, and then use AI to help you put principles into practice.

With this in mind, let's get cracking.

The Framework

1/ Data

A lot of the manual effort in Excel comes from manipulating data.

This is why getting the data right is a crucial step in creating a clean and scalable process.

Define your data sources (another sheet, another file etc) and then get them into Power Query.

2/ RR&R

Once you've a routine to get data into Power Query, it's crucial that you have a way of dynamically updating it should the source data change.

RR & R stands for Refresh, Resize and Recalc.

For example, if a column is added, a range changes, or something's renamed, you don't want this to break your automation.

3/ Language

Using the previous steps may give you enough to automate 80% of your process.

But what if you wanted to keep going?

Your 2 main options are VBA (Visual Basic) or Office Script

VBA - Runs locally on your machine. It's great for Excel, but can also be used across loads of other applications too.

Office Scripts - Cloud based and limited to Excel. Can be built into Power Automate flows

Think about the outcome you're looking to achieve and pick the most suitable language.

4/ Scalability

Several simple automations are easier to scale than 1 massively complicated automation.

Mark references the 'Unix framework' in which automations can be chained together.

Formula Example

Let's compare this approach to using formulas.

Here's a single formula containing several functions.

There's a couple of issues with this:

1. When we make changes, we're affecting the entire formula.
2. There is no way to run each function seperataly.

Here's a formula that references other formulas:

The advantages here are:

1. We can change each function independently.
2. We can choose how we want to total whilst keeping each seperate function intact.

VBA Example

Here's an example of a VBA macro with multiple functions combined:

Every time it runs it will perform all functions.

Here's an example of a macro where the functions are split:

With a separate macro that runs them.

You can then tweak the last macro to run the seperate functions you want, and you can scale it to include more functions as you build.

Putting it into practise

1. Data - Check out Mark's 'How to get data into Power Query post' here.
2. RR&R - Read through Marks resources: 'Expand columns dynamically' here & 'Get data from workbook by position' here to give you some foundational knowledge.
3. Language - Become an insider at Excel off the Grid and get 30 VBA macros + example files here.
4. Scalability - Work through the Excel Automation Machine PDF here.
5. Augment with AI - Try experimenting with prompts like:
   - "I have data in a dynamic array in [spreadsheet tab], walk me through how to pull this into Power Query as the foundation of a scalable automation."
   - "I want to push the data from [spreadsheet tables] into a PowerPoint presentation. Is this possible using VBA?"
   - "Create me a string that I can use to chain these seperate macros together [enter macros]"

You can also check out the full conversation between Mark and I here.

You'll be automating like a pro in no time!

Until next time!

Adam

2 Ways I can help

When you're ready here's how I can help you:

• Book a 1:1 coaching session - We'll turn your tech confusion into tech confidence. Start crushing it here
• Join the AI & Automation Academy Waitlist - Get early bird access to my up and coming course here