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
To CFO.com:
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
To CFO.com:
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.

• « previous
• 1
• 2
• 3