• Technology
  • CFO.com | US

Sloppier Spreadsheets: How Bad Can They Get?

Readers weigh in again on Excel sins and solutions. Tips include totaling at the top, while traps like poor version control can even threaten to force restatements.

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!)

From Janice Bell: I completely agree, and would like to add that “version control” of spreadsheets is a major nightmare when work has to be done quickly by several people in an organization. I not only use a file name that contains the date and my initials, but I also try to include that in a header on the printed copy.

From Richard Block: Spreadsheet version control is critical, and when a spreadsheet is shared by placing it on a common or shared network drive at work, lack of critical controls for use and updating can spell disaster for users and the company. Disasters such as delayed or restated 10-Q and 10-K filings can happen if shared spreadsheets aren’t managed, protected, and controlled properly. And they often are not.

Discuss

Your email address will not be published. Required fields are marked *