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
Related posts:
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
Looks like the spammers got here before me…
I like this approach, I tend to do similar things, particularly marking cells to be filled in, and I also tend to choose pastel yellow for this. If there are different users / departments then I use some kind of colour code to indicate who puts what where (a bit like the “office use only” bit at the bottom of a paper form). Cells for data entry I would usually put validation on as well, if a simple ruleset exists.
I also use simple error indicators, such as a ! next to invalid cells and then a check cell at the top which looks for any errors and puts a big red banner across the whole screen screaming “something is wrong! check your data entry” (actually usually wording a bit more helpful than that). I have used conditional formatting to make the shading of entry cells clear once all cells have a valid (and hopefully correct) entry in them. This makes much cleaner printed versions if needed.
I have had couple of cases where one cell’s entry makes a big difference to the whole of the rest of a sheet, for example choosing a currency for an expenses claim form. In this case I used conditional formatting to colour the entry cells differently depending on what units they were in to make it really obvious. I changed the labels as well of course to give the right prompt.
Keep up the great blog!