The recent detailing of “Spreadsheet Worst Practices” on CFO.com clearly tapped deep-seated emotions among our readers, who shared their reactions in dozens of comments and E-mail messages and offered their own pet peeves from the world of finance.
Today we present a “best of the worst” selection from readers, from formatting faux pas to basic ignorance of good spreadsheet mechanics. The authors of our original article agreed to provide commentary on reader observations, and to suggest some possible corrections. Shahid Ansari is a professor of management accounting at Babson College, while Richard Block is a Babson adjunct professor of management accounting, as well a CFO Leadership partner at Tatum LLC. Also adding her thoughts is accounting professor Janice Bell, who holds the Weiner Family Term Chair at Babson.
“We highlighted six major areas where spreadsheet problems arise,” said Block with a laugh as he reviewed the reader contributions. “Guess what: There are many more.”
We continue to welcome feedback of all kinds: stay tuned for our next article, based on reader tips for better spreadsheet use. —The Editors
Printing Pratfalls & Formatting Flops
The worst practice that I see every day is sending spreadsheets out to people and not setting up the sheet to print. I received a simple spreadsheet today that, when printed, came out as a three-page portrait, when it should have been a one-page landscape. So after printing the garbage, I had to take time to re-format the entire print function.
I require that my staff format every spreadsheet before it’s sent out. I tell them to assume I am forwarding it to the president, and he will just print it.
It is a challenge because no other department operates this way, and I end up wasting countless hours to be able to print reports that are readable. Nearly every new college grad I hire has no idea how to actually create a spreadsheet that is ready to publish! I have a million examples I could forward you. —Dale Hosack
One of the most annoying issues is the numbers not being in a common format, which makes the reading of numbers on the screen or on a printout very difficult. The numbers can be formatted to a single decimal or no decimals, but if there is no format and there is a table to read through, it’s an annoying situation. —Prashant Agarwal
Comments from Richard Block: The lack of printability is far too common in the world of spreadsheets, and will be added to our growing list of worst practices.
Your comments viscerally touched me, Dale and Prashant. I teach a graduate-school evening class in which the students submit spreadsheets as answers to case studies. I give no specific instructions on labeling, formatting, or other submission requirements, assuming I will receive spreadsheet answers that are well formatted, easy to read, and easy to grade. Yet, even from these mature graduate students, holding significant corporate positions, I receive spreadsheets with tons of numbers in a variety of fonts, in columns and rows that are without labels, in multiple tabs without names other than the default names, such as Sheet 1 and Sheet 2, and with a non-descript file name that forces me to rename the file in the student’s name as I download it.
This fall, I am preparing a set of spreadsheet requirements to reverse this trend.
In addition to properly naming the spreadsheet file, giving each tab a descriptive header and footer, ensuring descriptive labels are placed on key rows and columns, the two additional best practices here are a) to print/preview the spreadsheet before sending it, adjusting page breaks so it paginates well, and b) to ensure that column and row headers appear on each page if the tab is to print over multiple pages. Getting column and rows to appear on multiple pages is performed in “Page Layout/Print Titles” commands on the top of an Excel spreadsheet.
I have seen a couple of users writing long [comments], broken over many cells. When you re-size or copy over, many text characters get dropped . . . Perhaps it is a popular form in some companies’ cultures, because many such users [I observed] were from the same company. —Raj K. Bhutani
From Richard Block: There is nothing more annoying, Raj, than dealing with a long comment typed into a spreadsheet cell. It can have twin negative effects: Comments often block data in adjoining cells, and also may cause printing or pagination problems.
The best practice for adding comments to a spreadsheet is either to label well the specific columns or rows being described, or to add a comment using the “Review/New Comment” command on the top of the Excel spreadsheet. Once a comment is added, a small triangle will appear in the right top corner of the cell. The comment will only be visible when the cursor moves over the cell.
From Janice Bell: It is common to want to provide comments on items included in your spreadsheet. But placing comments in the rows below the calculation area, or in a column beside the cell that requires explanation, can cause the information to spill across many columns, because Excel isn’t a word processor and doesn’t wrap the text. (You can wrap the text by going to the Format command tab, select Format Cells, Alignment, and click Wrap Text.)
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
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
• 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.
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
From Richard Block: I agree with your first comments 100 percent. Hard-coding numbers and formulas into cells is a formula for disaster.
You also bring up a good suggestion for when spreadsheets are used to receive and report monthly data. Downloading monthly data from another system is common. What isn’t common is downloading the data in a consistent format, or storing it in a separate, well-designed tab within the spreadsheet. (Say you want a tab with 12 months of data so reporting and analysis can be performed.) Most ERP systems allow you to do this. For users at smaller companies, when downloading monthly from QuickBooks to Excel, for example, a new tab within the the existing spreadsheet automatically will be created. Once downloaded, the monthly data can be copied into a monthly column, so by the end of the year all 12 months of data can exist in one tab. If all the data is now formatted consistently, then using Excel’s lookup features (vertical or horizontal lookups) can be an efficient way to report or analyze monthly trends.
The worst overall use of a spreadsheet I have seen is when a new college graduate is asked to add two numbers together. Instead of using a ten-key, they spend three minutes on the task using a spreadsheet. —Phil Medler
From Richard Block: Phil, I laughed out loud when I read your comment. At least the new grad knew that you could add two numbers within a spreadsheet. I once asked a new director of marketing for a budget. A week later, after no budget was submitted, she reluctantly admitted that the delay was caused by her not knowing how to use a spreadsheet. Feeling a bit guilty, I created a template for her, labeling the columns in months and quarters, and the rows with specific marketing expenditure categories. When another week went by without a budget submission, I went to find the cause of that delay. I observed her typing numbers into the January, February, and March columns, and then using a calculator to add up the first quarter results in the Q1 column. When I explained how the spreadsheet could do this, she looked at me as if I had performed a great magic trick.
Final Note from Shahid Ansari: In my class, I just finished teaching a case in which my students had to come up with a new production plan, and to try several other what-if assumptions, including labor mix, increase in quality, and decrease in inventory. All were graduate students who work for major U.S. corporations and have at least 12 years of work experience.
It became painfully obvious that the team members I asked to lead the discussion had followed all the worst spreadsheet practices in our first article. They had numbers in the formulae; they had fonts and labels that were hard to follow; there were no data sheet links to allow what-ifs, and on and on.
I stopped the class and asked everyone to go the CFO.com site and read the article.
Obviously, there is something about spreadsheets that tempts everyone to be lazy. And that suggests one more tip for spreadsheet users: To go fast you must go slow.
The time spent organizing the data and assumptions pays off when nonfinancial managers ask for the profit implications of changing the labor mix, or reducing inventory, or increasing receivable turnover.