Skip over navigation
Guide and features
Guide and features
Science, Technology, Engineering and Mathematics
Featured Early Years Foundation Stage; US Kindergarten
Featured UK Key Stage 1&2; US Grades 1-5
Featured UK Key Stage 3-5; US Grades 6-12
Featured UK Key Stage 1, US Grade 1 & 2
Featured UK Key Stage 2; US Grade 3-5
Featured UK Key Stages 3 & 4; US Grade 6-10
Featured UK Key Stage 4 & 5; US Grade 11 & 12
Excel Technique: Conditional Formatting
Stage: 3 and 4
Suppose we want a cell, that has numbers in blue, to switch to red type if that cell value becomes negative.
Make sure that the correct cell is selected.
From the Format menu choose conditional formatting . . . and a dialogue box will appear.
Set the condition Cell Value Is to be less than 0 as shown in the illustration.
Click the Format button on the dialogue box to set the appearance that the cell is to have if the condition is met.
Notice the limited range of format change that is possible. You can only set the font colour, the fill (background) colour, and the cell border colour.
Click OK, and then OK back on the Condition box.
Make the cell become negative, then positive, then 0, etc. Checking that the conditional formatting is triggered correctly.
More Powerful Conditions
It can often be very useful for the format change to trigger on a calculated value not a specified value.
sheet I set conditional formatting to trigger if a cell matched its integer part, that is had no fractional part.
I changed the second input box from " between " to " equal to ".
Typed in =INT(A2) in the third input box. The = is necessary because a calculation is required.
Clicked the Format . . . button and made my choice, etc.
When a cell is replicated, not only its formula but also its formatting is copied. This includes conditional formatting.
Experiment with these options.
Meet the team
The NRICH Project aims to enrich the mathematical experiences of all learners. To support this aim, members of the NRICH team work in a wide range of capacities, including providing professional development for teachers wishing to embed rich mathematical tasks into everyday classroom practice. More information on many of our other activities can be found here.
Register for our mailing list
Copyright © 1997 - 2014. University of Cambridge. All rights reserved.
NRICH is part of the family of activities in the
Millennium Mathematics Project