Are you an Excel Hero?

Background

Not too long ago, I was approached by a systems consultant to consider writing an Excel solution to create a weekly production schedule for one of his clients.  The reason for using Excel will become clearer in a moment.

The current process

Sales representatives submit a unique Excel spreadsheet / file for each customer order.  All orders are saved to a common subdirectory on the company server.  Every Monday, an administrator then opens each spreadsheet and extracts information for each individual order and manually enters the relevant data into a “master” spreadsheet.  After formatting the entries, the production schedule is then created.

In this case, the client was typically managing 200 to 250 unique orders per week and the time required to create the master spreadsheet could range anywhere from 4 to 6 hours.  Since the sales team was already acclimated to using Excel, the client was reluctant to consider an alternative order management process.

The Solution

I agreed to take a look at the application and after an hour of writing some Visual Basic for Applications (VBA) code, I produced a working model that automatically processed over 200 files and created a production schedule in less than 28 seconds!  I also provided functionality to allow access to each file directly from the master as well as a few other bells and whistles!

The client was impressed by (couldn’t believe) the results and, as with any successful effort, requested additional functionality that they also never thought was possible.

Could this be you?

Although Excel is by far one of the most popular spreadsheets on the market, it is perhaps still one of the most underutilized desktop application in business today.  Although I can only speculate, I think one of the reasons for this is the overwhelming thought of having to read an ever-growing volume of books having an ever-increasing number of pages with each new release.

I would also suggest that some of Excels functions such as pivot tables and look up capabilities are so powerful that people just assume they must be complicated when this really isn’t the case at all.  I can only suggest trying them before making a judgement.

While a number of Excel solutions exist in the form of templates ranging from very specific applications such as our free Excel templates for OEE to the more general and commonly used applications such as dashboards, there are many more applications where “canned” solutions just don’t exist.

Naturally, because many people have access to Excel, they find themselves creating their own solutions “on demand” as was the case with the production schedule above.

An even greater concern is the number of “orphan’ spreadsheets that exist outside the scope of any managed data infrastructure.  The danger here is that these solutions may unknowingly compromise the integrity of the current management system.

I contend that orphan solutions eventually become the hidden or “unknown” extension of the existing infrastructure and present a real opportunity yet to be uncovered by many organizations.

“Homegrown” Solutions

For cases where customization is required, someone in the organization is usually tasked with creating a spreadsheet solution.  Unfortunately, these same individuals are not necessarily as capable as we may first think.  I discussed this concern to some degree in Lean Office with Excel and VBA and also became a topic of discussion at Daily Dose of Excel’s Learn VBA to be Lean.

The result is often a solution that “appears” to meet the requirements – at least on paper.  I have found that these same spreadsheet solutions seldom take advantage of even the simplest functions that Excel has to offer including the most basic arithmetic functions such as SUM as suggested by the animated graphic that accompanies this post.

Although the articles referred to above focus on Visual Basic for Applications (VBA), there are many other very powerful functions that can be used to make better use of Excel’s functionality and increase it’s value in terms of providing effective and efficient solutions.

Getting the Knowledge

There are a number of ways to learn Excel ranging from formal training, reading books, and, of course, the internet.  Although I have been using spreadsheets since their inception, I still consider myself to be a student in many respects.

I recommend reading and working through at least one good general knowledge Excel book and one good introductory book on VBA such as Excel 2007 VBA Programming for Dummies or Excel 2007 Power Programming with VBA.  I have listed a number of recommended titles on our Excel Books page.

With a little knowledge in hand, I recommend visiting the sites that I have learned to trust as listed on our Websites (Excel) page.  Each of these sites present powerful solutions to many common problems.  They also offer full explanations and practical examples that you can use in your own spreadsheets.

Excel is best learned by doing.  Having an application to work on and exploring solutions that may be available to you can be a most rewarding experience.  This is especially true when they can be implemented and put to good use with immediate results.

Getting to Lean

Obviously all of this learning isn’t going to happen over night, however, you will be surprised how much can be learned in a relatively short period of time.  The time saved by creating a more efficient solution in many cases will offset much of the time spent learning.

The initial challenge is to determine what spreadsheets solutions are in existence and why.  The number may surprise you.  If your company has an IT department, I would task them with this assignment.

As demonstrated by the case study presented earlier, the hours saved can be significant.  The solution I created offers the added advantage of being able to refresh the production schedule at any time, in real-time.  In other words, it is now an everyday event, not just a “Monday” event.

Your skills will improve over time and with each application.  As you increase your learning, old solutions can be upgraded to reflect even more functionality adding even more value.  The more comfortable you become with Excel, the more useful it will be as a tool for data analysis in other areas of the company, including lean initiatives.

I continue to be surprised by the quality of solutions that some companies have created simply because they took the time to understand and learn more about Excel for themselves.  In some cases companies have hired outside support to help them in the process.

There are pros and cons for how spreadsheet solutions are developed and you should consider them as you look to develop your own solutions.  Of course you can always be the Excel Hero by taking the time to learn Excel for yourself.  The added benefit is how much more you may learn about other processes in your company.  Finally, you may just be surprised how in demand these skills are.

Speaking of hero’s, I recently added ExcelHero.com to our list of trusted sites and to Daniel Ferry’s credit, it also inspired the title of this post.

Until Next Time – STAY lean!

Vergence Analytics

Twitter:  @Versalytics