Exit Excel Hell - Part 2

In my previous 'Exit Excel Hell - Part 1' blog I started talking about how sending all those Excel attachments is a misuse of the email system. Other problems also exist. When you ask staff to update a spreadsheet and email it back to you, how do you incorporate multiple staff input changes? Doing that manually just creates an opening for errors. You might be surprised at the number of copies of a given spreadsheet in an organization. Which is the most recent and correct version? And, what happens to a spreadsheet that is used annually and is dusted off each year: does the creator and/or user relearn the contents and how it is used? Old spreadsheets are breeding grounds for errors because people will automatically assume it was correct. Or for that matter, if a user builds onto a spreadsheet that someone else developed, they often have to reverse engineer it to truly understand it. Furthermore, as Excel users become more sophisticated, their spreadsheets get more complicated and they start using external links and hidden references creating even more risk.

 

Well, how can this proliferation of spreadsheets be controlled and what other controls can be used for spreadsheets?

My first preference is, obviously, not to use a spreadsheet. But if you must, and if it is to be shared by multiple staff, it should be in a repository (or an Electronic Document Management System) where only one person can check the spreadsheet in or out to modify it at a given time.

Another control feature you can use is to put a password on a spreadsheet or part of a spreadsheet so someone can change or access only certain cells. In addition, important and complex spreadsheets should be documented. How would you document a spreadsheet?

There are several ways. The first is to use named cells wherever practical. For example, when calculating revenue, the most popular way is to multiply two cells — for example, F12=N12*P12. But why not define columns N & P and name them Cost and Quantity so column F becomes Revenue=Cost*Quantity. The formula itself becomes more descriptive and understandable to anyone looking at the spreadsheet. You can also insert comments in the cells to help explain the reason for your calculation or process. You can embed instructions right into the cells themselves, or create an external documentation manual, or even create a spreadsheet tab call 'Instructions'. You could maintain a revision history in which each person who makes a change to the spreadsheet logs what they did and when.

Some companies, when they have important spreadsheets, will follow a structured methodology similar to developing software. Some call it the Software Development Life Cycle (SDLC) which includes user requirements, design, development, testing, training, and so on. But unfortunately not many Excel users have formal SDLC training and they usually develop a spreadsheet in more of an ad-hoc fashion.

When you have an important memo or communication to send to a lot of people do you have someone else look at it before it is sent out? I do. I want someone else to give it a once-over. I want to make sure the message I’m trying to communicate is free of grammar and spelling errors, but also I want see how someone else interprets my message.

That same concept is exactly what should happen with Excel spreadsheets. Someone, other than the creator, should be auditing and verifying the spreadsheet. It’s too easy for those of us who create the spreadsheet to become so close to give it a truly objective look. You’re unlikely to find all your own mistakes. This is even more important for monthly, quarterly or year-end financial statements which are so often rushed.

Ideally, if it is a financial report, have the report come directly from the financial system. With the flexibility and options in report writing these days, you can even get a nicely formatted report. If the report, which can be run anytime, comes from a single official corporate source, there is far less chance of error than re-entering the summary data into some spreadsheet report.  If you must use Excel for financial reporting or some other corporate reporting function, consider having your IT department develop scripts to automatically query the corporate database and deposit the data in the spreadsheet (Excel can do SQL queries to a database). This will at least avoid transcription data entry errors. 

Spreadsheets provide a great analyzing tool. Personally I think spreadsheets are great for individuals to do their job, but I try to categorize spreadsheets two ways. It’s either for personal job working purposes or for corporate use (i.e. many staff use it). By that I mean when that person leaves the company, their files, and I refer to Excel files in this instance, should be thrown out, deleted along with all the other files in his/her data directory. If it is a corporate Excel file that should be kept, it should be documented and kept in a location other than the employee’s data directory.

Any file that is used by more than one staff member should be in a department or corporate directory or an Enterprise Document Management System (EDMS) or an Enterprise Content Management System. When a person leaves, the supervisor or manager can quickly look at files in that employee’s data directory to see if any might be useful to the next incumbent in that role, but for the most part, good file management procedures should include deleting the personal directory of staff members when they leave the organization.

Enterprise Content Management System, or ECM as it is commonly referred to, is a formal way of storing and organizing corporate documents or other content in a central repository. I will tackle ECM in another blog.