Do you ever pick up an old worksheet and think, “What on earth was I thinking?”, or get 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 workbook - 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 are some suggestions of standard features I have implemented and find useful:
Regularly looked up data
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 IDs with the full staff names, messy data with their conversions to clean data).
Calculated columns
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 in light blue to indicate an added calculated field.
Inputs
These get a nice bright yellow fill from me – a little ugly, but very visible to users to show where they can put their inputs. Ideally on a separate tab at the front of the sheet.
Some kind of tab colour coding
e.g. dark grey for sheets to ignore, red for the important sheets.
Number formatting
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 implementing slowly over the coming years.