Sloppy Spreadsheets: Readers Speak Out

If I am sending a spreadsheet to someone who will print the spreadsheet for reproduction, I try to follow an "endnote" procedure. In the title to the spreadsheet or in each column or row where I have a comment, I place numbers corresponding to an endnote such as (1). I then use a separate sheet for all my endnotes, and label the sheet name, "Endnotes." I type in this sheet much like a word processor. Since I cannot spell, I copy the material that I type there and paste it to Microsoft Word, make corrections and then recopy to this section.

Editor's Note: To illustrate some of the worst-practices in these reader suggestions, Richard Block devised this before-and-after treatment of financial information detailing sales growth and projected sales growth for three products to two customers. In Tab 1, (Customer Production Sales Analysis A) column headings and tab headings are not present; comments are unclear, cumbersome, poorly formatted, and run off the visible page; and when printed are even worse. In Tab 2 (Customer Production Sales Analysis B), column headings and tab headings are present; the information is crisp and easy to grasp; and intermediary computations help the reader follow development of the data, so comments can be focused. The printing is easy, with Tab 2 already properly formatted, and with an appropriate header and footer.

Heading Horrors & Labeling Lapses

To CFO.com:

Even before getting to the spreadsheet construction and calculations, I've encountered more people than I ever expected who seem to forget to put a heading on a spreadsheet that is to be distributed and to include a title that is at least somewhat descriptive of the purpose of the spreadsheet. This has included both new staff and experienced professionals who just developed sloppy practices in their previous assignments.

For some reason, they assume that other readers will automatically understand the purpose and that if they have to refer to the spreadsheet again next week, next month, or next year, that they will also remember the purpose. —Richard Archer

To CFO.com:

• Spreadsheets that do not include file location and date. Spreadsheets should include footers showing file location, date, tab name, file name, and page #x of #y if appropriate.

• Spreadsheets with multiple tabs and no summary tab. Spreadsheets should include a summary tab that directs the user's attention, and worksheets should be clearly named and flow from left to right.

• [Misuse of] hidden columns/rows and hidden sheets. These can contain confidential information that should not be sent to certain parties. Users may not realize that there are hidden columns/rows containing important information; columns and rows should be grouped, not hidden. —Bill Myer

From Richard Block: Again, I am viscerally affected by this comment. Proper spreadsheet file names, and tab headers, footers, and tab labels are critical to ensure documentation is complete and the recipient knows what is being sent. However, your comment on sending spreadsheets with hidden columns, rows or tabs deserves special note. I'll call this worst practice "the Ostrich Effect." Sometimes column and/or row data is hidden for ease of presentation. But sometimes data is hidden because it is confidential. When dealing with confidential data, the best practice is not to send spreadsheets containing such information hidden or otherwise.

When data is hidden for ease of printability or presentation purposes, a best practice is to protect the cell with a password. Excel path: Home/Format Cell/Protect Sheet). The recipient will not be able to inadvertently unhide the data, and can only view the visible portions of it.

From Janice Bell: I was once on a committee (not at my current employer) that was to analyze the way merit pay was awarded, and to make a report to all employees. Obviously, employees' names, social security numbers, and exact compensation were confidential. Human Resources provided my committee with a spreadsheet containing employee names and social security numbers hidden, but not protected. The information was transmitted all across the organization. A firestorm followed, but the damage was done.

Miscellaneous Miscues

To CFO.com:

In a nutshell, [a big problem is] hard-coding numbers in formulas that should be assumptions. The only time you should do this is if a) you don't have a calculator other than Excel, or b) you or someone else will never be using the spreadsheet again.

Some people try to "fix" the problem by adding comments to cells. This helps a little, but still doesn't allow for the changing of assumptions. When creating a spreadsheet plan to have all figures used in the calculation as clearly labeled assumptions. A second common inefficiency is having to type in new amounts monthly into a spreadsheet. Spend 30 minutes learning the lookup function and then dump financial data out of your ERP onto a data tab. —Paul Marvin

• « previous
• 1
• 2
• 3
• next »