Below you can find some examples of my Excel skills used to analyse data for a personal project and to automate a task in a professional context which added value to my employer.
I have previous experience of using my Excel skills to automate business problems saving time and money for my employer. I have created an anonymised and generalised example with a fictional context of one such instance that required combining numerous data sources.
You can download the final dataset by clicking here
At the end of the year a manual reconciliation of sales data would take place to ensure data consistency across a number of different data sources and that installment payment charge calculations were correct. The company would spend part of their budget on temps to perform this task so it was costly and time consuming for them. The temps would manually consult and compare each data source and then once finished perform a manual calculation before filling out a checklist with their findings.
Each case would take ~30 minutes and the work was very manual and laborious
I identified that Excel provided a suitable platform to significantly reduce the time taken to complete this task so I wrote a tool using Excel with the intention of automating as much of the task as possible.
I then realised if I copied the relevant sections of each data source into a workbook most of the work could be done in Excel using lookup functions rather than manually and then simply by selecting the relevant sale ref from a dropdown the checklist checks could be run automatically and the output copied and pasted into the checklist. Pretty much the entire task could then be automated. All the end user had to do was select a sale reference and then copy the output into the checklist. If the success criteria wasn't met the tool would highlight issues for further investigation
The finished workbook condensed the time taken taken to reconcile a case from ~30 minutes down to less than 5 minutes. Given that there were hundreds of cases this resulted in a considerable time and cost saving on temporary workers for my employer.