The standard approach to professional application design in Excel is to split out the data, the logic, and the presentation (Bullen et al 2005). In practice this is often harder than it at first seems, but that’s not the point of this post, the point is that if you adopt this structure you have to set about designing you worksheets appropriately.
There are a few guides to worksheet design. Some talk about how a worksheet should flow, top to bottom and left to right(Codematic, Eusprig), some focus more of how and why they should be formatted (SpreadsheetStyle ).
In my experience, more often than not, there needs to be some compromise in any none trivial application. This is especially the case in the “reporting/presentation” layer.
Recently Simon posted about the first thing we look for in a spreadsheet we inherit. He was interested in know the key indicators that the spreadsheet has been developed poorly. This in turn sparked a discussion about how much formatting is ok?
I’ve haven’t done much development work for quite a while, until last week when I developed a model for an in-house project Below is one of the sheets, with some notes on it.
Basically I have tried to make it easy to use. I have removed anything that is not used by the model and tried to guide the user to the bit that are.
Thoughts: Do you consider this too much formatting, too little formatting. What would you do differently
These are great ideas, but clients sometimes have different ideas. And clients are the ones paying the bills.
I actually prefer (sometimes) having summary formulas at the top of a sheet with the heavy duty calculation further down for reference if needed.
For one client, one sheet would never work (SpreadsheetStyle suggestion). 4 pages of detailed input, with 35+ pages of calculations, and 10 summary views. Complicated problems require a lot of detail for clients that want to be able to follow the entire calculation process. The challenge then becomes one of making it easy for calculations to be followed.
I agree with the suggests you list here and on the links, but alas, clients have much different ideas.
Hi, I just found your site, while researching Excel Data Experts. I really liked your post about spreadsheet design. Are you familiar with other excel data visualization sites? I’d enjoy connecting by email if you do. My client’s site is http://www.tableausoftware.com/fast-analytics, if you’re interested. Keep up the blog posts — good stuff!
Oops! Sorry, added a comma. http://www.tableausoftware.com/fast-analytics