Search Not Just Numbers

Tuesday 16 June 2015

Excel Tip: Colour-coded tabs make life much easier

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".

6 comments:

  1. Brilliant, so simple and right in front of my nose all that time!

    ReplyDelete
  2. Thank you very much for sharing this tip, Glen, it's very useful to identify sheets by their color tab, according to their functions. Great!

    ReplyDelete
  3. also worth noting that if you have several sheets selected and change the tab colour, they will ALL be changed (handy if you're not exactly sure which colour that other tab is, though the current colour should be highlighted when you try to change it)

    jim (and thanks for the credit!)

    ReplyDelete
    Replies
    1. It's always worth being aware of that with any change! Select multiple tabs with caution!

      Delete