A recent consulting project delivered another example of how Excel automation can be used to drive efficiencies for business.

Ranked Performance Report

In this case the client runs a number of food service outlets and uses a template to capture key performance metrics for each shift at all stores. The customer wanted to quickly identify which stores and which shifts were performing the best and the ones that needed to improve. The data was entered on a weekly basis into a separate template and a separate workbook for each store. The only way to collate that information was a manual process to open each workbook, extract the data and then store it to a summary workbook for analysis. With the data coming in from multiple stores on a weekly basis this process could have taken many hours and needed to be repeated on a regular basis to create a view of the performance for those stores.

Extract and Transform – Data Entry Template


This type of business process is one that can be automated with Excel routines to read the source template files and then transform the data for reporting. This is exactly what we did for this client and the laborious task for manually opening each Excel workbook to source the performance data was replaced by an Excel routine that was able to extract and then rank the data from all shifts from a store in a matter of seconds. See below a demonstration of Excel routine that we developed for this project.


[fvplayer src=”https://xlautomation.com.au/wp-content/uploads/2018/08/Extract-and-Transform-DEMO.mp4″ splash=”https://xlautomation.com.au/wp-content/uploads/2018/08/Extract-and-Transform-Ranked-Report.jpg” width=”1280″ height=”720″ autoplay=”true” controlbar=”yes” share=”yes” caption=”Extract and Rank Performance” splash_text=”Extract and Transform of Performance Data” lightbox=”true;600;350;Extract and Transform Performance Data”]