There is a Russian proverb that you don’t really know someone until you have shared a pound of salt with him. In the office, you don’t really know someone you have shared an Excel spreadsheet with him. A well-formatted spreadsheet makes you a valuable part of the team.
One with hidden rows and randomly hard-keyed cells?
Feel the wrath of 10,000 analysts upon you.
Download your own 10 Commandments checklist here.
Below are Ten Commandments of how to design a spreadsheet worth sharing. I am going to leave out the “Thou shalts.” I lack God’s authority, and even to me, Excel is not quite in the realm of morality.
1. Have no other formats before Excel. I see the world through Microsoft Excel. Any other format is a hazy substitute. I prefer the original .xlsx file. Don’t send a PDF or JPEG. It’s just going back into Excel, but this time I will have to keypunch it.
2. Send it ready to print. There is nothing worse than hitting “Print” on a file and finding random appendages of work across multiple pages. Have your work ready to print.
3. Do not send in page break preview. While page break view will help you avoid the Second Commandment, leaving in print break view is a violation of its own commandment. It is really difficult to read over a huge “Page 1” watermark.
“Hard to see over that giant Page 1, no?”
4. Do not send with multiple tabs selected. How many times have I opened an Excel file and started to manipulate things, only to run into gray boxes on the Home Ribbon? I fiddle around with it and find out that there are multiple tabs selected. Send spreadsheets with one tab selected at a time. 
“Why is this thing locking up on me? Multiple tabs selected, that’s why.”
5. Have a unique key. There’s a good chance that I am going to merge the data you gave me with some other data. To do this, I need a reliable key to map between the data. An employee ID number or SKU is a good key. A name or product number is NOT a good key. These identifiers change and are not unique. If you send me a file with no unique key, the first thing I am going to do is assign one. Spare me the work and put a key on your file.
“This is so helpful, because there’s only one Jones in the entire company.”
6. Group your inputs and outputs separately. Make spreadsheets intuitive. It should flow like any problem: inputs, assumptions, calculations, output. This structure makes easier for others to study your workflow. It will also be easier to check errors. Excel’s reputation as unreliable comes from poorly structured workbooks. By designing your workbook thoughtfully, you avoid mistakes.
7. Do not hide data. I understand that sometimes you want to draw attention away from some data. But hiding selected rows or columns is dangerous. Hidden spreadsheet rows hit Barclays with toxic assets from Lehman. Note that the culprit also sent the file as a PDF, thus violating the First Commandment. Group the data instead — this way one can easily spot where data is hidden.
“Oh, there’s more data there? Glad I didn’t delete it and cause a financial panic.”
8. Use cell anchoring. You should assume that others will be manipulating your work and make it as easy as possible for them to do so. Let’s say that you used “VLOOKUP” to check out one store’s sales. Then I added another store beneath that one and dragged down your formula. The table array was not anchored, causing the VLOOKUP to “miss” store 50’s sales. This is not a good feeling. Anchor your references so that any ad-hoc analysis
will not accidentally miss data.
“Is it that hard to hit F4?”
9. Align your data labels. Avoid what I call “label rubbernecking,” where your label is left-formatted, your data is right-formatted and you keep bouncing between the two. This is even worse when there is a big gap between the label and the values. In general, it’s smart to be consistent with your column labels too. A left-align followed by a right-align can leave a big gap in your columns, making things appear jumbled.
“My eyes are playing pinball on this thing.”
10. Do not hardcode values that may change.
This could be the most offensive Excel mistake. When adding calculations in Excel, make sure that these calculations can change efficiently. For example: below, we have after-tax sales for a few items. Currently the sales tax is 30% But what if we wanted to change that? Then we would have to manually re-key each entry. It would be much better to use a formula here than to hard-code.
“Hello? Refer to cell E2 and save us a lot of trouble.”
I hope these tips help you in spreadsheet design. For more on the importance of mitigating spreadsheet risk management, check out this story on “Chief Excel Officers.”
Leave a Reply