Software

Design timesaving Excel sheets with growth in mind

Are your users searching two or more Excel sheets to compile information? Or worse, are they filing through endless paper records to find what they need? Design an Excel solution that saves time and makes it easy to add additional data sources.


Recently, a small business owner asked me to help design a computer-based solution to replace a paper-and-computer-based process in his office. "And I'd like to keep the cost low," he said. (I hate it when clients say that, but that's another story.)

I went on-site and met with the employees involved in the process that needed automating. I ended up selling a solution that the client liked for two reasons: It came in under his stated budget, and it's designed so that his employees can easily make enhancements in the future.

The not-so-handy manual process
The client manages information about unemployment claims for a variety of employers. In essence, here's what happened under the old system:
  1. A claim was received in the office.
  2. The business analyst handwrote an employee identification number (EIN) on a form that contained spaces for a dozen or so other pieces of information.
  3. The business analyst went to the computer room, opened an Excel sheet with 35,000 or so rows of data, pressed [Ctrl]F, and searched for the EIN in question. (Not all employees know about [Ctrl]F. Some of them drag the scroll button or press [PageDown] and manually navigate the sorted list.)
  4. Assuming the EIN was found, the business analyst copied by hand information from the screen onto the paper form.
  5. One of the fields that got copied was a six-letter location code. The business analyst fetched a 10-page, tattered printout, looked up the six-letter location code, and copied by hand contact information onto the paper form.
  6. Finally, the business analyst got to do the work he or she was being paid to do—namely, to analyze the claim, make some calls, take some notes, and make a recommendation.

The type-and-click solution and its effect on morale
The flow chart for the new process looks like this:
  1. A claim arrives in the office.
  2. The business analyst clicks on a shortcut to the Excel spreadsheet, now located on a network drive.
  3. The analyst types the EIN, clicks a button, and the completed call sheet comes out of the printer almost immediately.

The client initially measured the value of this new process for his business in the number of minutes per day saved by each analyst. The formula is straightforward:
(No. of Analysts) * (No. of Claims Handled Per Day by each Analyst) * (No. of Minutes it used to take to Fill Out the Form Manually).

Besides being a major time-saver, another benefit of the new process was the effect on the morale of the business analysts. "You mean I don't have to look up the stuff for the form anymore?" they'd ask. When completed forms started popping out of the printer, they were ecstatic.

The Excel design
The thing that befuddled the client was the fact that his people needed to pull data from two sources, a spreadsheet and a printed list of locations, in order to start processing a claim. The solution I delivered involved a single Excel notebook with three sheets, with their tabs labeled FromMainframe, OurLocations, and OurForm.
  • In the FromMainframe sheet, we copied and pasted the 35,000 rows of raw data that the business analysts previously searched manually. A new file is generated each month with the data already sorted in the appropriate order. All the client has to do is delete the records in the FromMainframe sheet and copy and paste the data from the new file.
  • In the OurLocations sheet, we entered all of the information contained on the 10-page printout that the business analysts had been using to look up location codes. In the past, when changes were made to the names, telephone numbers, and addresses on these sheets, the only way to disseminate the updated list was by photocopy and interoffice mail. Under the new system, the business analysts don't even need a printed copy of the list—everything they need comes out on the custom form. And if anyone wants to print a copy of the list of locations, it's readily available.
  • In the OurForm sheet, we re-created the look of the infamous original paper form. When the business analysts open the worksheet, however, the only piece of information they need to enter is the EIN. At that point, strategically placed VLOOKUPs return a set of fields from the FromMainframe sheet that includes the six-character location code. Other VLOOKUP functions use that six-character code to return information from the OurLocations worksheet.

After a quick review to make sure they entered the correct EIN, the business analysts click the Print This Form button I created for them. The macro behind the button contains a single line of code:
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

The client didn't want the Print Me button itself to appear on the printout. To suppress it, I right-clicked on the button, went to Format Picture | Properties and deselected the checkbox labeled Print Object.

Room for growth
The client tried to create his own Excel solution. However, like many inexperienced spreadsheet designers, he wasn't aware of all of his options. For instance, when I proposed storing the mainframe data and the location data in the same Excel notebook, he commented that he didn't know you could use VLOOKUPs in one sheet that referred to a range in another sheet. He assumed he'd have to store the 35,000-row database and his form in the same sheet.

The trick I shared is using the name of the sheet, instead of a named range, as the VLOOKUP's data source. (That way, the client can add rows of new data without worrying about updating the range.) For example, here's one of the formulas in which I used VLOOKUP to pull data from another sheet: =VLOOKUP($A7,OurLocations,11,FALSE).

In the future, when the client decides that he needs to pull information from a third source to fill in a blank on his claim form, he probably won't have to call me in. All he has to do is insert a new sheet, copy the new data in the sheet, and add some new VLOOKUPs to his form.

Share your favorite paper-to-Excel solution
How do you help clients who are stuck in the paper-system rut? Post your comments below or write to Jeff.

 

Editor's Picks