Excel Basic Formats and Layouts

Do you ever pick up an old worksheet and think “What on earth was I thinking?”, or getting bad feedback from your users about a tab in your worksheet they weren’t meant to be looking at?

This can be fixed by having some standard personal rules for your worksheet formats – at least on a small degree. I have seen some recommendations and templates for a standard worksheet format through the entire worksheet - with standard heading format rules, rules for certain types of information sources, colour coding for types of input, etc. But really the best plan is to start small and try to implement a few standards and build on that.

Here’s some suggestions of some standard features I have implemented and find useful:

· Regularly looked up data

o e.g. I will always have a “Lookups” tab at the end of my worksheet, that contains additional data sources in simple vlookup friendly tables (e.g. Staff ID’s with the full staff names, messy data with their conversions to clean data).

· Calculated columns

o Calculated columns in a data table should always be on the right so the data can be re-pasted if necessary, and I will always highlight light blue to indicate an added calculated field.

· Inputs

o These get a nice bright yellow fill from me – a little ugly, but very visible to users on where they can put their inputs. Ideally on a separate tab at the front of the sheet.

· Some kind of Tab colour coding

o e.g Dark grey for sheets to ignore, red for the important sheets

· Number formatting

o Primarily use the “Commas” number formatting, adjusting the number of decimal places depending on the context

Most of these are so that if I email out or point users in the direction of a spreadsheet, I can easily give directions like “please enter only in the yellow fields” or “please only focus on the red tabs, the others are working sheets”.

These have been gained through years of trial and error, and I’m sure there’s more I will still be implanting slowly over the coming years.