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
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
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
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
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.
Tip: Test It Yourself
How many people actually test their spreadsheets? With the formulas hidden, it’s so easy to introduce a mistake and not even know it. With calculations spanning data on multiple worksheets, there are lots of moving parts and subtle linkages that are not apparent to the naked eye.
Case in point: we received a multi-page, long-range planning worksheet from a [venture capitalist.] In using it, I found and debugged errors introduced when the spreadsheet was passed on to someone else to “enhance.” This is a common pitfall with complex spreadsheets: expecting the next person updating it to understand the thinking of the creator.
Many of us, myself included, use spreadsheets in lieu of IT support. Why? Their lengthy “process,” time investment, control of product, and ability to make changes on the fly. In reality, some spreadsheets probably should be replaced by programs (using the spreadsheet as a requirements statement.)
My real point: we need to validate the results in our worksheets — applying common sense and comparing to our expectations, and making sure it is fit for purpose. This is part of the IT overhead that we need to adopt. Think about this: spreadsheets are, hands down, the most widely used analytic tool for business. Scary, because they are the least controlled. —Michelle Wallace
From Janice Bell: When I create a complex spreadsheet, I test it by using numbers that should be reasonable, and then calculate the results by hand to compare to the spreadsheet results. If it works, I then generate data that should be out of the range of normal inputs, for instance, negative inventory, to see how the spreadsheet handles exceptions.
From Richard Block: Michelle, you are spot on. The key is to validate the results of a spreadsheet you create by inputting sample data to confirm the results you have predetermined to be correct. Jan’s suggestion further emphasizes this point by inputting data that knowingly will produce a “bad” result to test whether or how the spreadsheet logic will handle “bad data.” These steps are critical to appropriately developing a spreadsheet.
Trap: When Rows Don’t Foot
I have found the most basic, often-forgotten rule in building spreadsheets — the ones populated with complex formulas — is the cross check. It is very easy to get deep with your lovely data design, only to find the rows and columns do not foot. This is so common that I now refuse to review spreadsheet work where the footing cell is not visible. I no longer waste time on work that is fatally flawed. —Patricia Chapman
From Janice Bell: We’ve commented on this before, but it bears repeating.
From Richard Block: Ditto!
Tip: Publish Your Standards
If some advantages of spreadsheets are flexibility, and ability to personalize, they can become a disadvantage if one is preparing a spreadsheet that must meet certain conventions and/or legal requirements. Common sense should dictate a minimum of structure in the preparation of spreadsheets. But unfortunately, even when you have official structure as a requirement, you will find significant deviations.
One solution is to publish a set of standards with detailed structure and requirements for the company. I have striven all my professional career to encourage this, but have encountered strong opposition.
One saving grace is that under Sarbanes-Oxley IT documentation, including spreadsheets, can com under scrutiny, particularly if they provide key information, such as calculation estimates—to mention one that may have material effects on financial statements. Perhaps this will be the impetus to better spreadsheet design and preparation. —Eduardo Casas
From Richard Block: Eduardo, don’t let the masses keep you from publishing your spreadsheet standards, assumptions, and other documentation. Spreadsheets are too often used as critical parts of the accounting and financial reporting process. Thus, they should designed to be able to sustain the same critical scrutiny and audit review that is applied to all other accounting and operational processes as part of a Sarbanes-Oxley internal controls review. Sadly, most spreadsheets do not pass muster. So tell your “strong opposition” that unless they don’t want their area to pass an audit, they should listen to you…and allow you to document profusely.
Trap: Where Did We Leave Off?
In Lotus123 and Excel “.xls” workbooks, the file opens at the point where it was last saved. If you want the recipient to see the conclusion or summary or some pertinent point or data, save the workbook so it opens to the appropriate worksheet, at the proper point. (I hate searching for the answer over numerous rows, columns and spreadsheets.)
Also, with over 200 PCs in our office network, and unlimited numbers of vendors, customers, consultants, sales “demos,” etc., I receive spreadsheets using various software and software versions. All are not compatible with each other. You cannot assume that recipients have the same version as you, or can open any other version. Find out what format is appropriate. The standard “.xls” file can be opened by the new Excel version and Lotus123. The new Excel “.xlsx” file cannot be opened by either Lotus123 or previous versions of Excel. —Ronny Reed
From Richard Block. Ronny, you bring up a great point — and one that exposes major frustration when creating and sending spreadsheets during that long period when Microsoft, et. al., update their software. I cannot tell you how often I’ve had to ask senders of spreadsheets formats in the .xlsx convention using Office 2007 to resend them in the .xls convention because I had only Office 2003. Yes, it would be polite for new Operating System users to be more considerate of users who might not have that latest version. But just as there is no accounting for good taste, one cannot legislate politeness and thoughtfulness.
Tip: An Alternative to the Negative Sign
I loved the major improvements Richard Block made to the before-and-after worksheet. But I prefer to format any negative numbers or percents into brackets, instead of showing that little bitty negative sign before the number.
I believe the brackets are much easier to see, and highlight something that is “bad news,” and possibly should be addressed. While I’m on the subject of percents, I also would have formatted the percent calculations to be carried out at least one decimal place. In any case, with or without the decimal place, I would have written the formula so that the final result is either rounded up or down using the “round” function. —Thomas Mroz
From Richard Block: Thomas, I agree with your comments. I too prefer to format negative numbers using brackets instead of the negative sign, so I’m sorry that I didn’t do that in my previous example. Brackets are much easier to see. When I am formatting numbers in a difference column, such as “Actual vs. Budget,” I use the B/[W] or F/[U] to ensure that the reader knows that brackets (or parentheses) are going to indicate a negative or unfavorable difference. While I use the round function often, I sometimes set it to one decimal point when calculating percentages as it sometimes takes tenths of a percent to highlight a difference that matters.
Trap: Dressed Up, but Nowhere to Go
My pet peeve is receiving a spreadsheet that is all dressed up — i.e., formatted correctly with macros, formulas tied together nicely, etc. — yet the one who produced it cannot produce the data behind the spreadsheet. Without the data such a spreadsheet is nothing but window dressing for bad data. I see it happen all the time. They believe a spreadsheet that looks and performs well compensates for questionable or no data. No matter how easy to use, read, interrupt a spreadsheet is, if the core assumptions and data cannot be provided, then it is just a pretty trash can, GIGO. —Rod Ferrara
From Janice Bell: This reminds me of an experience years ago when I was on a strategic planning committee, making decisions that would impact operations of a college for years to come. Committee members were provided “data” in a spreadsheet with nice charts that showed program ratings offered by students, alumni, employers, and faculty. The faculty ratings were quite surprising given my knowledge as a “person on the street.” When I asked to see the data behind the chart I was provided raw survey forms, untabulated in any way. I tried to compare the raw data to the spreadsheet inputs, and found that the spreadsheet data was in summary and not the raw data I’d been provided. I then tried to recreate the summary data and found that the data in the spreadsheet had not been created from the raw data after all; it had been changed by a small group who didn’t like the original results. This was truly a case of GIGO, but everyone had been very impressed because of the “authority” of the spreadsheet.