Excel Conditional FormattingSearch this site:
Custom Search
Excel Conditional Formatting can be used to alter the formatting of an Excel cell based on either:
This page describes Conditional Formatting in both Excel 2003 and Excel 2007 Go To Explanation of Conditional Formatting in Excel 2003 Go To Explanation of Conditional Formatting in Excel 2007 Conditional Formatting in Excel 2003BasicsIn order to define Excel Conditional Formatting, first select the cell(s) to be formatted and then, in Excel 2003, select the Format drop-down menu from the top of your Excel window. From this menu, select the option Conditional Formatting ...Within this option, is a series of boxes, to assist you with defining your conditions. The first box is a drop-down menu, from which you can select either:
Within Excel 2003, you can set up to 3 different conditions, followed by 3 different formats. It is important to understand that Excel tests the conditions in the order that they are input, so if the first condition is satisfied, the formatting linked to this condition will be applied to the cell, whether or not the further conditions are satisfied. A lack of appreciation for this can result in unexpected results in your formatting. Excel Conditional Formatting Based on a Cell's ValueIn order to define a condition that depends on one or more cells' values, select the Cell Value Is option from the first drop-down menu in the 'Conditional Formatting ...' menu. The second drop-down menu then provides you with a selection of logical tests such as 'between', 'equal to', 'greater than', etc.
The values for the logical tests can be entered as numbers or as cell references. If you enter numbers, this is straightforward - the same condition is applied to all of the selected cells. However, if you use of cell references, the rules of absolute / relative references apply. This is explained further by the following example:
Example of Excel Conditional Formatting Using Cell References
In this example, you need to apply formatting to the values in columns B-D (but not column A, as there is no previous month's data to compare the values in column A to).
In the example on the right, cell B2 is the primary selected cell, although cells, B3-B6 and C2-D6 have also been selected. It is important to be aware of the primary selected cell when using references to define Excel Conditional Formatting - as we will see shortly! From the Insert drop-down menu, select the Conditional Formatting ... option and within this,
The resulting spreadsheet is shown on the right. As required, all cells containing values that are greater than the corresponding previous monthly values have been highlighted by formatting the text in bold and coloured red.
Common Error
Clearly, this formatting is incorrect, in that it does not compare the value in each cell with the previous month's value. For the required result, we need the reference in the Excel Conditional Formatting formula to adjust for each of the selected cells. i.e. we wish for cell B2 to be compared to cell A2, cell C2 to be compared to cell B2, etc and for cell B3 to be compared to cell A3, etc. This is done by changing the selection of $A$2 to the relative reference, A2, by removing the $ signs in the reference. Excel Conditional Formatting Using FormulaeIn order to define more complex conditions that depend on the results of a formulae, within Excel 2003, select the Formula Is option from the first drop-down menu in the 'Conditional Formatting ...' menu.You are then provided with a single box, in which you can insert your formula. 
The formula that you use for your condition can be built up using any of Excel's built-in functions or operators. If the function evaluates to TRUE or a non-zero number, the condition is considered to be TRUE and your formatting is applied to the cell. If your function evaluates to FALSE, 0 or a non-number, the condition is treated as being FALSE and so the formatting is not applied to the cell(s) When entering into the 'Conditional Formatting ...' menu, your formula must always be preceded by the = sign. This is shown in the example below.
Example of Excel Conditional Formatting Using a Formula
In this example, we wish to apply Excel Conditional Formatting to each entire row, based on the sum of the values in the row.
Because A2 is the primary selected cell, the formula entered into the 'Conditional Formatting ...' menu will apply to the cell A2. Any cell references used in this formula may or may not be adjusted when applied to the other selected cells, depending on whether relative or absolute references are used in the formula. The formula to highlight rows in the above spreadsheet that total 100 or more is shown below: 
As required, the rows that have totals exceeding 100 (representing sales of over $100,000) are highlighted. Using Multiple ConditionsIn Excel 2003, you can specify up to 3 conditions, each of which has a different format specification. To specify a second or third condition, click on the Add >> button in the 'Conditional Formatting ...' menu. This adds a further set of options that can be used to specifiy your second or third condition.
Common Error
When more than one condition is used for Excel Conditional Formatting, it is important to understand that the conditions are tested in order. If the first condition is satisfied, then the remaining conditions are ignored. For example, if we wanted to specify that cells having a value greater than 10 be coloured in red and cells having a value greater than 5 be coloured in orange, the following definition would NOT work, as required: 
Conditional Formatting in Excel 2007Excel Conditional Formatting has been enhanced significantly in Microsoft Excel 2007. One major benefit is that more than 3 conditions can be used in Excel 2007. There are also several new types of formatting. However, the new interface in Excel 2007 can, at first sight, appear to be more complicated than in older versions of Excel.Basics
This menu allows you to select the type of Excel Conditional Formatting that you want to apply to your cell(s). This can be either:
Highlight Cells RulesThe Highlight Cells Rules option allows you to select a simple condition to be applied to each cell in the selected range.For each cell in which the condition evaluates to be TRUE, a selected format type is applied. Therefore, in this type of Excel Conditional Formatting, each cell in the selected range is evaluated independently of the other cells in the range. If you select one of the Highlight Cells Rules, a box appears, which allows you to input a value or a cell reference, to compare the current cell's value to. If you enter a value to compare with, this is straightforward - the same condition is applied to all of the selected cells. However, if you use of cell references, the rules of absolute / relative references apply. This is explained further by the following example:
Example of Excel Conditional Formatting Using Cell References
In this example, you need to apply formatting to the values in columns B-D (but not column A, as there is no previous month's data to compare the values in column A to).
In the example on the right, cell B2 is the primary selected cell, although cells, B3-B6 and C2-D6 have also been selected. It is important to be aware of the primary selected cell when using references to define Excel Conditional Formatting - as we will see shortly! 
The resulting spreadsheet is shown on the right. As required, all cells containing values that are greater than the corresponding previous monthly values have been highlighted.
Common Error
Clearly, this formatting is incorrect, in that it does not compare the value in each cell with the previous month's value. For the required result, we need the reference in the Excel Conditional Formatting formula to adjust for each of the selected cells. i.e. we wish for cell B2 to be compared to cell A2, cell C2 to be compared to cell B2, etc and for cell B3 to be compared to cell A3, etc. This is done by changing the selection of $A$2 to the relative reference, A2, by removing the $ signs in the reference. 
Top/Bottom RulesThe Top / Bottom Rules of Excel Conditional Formatting are new to Excel 2007, but are straightforward to use. The conditions relate to a whole range of cells, and formatting is applied only to cells that satisfy a statistical criteria, such as having the top ten values, having values greater than average, etc.Therefore, the formatting of any one cell in a selected range is dependent on the values of the other cells in the range. In order to apply this formatting, select one of the options from the Top/Bottom Rules menu, (ie. Top Ten Items, Top Ten %, etc). An options box will then appear that allows you to choose a format to apply to cells satisfying the chosen criteria. If you have selected a 'Top Ten' or 'Bottom Ten' option, there will also be the option of changing the value of '10' to a different value (eg. Top 5, Bottom 20, etc) 
Data Bars, Color Scales & Icon SetsThe Data Bars, Color Scales and Icon Sets in Excel Conditional Formatting are also new to Excel 2007. As well as being useful for highlighting patterns in data, these formatting options can also look extremely professional when applied to your spreadsheet.
All three of the options, Data Bars, Color Scales and Icon Sets work on a whole set of data (rather than on individual cells). These options evaluate the selected range of cells and apply colour or symbols to the cells, depending on each cell's value relative to the other cells in the range. The table on the left shows examples of Data Bars, Color Scales and Icon Sets applied to the values 1 - 10 in a range of spreadsheet cells. The colour schemes and symbols used in the formatting are selected from within the Data Bars, Color Scales and Icon Sets menus and are literally applied to your selected range of cells with a single click of a button! The 'More Rules...' OptionAll of the Excel Conditional Formatting types can be refined by selecting the More Rules ... option, at the bottom of any of the 'type' menus. Within this option, the conditions can be 'tweaked', and more formatting colours and styles are available.Also within this menu, is the option to 'Use a formula to determine which cells to format'. Using formulae to define Excel Conditional Formatting is discussed further in the following section. Excel Conditional Formatting Using FormulaeIn order to define more complex conditions that depend on the results of a formula, you can either:
This causes a New Formatting Rule options box to open up. In order to define a formula for your conditional formatting, select the option, 'Use a formula to determine which cells to format'. Excel then presents you with the dialog box shown on the right. The formula that you use for your condition can be built up using any of Excel's built-in functions or operators. If the function evaluates to TRUE or a non-zero number, the condition is considered to be TRUE and your formatting is applied to the cell. If your function evaluates to FALSE, 0 or a non-number, the condition is treated as being FALSE and so the formatting is not applied to the cell(s) When entering your formula, it must always be preceded by the = sign. This is shown in the example below.
Example of Excel Conditional Formatting Using a Formula
In this example, we wish to apply Excel Conditional Formatting to each entire row, based on the sum of the values in the row.
Because A2 is the primary selected cell, the formula entered into the Conditional Formatting menu will apply to the cell A2. Any cell references used in this formula may or may not be adjusted when applied to the other selected cells, depending on whether relative or absolute references are used in the formula. 
=SUM($B2:$F2)>100
Note that, in this formula, the reference to the range $B2:$F2 uses:
As required, the rows that have totals exceeding 100 (representing sales of over $100,000) are highlighted. 
Using Multiple ConditionsOne big advantage of Conditional Formatting in Excel 2007, is that you can specify more than 3 conditions, each having different format specifications. After you have specified your first condition, you can specify further conditions by simply repeating the process for adding a condition.If you want to view or edit the conditions that have been set so far, select the Manage Rules... option from the Excel Conditional Formatting menu. This shows a list of all rules that have been set so far. Note that you can opt to view either the rules for the current selected range of cells, or the rules for the whole spreadsheet. It is important to understand that the order that the Excel Conditional Formatting rules are listed is important. The condition that is positioned at the top of the list is tested first, and then the next one down, etc. This ordering process is particularly important when you have conditions that overlap (eg. A1>10, A1>5). This is illustrated in the 'Common Error' example below.
Common Error
When more than one condition is used for Excel Conditional Formatting, it is important to understand that the conditions are tested in the order that they appear in the 'Rules Manager' window. If the first condition is satisfied, then the remaining conditions are ignored. For example, if we wanted to specify that cells having a value greater than 10 be coloured in red and cells having a value greater than 5 be coloured in orange, the following definition would NOT work, as required: 
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Copyright © 2008-2010 ExcelFunctions.net |
symbol, then click on the cell A2