post

Using Excel to Automate Business Tasks

We all have to deal with them. They are hair pulling, mind numbing and time wasting.  Some are inherited from the systems we work within but many turn out to be self-inflected. I’m referring to inefficient tasks, you know – those processes that all businesses have – mundane, repetitive overhead – costing real time and money to repeat day in and day out. They destroy morale, decrease job satisfaction and in some cases can lead to significant turnover or losses. But we don’t have to live with them; we simply have to reinvent our processes so that they are streamlined and automated, typically through process specific software.

I’ve worked for and with a wide range of businesses ranging from Ford Motor Company to small 3 man startups – and all of these businesses face these kinds of issues.  Of course when you are a multi-billion dollar company, you can afford to have a team of experts running around helping to remove those inefficiencies with enterprise class software.   And I’ve noticed that companies in very focused and common industries, companies like accounting firms, medical practices and insurance agencies, have a myriad of choices of off the shelf software that will solve most of their problems. But for those small and medium size businesses that don’t fall into these categories or that don’t have an army of people running around fixing processes, there are not always readily available and affordable choices for business automation software.

Excel to the Rescue

Luckily, affordable custom automation solutions can be developed using Excel as the engine.  It has a wide range of features that can help with many tasks.  It has one of the best pivot table features ever developed, great for large data sets that need to be sliced and diced in a number of ways. It can import data directly from almost any database and has a large selection of built-in functions for working with different data.  Conditional formatting allows for the development of nice looking user input forms and the built in VBA scripting language opens up a whole world of possibilities.

But perhaps what makes Excel a great choice for many businesses is the ease of use and implementation.  It doesn’t take long to teach newbies how to interact with Excel and use it for simple tasks.  Users with a bit more experience can even take advantage of the great Macro Recorder to create simple automation.   Combine that with the fact that there is no need to compile or install workbooks to share them with others and it becomes obvious why Excel is such a great choice.

Common Excel Automations

I’ve seen excel used in a variety of business automation tasks, from very simply to extremely complex. Here are just a few of the most common tasks that can be automated with Excel:

  • Custom spreadsheet templates – these would include custom forms and calculators specific to a unique business need
  • Manipulating text files from legacy systems – importing text files from other databases usually to rearrange the data, format it and add summary tables or secondary calculations
  • Custom and informative dash boards – creating dashboards for management that summarize and highlight relevant data, based on the managers needs
  • Cleaning large data sources– often used to merge data to create custom lists, look for duplicates and find missing data, often to prepare data so it can be imported into another system
  • Merging data from separate systems – compiling and matching data from different systems such as sales and payroll to provide additional insight
  • Converting data through custom user defined functions (UDFs) – custom functions tailored to a specific industry or used to streamline repetitive calculations
  • Retrieving and storing data in existing databases – whether to load large data sets or to pull data for summary reports, Excel can be used as an interface to existing databases
  • Gather process data – gathering data from devices such as scales and bar code scanners to create reports or to store the data in a database

A Closer Look

Let’s take a closer look at a few examples of actual automation I’ve done over the years.

File Conversion

I worked for a company where we were continually manipulating data files from a piece of diagnostic lab equipment so that we could make visual comparisons of the data.  The data file is a simple CSV with a single sheet containing headers and data points. A sample of the file can be seen in Figure 1.

Figure_1

 Figure 1: Source CSV file from diagnostic device

In order to examine the raw data, my coworkers had to modify the file including baseline adjusting the values, adding pivot tables and charts and performing specialized calculations to determine the CT (crossing threshold) which involves taking the second derivative.  To present the data to others in reports, they also had to manually adjust graph settings and titles. In all, they were spending 15 to 20 minutes adjusting a single file and often had 8 to 10 files a day to prepare – that adds up to 3 to 4 hours a day.  Needless to say it was taking a toll.

So they approached me and asked me to automate the entire process.  The custom Excel add-in I created for them allowed them to process the entire file in less than 5 seconds.  This included adding all of the formatting, calculations and graphs they needed. You can see a small sample of the converted data in Figure 2.  At last count, they have been using the same system for over 5 years.  That adds up to over 3500 hours saved, as well as fewer grey hairs all the way around, simply by using a simple piece of automation.

Figure_2

Figure 2: Example of file converted by Excel Automation

User Defined Functions

I once did a project for a company that had an old legacy database system for production, but a new system for generating and tracking sales.  A team of analysts were tasked to cross analyze data from both systems, which involved running reports from both systems and then merging the data between the systems into one comprehensive data set which could then be analyzed. This is a process they repeated several times a day.

One of the challenges they faced was matching up the data since the part number format was different and while the old system included city information, the new system only contained region information.   This meant that the analysts were constantly reusing the same long and complicated formulas to modify the part numbers and cross reference the cities to regions.  Figure 3 below shows part of the information that was available in each system.

Figure_3

Figure 3: Example of legacy and new data

I was asked to help simplify the process by creating custom User Defined Functions (UDFs) – custom functions that can be used like any other function in Excel (such as SUM() and vlookup()). One of the first functions I came up with was CONVERTNUMBER() which converts from the old style part number to the new style.  To use the function, the analyst simply had to enter =CONVERTNUMBER(A2) where A2 is the old value number and the function would do all of the work of conversion.

Another function we created was IDREGION().  When entered (=IDREGION(B2)), the system would automatically convert the legacy city information into the corresponding region by using a lookup table.   Figure 4 below, shows how these functions were actually used to create Figure 3 above.  You can see that the UDFs use the values in columns A and B as inputs to create the values in columns D and E.

figure_4

Figure 4: User Defined Functions in use

UDFs are great for simplifying repetitive and complex calculations.  Since they are built using VBA, there really is no limit to what can be achieved with a UDF.

Additional Examples

Check out our portfolio for other automation examples.  Some of the examples also have video demonstrations of the automation in action.

Give it a Try

These are just a few simple examples of how Excel can be used to automate repetitive business tasks. Adding a little bit of automation can really save valuable time and money, improve job satisfaction, standardize processes and eliminate errors.

So don’t be afraid of those inefficient tasks.  Take some time to identify the tasks that are increasing blood pressure or killing productivity and eliminate them using the powerful features built into Excel. Start with small easy projects and move on to the harder ones and you will quickly realize benefits and become the office hero.

Speak Your Mind

*