cfo.com

cfo tagline

Sloppy Spreadsheets: Readers Speak Out

Readers make some pointed additions to CFO.com's "worst practices" list.

CFO.com Staff, CFO.com | US
June 18, 2008

Spreadsheet hell - the NOW function

Please tell them not to use the NOW function to indicate the date at which the spreadsheet report was completed. The function's value changes every day.

Posted by Alecos Papadopoulos | October 07, 2008 05:33 pm

Has anyone tried Distributed Spreadsheet?

The problems with sending out workbooks containing lots of sensitive data could be solved I believe. Each recipient needs a copy of the software as does the sender. Quite versatile.

Posted by Mark Ryan | September 26, 2008 12:17 am

Why print a workbook at all?

Full comprehension of a workbook, even a single-sheet income statement, requires some tracing of the links in various cell formulae. This can not be done by looking at a print out. In addition, comments and suggested changes are much harder to manage when dealing with hard copy.

Posted by Batara Siagian | July 15, 2008 10:50 pm

Less common, big problems

Lots of good everyday observations here. I will throw in a few that are less frequently seen but drive people crazy when they do happen. First, macros should never be included in a spreadsheet unless all potential recipients are expecting them and know what to do with them. Many users will not know what to do when they receive the macro warning upon opening a file. Some may not have their security settings set so that they can enable macros on opening the file and have no idea how to change those settings. For general purpose spreadsheets (as opposed to spreadsheet-based applications), Print macros should be relied on only as a last resort. Keep in mind that users will get a macro warning even if there are no actual macros present if you added a code module to the spreadsheet (for instance if you accidentally hit "Record Macro" and then shut down the recorder). Get rid of the module by deleting it in the Visual Basic Editor (Alt-F11). Second, many useful formulae can only be used if the AnalysisToolPak and AnalysisToolPak-VBA Add-ins are installed. Many people do not have these installed nor do they know how to do so. Any spreadsheet that uses add-ins should explicitly say so in cell A1 of the first tab and contain instructions on how to activate them. Needless to say, cell A1 of the first tab should be selected when the file is saved so that cell is selected when the user opens it. Third, presentation layers using constructs like VLOOKUP and OFFSET (my favorite) and descriptive range names are great, but keep in mind that many people have no idea what they do and will be annoyed that the can't understand the logic of the spreadsheet. Extensive commenting may help, but don't use an esoteric tool when a more universally familiar one will do.

Posted by Reid Curley | July 09, 2008 11:38 am

Hate unformatted Excel spreadsheets

I work a lot with Excel spreadsheets. What is also very important is the understandability of Excel spreadsheets. I hate when clients or anybody from my company sends Excel without formulas, that do not show how something has been calculate forcing me to spend additional time to understand or reperform their work. The other thing is luck of appropriate formatting and comments. I really don't understand whether people do not ask themselves a question how this sheet looks like? Where do they learn such poor presentations? The Excel spreadsheets should be more readible than Power Point presentation as they have to be in many cases self explanatory.

Posted by Tomasz Grycner | June 29, 2008 02:15 pm

Eye Opener - Spreadsheets

Its really an eye opener while going through the article. There are a few basic things which every executive should understand before working on any spreadsheet. - Assume that the spreadshees is being submitted to the President of the Company. - Assume that the users of ths spreadsheet are all layman. - Assume that they have very less time to understand and act upon the data submitted in the spreadsheet. Taking these into consideration, the moment you open a spreadsheet, follow these simple points: - Resize the view (from standard 100% to 85%) if you are going to present a financial data. - Select a standard font style and size (Arial is considered the best for Financial data) - Select the decimal places you want to show your data in ( one or two ) - Just decide upon a draft/template to present the data - Provide the 3 layered heading to the sheet ( Department name, Subject of the worksheet, applicable Time period ) - Provide sufficient spacing between the data and use borders for effective presentation - use appropriate sub heads - Now set up the print area and get a print copy Now have a look at the print copy and try to question " is it covering all the three assumptions stated above ", If you are convinced, it is the best you can do.

Posted by Hitesh Verma | June 27, 2008 02:54 am

No Print Area?

I hate it when I format a spreadsheet for printing perfectly, then the recipient changes column widths then prints it and stickytapes the sheets together and all that~ (whilst they're being so crafty, why not throw in some playdough for modelling!) In Excel 2003, Cust Prod Sales Analysis B has Epsilon Comments spilling over the page. To make it robust, it should really have a Print Area set (select the print range then File, Set Print Area). Then Page Setup, click the "Fit to" 1 Page Wide and delete the 1 in 1 page tall.(so it can be several pages long, but only 1 wide) Now it's safe for consumption!

Posted by Sam Rayfield | June 26, 2008 08:36 pm

More hidden info

Doing a print preview will also catch any confidential info hidden in headers and footers, say -- hypothetically, of course -- the previous client's name. Another source of embarrassment is the Properties feature (in 2007, Prepare > Properties), where you may find such delicacies as the original creator of the spreadsheet, who may in fact work for a competitor now.

Posted by Janelle Montgomery | June 26, 2008 04:45 pm

Sloppy Spreadsheet

Pet Peeves: Before a spreadsheet is submitted, it must be formatted to print correctly. We suggest that the employee create macro with a 'button' to automate the print. There it is the "deer in the headlights look". Simple fact: This is YOUR work and it says a great deal about the author. If YOU submit garbage, then it tells your employer something about YOU. They get the message and they usually figure it out (and on their own time). When we work with clients, we find that most of them still use a spreadsheet as if it were an accounting pad. They usually on use simple arithmetic. Very rarely do we see any boolean algebra formulae or lookups, etc. Excel and Quattro are powerful spreadsheets and to use them the way many do is actually a waste of everyone's time. When we do our client presentations on communication, we equate lousy spreadsheets (examples are shown to the client) with lousy powerpoint presentations, you know 10 to 12 point type, 10 bullet points a paragraph each . . . (and the presenter usually reads to PP Slide with their back to the audience). We find it amazing that college grads use WordPerfect (we like WP) and MS Word like a fancy typewriter; Excel & Quattro as an accounting pad and rarely know how to use Powerpoint. However, they do know how to text, IM, surf, etc. Not much more to be said.

Posted by Robert Callard | June 26, 2008 04:02 pm

Spreadsheet Nightmares....

I enjoyed and can relate to the spreadsheet errors that bother people. My pet peeve, relating to spreadsheets, is receiving a spreadsheet that is all dressed up, i.e pivot tables, formatted correctly, macros, formulas tied nicely etc..., yet the one who produced the spreadsheet 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, data, etc... being used to create the spreadsheet cannot be provided then it is just a pretty trash can GIGO.

Posted by ROD FERRARA | June 26, 2008 01:21 pm

sloppy spreadsheet

Seems to me that all the problems are not caused by the spreadsheet, but by the user. For a very good use of spreadsheet, go to www.icpas.org.sg/bizexcel/bizexcel_testimonials.asp One company used spreadsheet very well to solve a major problem. Read it and it may serve as a good standared for all spreadsheet development.

Posted by Kok Tang | June 26, 2008 12:57 pm

Spell check

Spreadsheet issues - misspelling - many individuals including some that are quite proficient at properly utilizing the features of the spreadsheet forget about using the spelling check feature. Nothing says unprofessional as incorrectly spelled words. Personally the comment on copying items into Word, correcting for spelling and copying back to Excel is a waste of time when the spell check feature is one button (F7).

Posted by Amy Crosswait | June 26, 2008 12:47 pm

One more improvement

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 1 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. Overall it was a great article and I learned some pointers from it. Thanks.

Posted by Thomas Mroz | June 26, 2008 12:34 pm

Training??

The "worst practices", focused on spreadsheets as a financial tool, are also rampant in many other areas as well. Consider an application where server storage space, and CPU utilization are output from a monitoring tool. I have seen team members manually moving fields to create an "updated" spreadsheet because they have no idea of the automation tools available. Ultimately, it comes down to training, and in my experience with many clients, training in Microsoft Office products (or similar) is woefully inadequate, addressing the bare essentials, ignoring the solutions to "worst practices". And in the end, it is the managers who are responsible for this situation... they poo-poo training as unnecessary only because their own limited training necessarily puts blinders on their own view of what spreadsheets (and other products) are capable of doing. And it seems that it is the training budget that always gets cut first.

Posted by Wayne Herbert | June 26, 2008 12:12 pm

Business-Critical Spreadsheets

For a first rate white paper on the use of spreadsheets in enterprise environments I would recommend the following paper to be found at www.protiviti.co.uk Spreadsheet Risk Management: Frequently Asked Questions Guide

Posted by Ralph Baxter | June 26, 2008 11:56 am

Spreadsheet worst practice? Printing?

I take strong exception to the idea that failing to make a spreadsheet printable amounts to bad practice. If you design a spreadsheet to be printable, you are basically preparing a typing exercise. No point in having spreadsheet then: might just as well use a word processor instead. The chairman, CFO, CEO, whoever should be reviewing data on screen. That's what it's there for. The computer industry doesn't built massively powerful computers simply to turn them into complicated typewriters. I wonder how much more CFOs will pine for paper (sic) when the Amazonian rainforest is felled completely?

Posted by Martin Thornhill | June 26, 2008 11:46 am

Where am I?

Great article and comments! Two more spreadsheet annoyances: 1) In Louts123 and Excel ".xls" workbooks, the file opens at the point where it was last saved. If you want the recepient to see the conclusion or summary or some pertinent point or data, save the workbook so it opens to the appropriant worksheet, at the proper point. I hate searching for the answer over numerous rows, columns and spreadsheets. 2) 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 compatable with each other. Do not assume your receipient has the same version as you, or can open any other version. Find out what format is appropriate. The standard ".xsl" file can be openened by the new Excel version and Lotus123. The new Excel ".xlsx" file cannot be opened by either.

Posted by Ronny Reed | June 24, 2008 06:19 pm

Testing spreadsheets?

Oh -- another thing -- 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 VC. 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. (NOTE: I'm NOT complaining abpout the VC -- only citing how insidiously problems can creep into spreadsheets. I've done it myself -- repeatedly, and have no stones to throw.) 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. Don't get me wrong -- I do not advocate inserting IT into the process -- I just think we ought to be able to benefit from their lessons learned. After all, how many companies would require exhaustive testing of a new billing SYSTEM, but not a new billing spreadsheet?

Posted by Michelle Wallace | June 20, 2008 11:11 am

Gaining proficiency in tools

I so relate to every one of the comments I have read -- I share the pet peeves. The tools have so many features, that no single person knows how to exploit all of them (even within the vendor company). I remember hearing someone say that most people use only about 3% of the richness of spreadsheets and word processing tools. Should that 3% be our baseline expectation? How do we expect people to learn how to create a useful (underline useful) spreadsheet or word document? They don't come out of school with a consistent approach to using their tools. So, where do they learn it? Trial and error does not work because everyone does not have the same curiosity or time to learn on their own. One on one coaching is ultimately the most expensive and least scalable solution but it is the unexamined norm, I fear. We learn from our coworkers, each with a different expertise in the tools so we learn brute force solutions intermingled with elegant uses of the many capabilities of the tools, making inconsistency the status quo. Shouldn't high schools should teach the basics of spreadsheets and word processing to every student? Both tools are so pervasive in today's business environment that having a baseline understanding is important, even critical to success. Some colleges have started to require tools courses in the freshman year. If you require a student to have a PC, then requiring familiarity with the tools and setting standards for their use makes perfect sense (at least to me). Until that happens, hiring managers will have to devise ways to assess these skills before hiring. Maybe on-boarding programs should address this for a business. Costly, but a way to avoid those unmeasured future costs (see all the comments about reformatting, etc).

Posted by Michelle Wallace | June 20, 2008 10:36 am

Large block of text

To create a paragraph, users often type text which spills to the right in a column of cells. Instead, you can drag a text box from the draw toolbar and type the words in there. I actually do both. The text box is nice if you have to change the wording, because it behaves like a mini-word processor.

Posted by Daniel Moore | June 19, 2008 04:26 pm

Worst spreadsheet practices

A few "worst practices" I have observed. 1) Being sometimes referred to as the "excel guru" I am often asked for help. When I go to their desk a few pet peeves of mine are not freezing panes so you can see the column headings, not knowing how to efficiently navigate a spreadsheet, and not using print titles. In order to help them we end up repeatedly scrolling to the top to see what column we are looking at which also increases the risk of entering data in the wrong column. This also brings me to the next point, not knowing how to quickly navigate a spreadsheet. Many people will either scroll or use the arrow keys moving one cell at a time, occassionally some will do it a page at a time but on large spreadsheets learn how to use the ctrl+arrow keys, and ctrl+end etc. or simply using the mouse to click above the freeze panes line to go back to the top. This applies to selecting ranges too. I also am amazed that people don't use print titles to repeat column headings so they eighter don't print at the top of each page and you constantly have to refer to the first page to know what you are looking at, or they actually copy the headings multiple times throughout the data and then massage the spreadsheet so that it looks like they used print titles. 2)Another worst practice I will blame partially on microsoft for inventing the "Merge Cells" and providing a button for it and not providing a tool button for the "Center Accross Selection" (I have created a simple macro and attached it to a tool button). While the Merge Cells may have limited application, in almost all cases I have seen it could easily be replaced by using the "Center Accross Selection" and other text/cell functions i.e. wrap text. Once you merge cells you cannot insert or delete columns thus making the spreadsheet inflexible and requires unmerging, making your changes, then re-formatting (and possibly re-merging cells) to get back to a consistent presentation. 3)Poor spreadsheet design and inflexible spreadsheets. Most people poorly design a spreadsheet so that it doesn't flow well, is inefficient for data entry if needed (i.e. they should set up a data table and use a pivot table or other form of summarization), and when they need to add a row or column it requires major surgery. Design them so that you can easily add columns/rows. Also, almost everyone ends up needing to make quick adjustments to amounts for various reasons. Build in an adjustments row or column so you can visually see that they have been made instead of editing a cell with a formula and hard-coding an amount for rounding or other adjustment, thus risking perpetuating wrong amounts if you forget to remove it the next time or taking unneeded time to clean it up for the next time. It is also easier to "hardcode" an amount in it's own cell instead of having to go to edit mode and place your curser in the right place or to delete all adjustments at once instead of having to go into each cell to do it.

Posted by John Henderson | June 19, 2008 12:52 pm

Building an accurate Spreadsheet

I have been to numerous Excel courses during my career. They all concentrate for the most part, on the technical side of the spreadsheet and the 'how to' of formatting, formulas, macros,etc. There isn't a course out there today that I have seen offered that teaches how to build and test a spreadsheet to insure it produces accurate results and is ready to share. If someone would design that course, I think there would be a lot of takers. I use the audit tools in Excel a lot, but I still worry that I may have made an error somewhere that would generate incorrect results.

Posted by Judith Sherling | June 19, 2008 12:49 pm

Microsoft, are you listening?

I share many of these peeves and at the same time I'm guilty of not always following the best practices. The ideas expressed herein are so easily addressed by designing software that is easy to use. For example, with Excel, how come I have to click 6 times in order to format a number with no decimals? Adding page headers is even worse. I haven't tried the numerous alternatives -- yet. Hopefully there's a product manager out there who is paying attention to this series!

Posted by Bryan Boroughf | June 19, 2008 12:33 pm

Spreadsheet horrors

Great Article - I wanted to emphasize the horror to receive a 20 tab or greater spreadsheet which does delineat which tabs I, as the reader, should focus on. It is okay to send the data and support information which is used to prepare the information I am destined to read, but not when I am not told 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 which are printer friendly, and which should be read, with the rest being "backup" / support material.

Posted by William Nicolai | June 19, 2008 10:58 am

Best Practice Spreadsheet Modelling Standards

I don't want to harp on here, but many of the issues mentioned in the above article are covered (quite descriptively) in the Best Practice Spreadsheet Modelling Standards which now appear (and are currently the most downloaded) under the whitepaper section on the CFO.com website. The Standards are also free to download at http://www.bpmglobal.com/bpm_standards.html .

Posted by Nicolas Boston | June 19, 2008 03:43 am

Some Other Poor Practices...

One pet peeve I have when using excel as the main "forecasting/budgeting" etc. tool in a company is when the company has different versions of excel running around on peoples computers. For example some companies will start deploying new laptops, with Finance being the last to get them. Once budgeting season comes around the operations and sales team start sending in their updated plans in the newer version of excel and Finance can't read them unless they already dowloaded the reading software for the newer version. Not sure if this is a bad practice or a poor move from Microsoft not making there software more friendly with the older versions. (on the other hand they do a great job with the newer version accepting older version, but this is rarley the problem :-)...) Another worst practice that most companies/individuals do is; links to outside databases. I have worked for some companies who would transfer most data from their ERP into excel. The Excel add-on would use datebase driven links to pull the data so that the person would be able to run the analysis and send it through. Unfortunately if to try to send this outside to a consulting firm or bank they don't have access to your ERP database and if they change or recalculate a cell all data specific cells will REF! out. In order to aviod this make sure that all data points that come from a specific or personal database need to be hard-coded so that the analysis/formulas do not get REF! and lose all value to the person needing the materials.

Posted by Karlo Bustos | June 18, 2008 11:37 pm

Another Format

I believe in the list approach to practically all analyses. It allows for further downstream filtering, sorting, insertion, extraction etc If security is an issue, then .pdf is the solution

Posted by Jag Prakash | June 18, 2008 11:39 am

Three pet peeves and a suggestion

Good article and comments - I've seen them all, and more. Pet peeve #1: when sharing spreadsheets the sender over protects the entire spreadsheet - I understand that formats need to be protected when using the sheet to upload into other systems, but leave the rest open so it can be used to populate the required cells! Pet peeve #2: again when sharing, a sender uses a link to another spreadsheet that the recipient doesn't have. Pet peeve #3: a co-user returns the spreadsheet with no indication of having updated it and no name change - a "revision1" addition to the name would help. A suggestion: use a standard footer that includes a copyright protection statement and/or a confidentiality statement to prevent further publishing (or at least put readers on notice) regarding your data, reports or conclusions. Rich Richer - Babson '73'74 - from before there were spreadsheets!

Posted by Richard Richer | June 18, 2008 10:34 am

Spreadsheet practices

Totals for a spreadsheet should be at the top for both ease of 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.

Posted by Tj Rese | June 18, 2008 10:01 am

Excel bad habits

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 a pager or two of information but the author never properly deletes the unneeded data. In companies where you inbox size is limited (like mine) a few giant spreadsheets can have you cleaning up email before reading/analyzing the spreadsheet.

Posted by Jerry Ballanco | June 18, 2008 09:54 am

CFO Publishing Corporation 2009. All rights reserved.