When you need to highlight information according to its value in a Google Docs spreadsheet, you can use the conditional formatting tool. While this tool isn’t as sophisticated as the corresponding feature in Excel, for example, it does have its uses and we’ll show you how to put it to work. In this example, we'll have a set of average temperatures for Melbourne and use conditional formatting to display different colours, depending on the temperature.
To colour cells where the temperatures exceed 20°, select the column of data and click Format —> ‘Conditional Formatting’. From the first drop-down list, select ‘Greater Than’ and in the next box, type 20 . You can then choose to colour the text in the cell, the cell background or both; for example, select Background and choose a colour to use. When you click ‘Save Rules’, any value in the list that’s greater than 20° will be highlighted.
Like regular formatting, you can copy conditional formats. Click on a cell that contains a format you want to apply to a cell, click the ‘Paint Format’ icon on the toolbar and drag over the cell(s) to format.
You can use multiple rules to colour cells differently according to their contents. In this case, you need to understand that the rules are evaluated from the top of the list down and as soon as a cell contents match a rule, that rule is applied and no other rules are evaluated. So, if you wanted to highlight cells that have values greater than 24° using a different colour, you must create that rule as the first one in the dialog and then create the rule that tests for values greater than 20°. If you don’t order your rules correctly, you won’t get the results you expect.
If you use a series of colours that relate to your data in some way, you can format your data so the colours suggest magnitude. In our temperature example, you could format values greater than 24° as red, those greater than 20° in orange, those greater than 16° a yellow orange and those less than 16° a plain yellow. This will reinforce the larger values as being hotter because you’re using a ‘hot’ colour for them.
Be aware that using ‘Greater Than 16’ and ‘Less Than 16’ as tests will leave cells with values that are equal to 16 not coloured at all. To avoid this, you need to include a test for ‘Equal To 16’ to make sure that cells with a value equal to 16 are coloured, too.
