Contents
Basics
Everything is very simple. We want the cell to change its color (fill, font, bold-italic, borders, etc.) if a certain condition is met. Fill the negative balance with red, and the positive balance with green. Large clients should be in bold blue font, and small ones in gray italics. Overdue orders are highlighted in red, and delivered on time – in green. And so on – as far as imagination is enough.
To do this, select the cells that should automatically change their color, and select from the menu Format – Conditional Formatting (Format — Conditional formatting).
In the window that opens, you can set the conditions and, by clicking then the button Framework (Format), cell formatting options if the condition is met. In this example, A’s and B’s are highlighted in green, C’s in yellow, and underachievers in red:
Button And also>> (Add) allows you to add additional conditions. In Excel 2003, their number is limited to three; in Excel 2007 and later, it is infinite.
If you set conditional formatting criteria for a range of cells, you can no longer format those cells manually. To regain this opportunity, you need to remove the conditions using the button Remove (Delete) at the bottom of the window.
Another, much more powerful and beautiful way to use conditional formatting is the ability to check not the value of the selected cells, but the given formula:
If the specified formula is true (returns TRUE), then the desired format is triggered. In this case, you can specify much more complex checks using functions and, in addition, check some cells, and format others.
Highlight the entire line
The main nuance is the dollar sign ($) before the column letter in the address – it fixes the column, leaving the link to the row unfixed – the values to be checked are taken from column C, in turn from each subsequent row:
Highlighting the maximum and minimum values
Well, everything is quite obvious here – we check whether the cell value is equal to the maximum or minimum in the range – and fill it with the appropriate color:
In the English version, these are functions MIN и MAX, respectively.
Highlight all values greater than (less than) the mean
Similar to the previous example, but using the function AVERAGE (AVERAGE) to calculate the average:
Hiding cells with errors
To hide cells where an error occurs, you can use conditional formatting to make the font color in the cell white (cell background color) and the function EOSH (ISERROR), which evaluates to TRUE or FALSE depending on whether the given cell contains an error or not:
Hiding data when printing
Similar to the previous example, you can use conditional formatting to hide the contents of certain cells, for example, when printing, make the font color white if the contents of a particular cell have a given value (“yes”, “no”):
Filling invalid values
Combining conditional formatting with a function COUNTIF (COUNTIF), which gives the number of found values in the range, you can highlight, for example, cells with invalid or unwanted values:
Checking dates and dates
Because dates in Excel are the same numbers (one day = 1), you can easily use conditional formatting to check when tasks are due. For example, to highlight overdue items in red, and those due in the next week in yellow:
PS
Happy owners of the latest versions of Excel 2007-2010 have at their disposal much more powerful conditional formatting tools – filling cells with color gradients, mini-graphics and icons:
This formatting for the table is done, literally, in a couple of mouse clicks … 🙂
- Highlight duplicates in the list with color
- Comparing two lists and highlighting matching items.
- Create project schedules (duties, vacations, etc.) using conditional formatting