what is conditional formatting and how can we control it
Answers
Answered by
3
Conditional Formatting (CF) is a tool that allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula. For example, you can have a cell appear bold only when the value of the cell is greater than 100.
sukhneetkaur57:
how we control it
Answered by
1
Conditional Formatting (CF) is a tool that allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula. For example, you can have a cell appear bold only when the value of the cell is greater than 100. When the value of the cell meets the format condition, the format you select is applied to the cell. If the value of the cell does not meet the format condition, the cell's default formatting is used. (By "default formatting", I mean the formatting that you set up using the normal formatting tools, not necessarily the worksheet's default font and font size.)
A cell can have up to 3 format conditions, each with its own formats, in addition to the default value of "no formatting". This allows you to have different formats depending on the value of the cell. For example, if the value was greater than 200, you can display the text in red, but if the value is between 100 and 200, display the text in green.
Remember that Conditional Formatting is the same as adding one or more formulas to each cell in which you use it, so applying Conditional Formatting to a large number of cells may cause performance degradations. Use caution when applying to to large ranges.
Simple Conditional Formatting
The simplest Conditional Formatting uses the Cell Value Is option in the CF dialog box, and uses one of the preset comparison operations. This CF Dialog for Excel2000 is shown below.
This dialog shows a format condition that will display the cell in Red when the value of the cell is between 10 and 20. In addition to the betweenoperation, there are several other comparison operations like greater than and less than.
To apply a format condition to a cell or range of cells, first select the range to which you want to apply the format condition, then open the CF dialog from the Format menu. This displays the dialog shown above. Next, change the between operation to which ever operation you want. Next, enter the value or values for that condition. Finally, click the Format button on the dialog box. You'll see the standard cell formatting dialog. Not all format items are available in Conditional Formatting. For example, you cannot change the Font or Font Size with Conditional Formatting. Once you have select your format, click the OK button.
You can add a second or third format condition by clicking the "Add>>" button on the dialog. Each of the three format conditions can have its own format style.
Order Of Conditions
When you have more than one format condition for a cell, only the first format condition which is true is used. The remaining conditions are not evaluated. For example, suppose you have three format conditions for cell A1.
1) Bold Text when the value is greater than 10
2) Red Text when the value is greater than 20
3) Gray Background when the value is greater than 30
In this case, if the value of A1 is 100, the text will display in bold, but not red or with a gray background, because one the first condition, greater than 10, is met, the remaining conditions are not evaluated. To get around this, you must put your format conditions in the right order.
1) Gray Background when the value is greater than 30
2) Red Text when the value is greater than 20
3) Bold Text when the value is greater than 10
Here, the most restrictive condition is entered first, and the least restrictive condition is entered last. In this example, A1 will appear with a gray background if the value is greater than 30, with red text if the value is between 21 and 30, with bold text if the value is between 11 and 20, and in the default format if the value is between 0 and 10.
Conditions are never combined. This means that in the example above, a value of 40 will appear in with a gray background (from Condition 1), but not with red text (Condition 2) or in bold text (Condition 3). Even though all three conditions are true, logically, format conditions are not evaluated once a true conditions is found.
The logic of Conditional Formatting can be described as
A cell can have up to 3 format conditions, each with its own formats, in addition to the default value of "no formatting". This allows you to have different formats depending on the value of the cell. For example, if the value was greater than 200, you can display the text in red, but if the value is between 100 and 200, display the text in green.
Remember that Conditional Formatting is the same as adding one or more formulas to each cell in which you use it, so applying Conditional Formatting to a large number of cells may cause performance degradations. Use caution when applying to to large ranges.
Simple Conditional Formatting
The simplest Conditional Formatting uses the Cell Value Is option in the CF dialog box, and uses one of the preset comparison operations. This CF Dialog for Excel2000 is shown below.
This dialog shows a format condition that will display the cell in Red when the value of the cell is between 10 and 20. In addition to the betweenoperation, there are several other comparison operations like greater than and less than.
To apply a format condition to a cell or range of cells, first select the range to which you want to apply the format condition, then open the CF dialog from the Format menu. This displays the dialog shown above. Next, change the between operation to which ever operation you want. Next, enter the value or values for that condition. Finally, click the Format button on the dialog box. You'll see the standard cell formatting dialog. Not all format items are available in Conditional Formatting. For example, you cannot change the Font or Font Size with Conditional Formatting. Once you have select your format, click the OK button.
You can add a second or third format condition by clicking the "Add>>" button on the dialog. Each of the three format conditions can have its own format style.
Order Of Conditions
When you have more than one format condition for a cell, only the first format condition which is true is used. The remaining conditions are not evaluated. For example, suppose you have three format conditions for cell A1.
1) Bold Text when the value is greater than 10
2) Red Text when the value is greater than 20
3) Gray Background when the value is greater than 30
In this case, if the value of A1 is 100, the text will display in bold, but not red or with a gray background, because one the first condition, greater than 10, is met, the remaining conditions are not evaluated. To get around this, you must put your format conditions in the right order.
1) Gray Background when the value is greater than 30
2) Red Text when the value is greater than 20
3) Bold Text when the value is greater than 10
Here, the most restrictive condition is entered first, and the least restrictive condition is entered last. In this example, A1 will appear with a gray background if the value is greater than 30, with red text if the value is between 21 and 30, with bold text if the value is between 11 and 20, and in the default format if the value is between 0 and 10.
Conditions are never combined. This means that in the example above, a value of 40 will appear in with a gray background (from Condition 1), but not with red text (Condition 2) or in bold text (Condition 3). Even though all three conditions are true, logically, format conditions are not evaluated once a true conditions is found.
The logic of Conditional Formatting can be described as
Similar questions