Browse All Articles

Or filter by News, Views or Tips.

Conditional Formatting - Make Visual Sense of Excel Spreadsheets

When dealing with large spreadsheets, often the most important numbers can get lost in the rows and columns of cells presented to you. You can find it difficult to pick out which numbers really matter and where performance is being lost or gained, particularly in the centre of spreadsheets, not in the totals. 

Conditional formatting is the best way to make such numbers stand out.

One of the problems has been that most are only aware of individual conditional formatting, where you need to create individual rules for every cell you want to highlight (or otherwise). This is a laborious process which is also prone to 'pilot error'.

However, Microsoft recognised this and there is a trick you can perform with Outlook 2007 and 2010 detailed below.

Conditional Formatting: The Easy Way!

Let's say you have a spreadsheet of 'widget sales' from different shops in Hampshire:

What you want to do is to take the sales trends for the widget and pick out under performing shops and seasonal trends so that you can manage the locations and project orders of widgets for the busy Christmas period.

The best way of achieving this easily is to measure the monthly shop performance and identify where sales have dipped.

To do this, highlight all of the cells (excluding the first column, because you can't compare that to previous numbers) and then choose 'Conditional Formatting'. In this example I have chosen 'Highlight Cells Rules' and then 'Less Than' so I can highlight cells where a drop in performance has occurred.

Then select the first (top left) cell as the value we want to compare with and format if a cell is less than. Here, it's really important to delete the '$' signs before the row and column which will automatically be placed in the field (i.e. above, it was '=$B$3' before being manually changed). If you leave the '$' signs in then all your cells will compare with B3, without them, behind the scenes, Excel will adjust the cell reference to ensure that all the cells compare with the number directly on their left.

Do this, press 'OK' and you're done. Very quickly you can see the numbers you want to. Pick out that Winchester needs to improve performance overall, and that August was a weak retail month (don't we all know that!).

Once you've tried this a couple of times it will become second-nature to you and will give you much greater clarity in interpreting detailed spreadsheets to the benefit of your organisation or business.

← Go back to Articles


Post new comment

Your email address will be kept private.

It’s Easy to Follow Us