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.