Responding to A Free Offer to Help Excel Users Improve Your Job Prospects, a reader sent me a workbook this morning. I’m glad he sent it, even though it contains no dashboards. This is because it illustrates many bad practices I’ve seen in Excel reports over the years.
I’ll call my visitor Randy.
Assuming that he created the workbook himself, Randy certainly seems to know his profession. And he did several things in his workbook that I like. But on balance, he’s made many common design mistakes that overshadow his professional skills.
I don’t know whether Randy’s spreadsheet work has hurt his career. But I’ve seen examples where other Excel users with good professional skills have been hurt by similar problems with their spreadsheets.
In this post, and in several future posts, I’ll discuss what I think is good and bad about Randy’s workbook.
I’m not doing this to pick on Randy, by the way. I’ve seen all of the bad things in other workbooks over the years; and there’s a good chance that many of them also are in your workbooks.
Profile Sheet
The first sheet in his workbook contains a profile of Randy’s organization. It contains the name of his company, the date, the currency, and other facts. This is a great start, and it’s similar to the information I put into my Control sheets.
Unfortunately, when he displays the date, the currency, and other profile information in other worksheets, Randy doesn’t link to the facts he’s entered in his Profile page.
This oversight is a problem, because much of that profile information can change over time. When it does, Randy must find every cell in the workbook that contains the information, and then change it. Using Search & Replace for this task is risky, because the command can change cells that shouldn’t be changed and overlook cells that should be changed.
When Excel overlooks cells that should be changed, it’s not because there’s something wrong with Excel’s Search & Replace command. It’s because if you display the same value in different cells you’re likely to enter that value differently in those cells. If your company is “Acme”, for example, you might enter it as “Acme” in one cell, and as “Acme, Inc.”, “Acme Inc.”, “Acme Corp.”, and so on in other cells. Similarly, billions of different date serial numbers could all display as Oct-2009.
If you have control-type or profile-type information that’s repeated in several pages in your workbook, put that information in one worksheet and then link to that data from other sheets in your workbook.
There’s a lot more in this workbook to talk about, which I’ll do in future posts.
Comments on this entry are closed.