From the Not Just Numbers blog:

This week’s tip is a really simple one, but can make using your spreadsheet so much easier.

It is something we have used with paper files for decades, if not centuries, but is sadly not used anywhere near as much in spreadsheets. This is a shame, because it is really simple and very effective.

I’m talking about colour-coding your worksheet tabs, i.e. the little tabs along the bottom of the screen for switching between your different worksheets.

We can change the colour of a tab simply by right-clicking on the tab name and selecting Tab Color (the menus have the US spelling)., we can then select our chosen colour from the palette.

Despite how easy it is to do, used well it can make a huge difference to the usability of the spreadsheet.

By ‘used well’ I mean the following:

  • Use a small number of colours to identify particular types of worksheets. For example I often use red to identify sheets that contain external data that must be refreshed, green for output reports and orange for data entry.
  • Order the tabs sensibly. You can move tabs simply by clicking and dragging them. I find ordering them in typical sequence of use is useful (left to right), so in a reporting spreadsheet we might have external data sheets that need refreshing first, followed by the reports that are generated from them. If there are also data entry sheets, I would tend to include these last if they are just standing data that it is not always necessary to update, or if they need editing every time, include them at the appropriate place in the workflow. Combined with the colours and well-chosen tab names, this can make the spreadsheet quite intuitive to use.
  • Keep tab names short but meaningful. The shorter they are, the more that will fit on the screen without scrolling (thanks to Jim’s comment on last week’s post for this simple tip).
My suggestions above are just that, play with this and you will find what suits you and the particular spreadsheet, but don’t ignore this ability to add very clear visual cues to your spreadsheet.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.