Excel Automation

Creative business solutions using Excel can automate repetitive, time consuming tasks

Check out my Time Wasting Calculator below

What do I mean by Excel Automation?

More time to do the things you love

Excel Automation is such an important topic and close to my heart. What do I mean by Excel Automation? I mean lets use Excel's POWER to automate the repetitive tasks robbing you or your staff of precious time to do more value adding tasks and of course to get home on time and perhaps reduce unnecessary overtime. Wouldn't it be better if automating spreadsheets means less time preparing and more time for the analyzing.

Not only does it waste time but tedious tasks can make you a bit cranky too! If your job can be made more enjoyable, there is more time & energy for the good things in life!

It might be that quotations are taking too long, preparing reports has so many steps or loading and updating new data for a forecast takes too much time.

The Excel power tools for automation are better spreadsheet structure, Power Query, using Excel Tables and last but not least using VBA (or Excel macros).

Some of the common automation objectives are to the right for you to check out.


Messy spreadsheets limit Excel’s power – data in various worksheets or workbooks makes it hard to combine, re-use, sum, lookup, to use Pivot Tables, to be interactive and hard to update.  Better structure & maximizing the use of Tables for dynamic updating is a key automation success.

Sometimes, printing takes too long because there are columns or rows to be hidden or multiple ranges to be printed or of course the range to be printed is dynamic or different each time.  A print macro can do all that setting up for you in a blink.  

Or you or your staff are re-keying data from your cashbook spreadsheet to your Accounting software – Excel can convert data from one format to another in the columns and format your Accounting software like MYOB or Xero needs for import.  

A macro can even save this data output range where you want it as a .csv or text file ready to import into your software.  

I used to enter quarterly data into MYOB for each BAS for the Dental Practice we owned and this approach cut a horrible 4 hour job into 10 minutes.  The conversion range also included some key checks to minimize the risk of trying to import data with errors.

Data should be entered once and re-used where ever it’s needed so stop re-keying or copying and pasting the same data in multiple places across worksheets or
Better structure, use of Tables, lookups & Power Query or VBA alleviate this.

Updating data like a new month’s actuals, a new supplier price list, new exchange rates, new forecasts – these tasks occupy a lot of what any analyst or manager does in a day.  Or used to.  So much can be done now with Excel Tables and Power Query or VBA.

Prices, part numbers, product descriptions, disclaimer clauses whatever is tied to a key field like a product number.  Use of Tables & lookups solves much of this.

Keying data to other software like your accounting package is a massive time waster.  Power Query or VBA can extract data, rearrange it to the needs of your other software & be imported via a .csv or .txt file from a set folder

I probably don’t have to explain using a centrally maintained template for users is obviously more efficient and is more efficient for improvements and fixes.  But also in VBA, it’s often better to have the code copy from an easily maintained template to the final output whether that be new workbooks, worksheets, pivot tables or pivot charts together with all the formulas, data validation and formats needed.  I once did a Budget application where over 100 branches needed to input their budget.  The final workbook just had a template sheet and the VBA code and few buttons to create all the input worksheets (in stand alone workbooks), pre-filled from data in a Table and saved to a defined folder each with the applicable branch name.  As management made changes to the budget requirements I only had to amend the template sheet.

Identify time wasters

Try out this calculator to see how much time is really wasted

This spreadsheet can calculate an estimated hourly cost of one of these time wasters.  

But of course it can’t calculate the potentially massive opportunity cost of more important work not being done.  Automation is highly leveraging.

Some blog posts related to Excel Automation

Re-usable Print Macro

If your application needs to print dynamic reports, then ideally to save tedious manual print setup, you need a re-usable print macro. If your application

Read More »

Other Automation ideas

Contact me

Send me an email at [email protected] or call me on 04088039960 or better still, complete the form below with a few details so I have better idea on what you are after.

Excel Expert John from the Excel Factor