Are you spending too much time doing manual work in Excel? Let’s change that today!  As an Excel development company, people think my company is good at Excel formulae or charts. Actually, the reason customers come back to us is for Excel automation – the process of getting manual work done very quickly / at the click of a button and without errors. 

But – you don’t need us!  Excel Automation is simpler than you think.  You can start reclaiming your time TODAY – let’s dive into level 1. 

EXCEL DOWNLOAD FILE LINK

WATCH THE VIDEO Here

00:00 INTRODUCTION

00:45 LEVEL 1

Excel’s dynamic array formulae are a great way to get started with Excel automation.  In the video, I show you how to use UNIQUE to pull unique values out a dataset, and FILTER to get the effect of a manual filter via formulae.  Filters!  These have long been a bug bear of mine.  People spend so much time clicking around filter menus to display the data they need to see.  The truth is, it’s not necessary, if you know the right automation techniques. 

VIDEO: STOP FILTERING EXCEL! (One Million Views) 

01:49 LEVEL 2

The next level of Excel automation is Power Query.  At the basic level, this tool allows us easily to perform operations on a dataset such as de-duplicating data or removing empty rows.  At the more advanced level, Power Query brings together multiple datasets by appending or merging data.  In the video, I show you how to do both in just a minute or two. 

Here, I’m using Power Query to bring together two datasets.  These datasets are on the same sheet, but your data could be anywhere – including another file or an online data source.  Simply clicking the ‘Refresh’ All button allows you to update the collated dataset with new data.  From here, you have limitless options to manipulate the data.  In the video I show you how to deduplicate the dataset with Power Query. 

VIDEO: Power Query For Beginners

03:18 LEVEL 3

Excel’s dynamic array formulae, featured in level 1, allow us to focus on a single value.  In our case, a specific video.  But, suppose you want to quickly analyse multiple videos, switching between them for quick comparison.  Is there a quick, visual way to do that?  Welcome to level 3 of Excel Automation! 

Pivot tables can sound scary and, if you’re like me, you probably avoided them for years.  But, with a clear idea of what you are trying to do, pivot tables are straightforward.  And, crucially, they unlock a range of quick and easy Excel dashboard creation options.    The quickest way to automate analysing data interactively is to use a pivot chart with slicers, as I show in the video when I use a pivot chart to quickly visualise a dataset. 

VIDEO: Quick And Easy Excel Dashboard Creation 

04:48 LEVEL 4

Now, a step up in difficulty,  and in possible impact on your day-to-day work.  Excel VBA is the coding language that sits behind Microsoft’s spreadsheet software.  Indeed, many professionals are aware of VBA in Excel.  But, few seem to know VBA dovetails with other productivity tools in the office suite including Word and Powerpoint.  Yes, just a few lines of VBA code could allow you create a beautiful Word-based report or Powerpoint presentation, based on analysis from Excel.  Welcome to the next level of Excel Automation! 

In the video, I show you how to use Excel VBA to transfer an Excel chart to a Powerpoint presentation at the click of a button.  But, in terms of what’s possible, this is just the tip of the iceberg.  See the video below for full instructions on how to automate full Powerpoint presentation creation … instantly! 

VIDEO: HOW TO AUTOMATE POWERPOINT PRESENTATION CREATION WITH EXCEL VBA (INSTANT PRESENTATIONS!) 

06:00 STORYTIME 

07:44 LEVEL 5

Previous tools confined us to the desktop – but what about working in the cloud on Sharepoint and OneDrive, as so many professionals are these days?  Level 5 of Excel automation deals with how to speed up tasks in the modern online work environment. 

Power Automate allows us to automate manual tasks on Sharepoint.  In the video, I demonstrate a Power Automate flow that takes a chart from an Excel spreadsheet, copies it to an email, and sends it to a designated recipient – all in the cloud!  Power Automate doesn’t quite match the awesome power of Excel VBA on the desktop but, combined with Office Script to do specific jobs in Excel files, it gets pretty close.  It’s an essential for the modern professional looking to streamline workflows in their organisation. 

VIDEO: Power Automate For Beginners

VIDEO: Excel Office Script For Beginners 

So, good luck with Excel Automation!  I would love to answer your questions, and hear about your punch-the-air moments, in the YouTube comments.