Excel automation

Not more spreadsheets to analyse and chart…

Always a regular cry in the office.  Reports produced on a regular basis need to be embellished, with charts that involve some fairly complicated calculations – cross-reference look-ups, previous month comparisons, and all that sort of thing.

Probably, now this is done largely by hand.  The data sits in several spreadsheets and/or databases, and is extracted, filtered, pivoted and everything else that was necessary to get it ready, before feeding into a chart.  When the chart was ready, it is pasted into the report.

Wouldn’t it be great with the data is all in one place, and with a single front-end, the necessary criteria set from drop-downs.  Once these selections have been made (locations, benchmarks, which dataset to use, and so on) you click a button and go and make coffee.  Actually, you haven't really got time to make coffee as the process takes just a couple of minutes – if that.

In this instance, any number of queries can be run; sometimes one query, sometimes several to generate the data.  Each one automatically loading a into an Excel workbook, in to the right columns, in the right order to aid presentation.  Then, your screen explodes into a fresh multi-coloured set of charts based on the information just collated! And is that the pivot table I slave over every month appearing before my very eyes!

In the past that has meant spending anything between a couple of hours to a couple of days for more complex reporting every period. Multiply that by twelve, and it becomes a not insignificant amount of time in a year m to get these twelve reports, graphs and charts prepared.

Say it was half a day of someone's time, at the average salary of £26,000 - that's a cost of around £60&.shtml#43; to the business for each report, or chart.  Each and every time.

If reporting is weekly, that's going to be more than £3,000 every year - more likely a lot more than that.  If it now takes 30 minutes to do the whole job, that's a total of just over 4 days for the whole year's worth (at one report a week).  The four days will cost under £500.  So you get to keep £2,500 to do something else with.

What would you like to do with it?

Excel automation by Bob Lewis-Basson – 07802 441 728