Sloppier Spreadsheets: How Bad Can They Get?

In June, CFO.com followed its popular article Spreadsheet "Worst Practices" with a collection of reader feedback we called Sloppy Spreadsheets: Readers Speak Up. That second column, with annotations by our original writers, drew almost as much interest.

Today we offer another sequel based on reader response, in the form of tips that you have developed for spreadsheet readers, and traps that continue to trip up even savvy users.

As before, our spreadsheet sages from Babson College comment on these reader reactions, with thoughts that can enhance some of the tips, and can render some of the traps avoidable. The experts are accounting professor Janice Bell, who holds the Weiner Family Term Chair at Babson, and Richard Block, an adjunct professor of management accounting, who also is a CFO Leadership partner at Tatum LLC.

Tip: Color-coded Tabs
To CFO.com:
I wanted to emphasize the horror of receiving a 20-tab or greater spreadsheet that does not delineate which tabs I, as the reader, should focus on. It is okay to send the data and support information that is used to prepare the information, but not okay to fail to note which worksheets I should focus on.

What I do is color code the tabs as green or red, etc., telling the reader which are the tabs that are printer friendly, and which should be read, with the rest being "backup" or support material. —William Nicolai

Comment from Richard Block: This is a great tip, drawing attention to a practice that is too infrequently used. I use tab color-coding also as a way of documentation. A large spreadsheet (as you suggest with 20 or more tabs) often is multi-purpose or has multiple sections. A few are typically used for data; a few are used to analyze that data; a few present results and reports; and finally, a few involve recommendations. Making each section a different color just makes navigating the spreadsheet easier.

Trap: Not Deleting Data
To CFO.com:
My pet peeve is spreadsheets that are over one meg in size. Most contain a few pages of information, but at one time were very large. For one reason or another, through editing the data is reduced to a page or two of information, but the author never properly deletes the unneeded data. In companies (like mine) where you inbox size is limited, a few giant spreadsheets can have you cleaning up E-mail before reading/analyzing the spreadsheet. —Jerry Ballanco

Comment from Janice Bell: I totally agree with this point. I find that I often receive spreadsheets that are much larger than they need to be because they contain all the raw data the previous person analyzed. But, let's not stop there; I'd like to tell readers to consider their recipient's inbox size and not send cards with animations and sound!

From Richard Block: One has to ask, however, why is it necessary to send a spreadsheet with > 1mb of data in it? Once the results of a large spreadsheet have been achieved, and those results cannot be sent in writing via email, the polite thing to do is (a) make a copy of the entire spreadsheet, then (b) copy and paste special/values for all the tabs that are summary- or presentation-ready. This step removes all the links from the cells in the presentation ready area to the analysis and data from other tabs. And finally, (c) delete all other tabs from the spreadsheet copy. This final step eliminates the tons of data that made the spreadsheet large to begin with.

Tip: Totals at the Top
To CFO.com:
Totals for a spreadsheet should be at the top, for both readability and ease of updating if you need to add additional items at the bottom. I prefer to have the summary tab first, just like in a document where the executive summary is one of the first pages. —T.J. Reese

From Janice Bell: I agree with you that putting totals at the top should draw the reader's attention to the data. However, I recently presented data this way in an article that was being published, and the editor changed the location to the end of the column.

From Richard Block: Totals on the top of columns do help the spreadsheet preparer. They save a ton of time compared to constantly scrolling to the bottom of long columns of data. As Jan states, totals on the top often don't make good presentation viewing. This is a good tip for the user, but for the reader of a spreadsheet, the bottom line is best kept at the bottom.

Traps: Overprotecting, and Not Noting Revisions
To CFO.com:
I understand that formats need to be protected when using the spreadsheet to upload into other systems. But leave the rest open so it can be used to populate the required cells.

And when sharing, senders often use a link to another spreadsheet that the recipient doesn't have.

And what about a co-user returning the spreadsheet with no indication of having updated it, and no name change? A "revision1" addition to the name would help. —Richard Richer (Babson '73 '74, from before there were spreadsheets!)

• 1
• 2
• 3
• next »