Conditional formatting
Conditional formatting, as the name implies, is about formatting a cell
based on some preset conditions. These conditions could be based on the
content within the cell or from some other cells. We can define up to 3
different formats for 3 different conditions in each. For example, you
could use conditional formatting to monitor whether salespersons fall
short, meet or exceed their sales targets. Here is an example on how
we can set these targets
Setting the conditional formats based on the value (actual
sales as a percentage of the sales target) within the cell.
1. Select the cell you wish to set the conditional formatting.
2. Click on Format in the menu, followed by conditional formatting
3. A dialog box will appear as shown:
4. Set the first condition (fall short of sales target) by selecting
"less than or equal to" instead of using the default “between” option
using the drop down button. The 2 text boxes on the right would be
replaced by one single text box. Enter the value “90%” into the text
box. This means that the salesman will be considered as falling short
of the target if the actual sales achieved by him is less than or equal
to 90% of the sales target. Click on the format button and define the
format (for fonts, cell border and colors & patterns) you want Excel
to show when the condition is met. One suggestion is to format the cell "red".
5. Next click on the Add button to create a second condition. In this
second condition, leave the “between” option as it is, enter the value "90%" and
"110%" into the 2 text boxes. Click on the format button (for condition 2)
and preset the format for this range. In our example, let’s assume that
there will be no change in format if the percentage falls within this range.
6. Click on the Add button, and make the cell turns “green” format if
it meet the 3rd condition, i.e. ">110%".
7. Click on the OK button when you are done.
8. Once the conditional formatting for the cell has been
set, we will be able to monitor the salespersons performance
using the cell’s color code. If the cell turns red, we will
immediately know that the salesperson has achieved less than
or equal to 90% of the sales target. If the cell shows a normal
format, they are within target. If it turns green, the salesperson
has exceeded the sales target.
Setting the cell format based on conditions in another cell
1. Select the cell you wish to set the conditional formatting.
2. Click on Format in the menu, followed by conditional formatting.
3. A dialog box will appear as shown:
4. Click on the drop down box on the left of the dialog box and
change the option from "Cell Value is" to "Formula is". The last
3 dialog boxes would be replaced by one single dialog box.
5. Assuming that the cell you want to set up the conditional
formatting is cell A1 (e.g. salesman’s name), and the percentage
of actual sales against the sales target is found in cell C1.
6. Enter the formula into the text box as shown:
7. Do the same for the condition on meeting the target and
another condition for exceeding the target.
As the value in cell C1 changes, the format in cell A1 will change accordingly.
An example
Given below is a list of stock items:
Anytime the Item # changes I want to highlight the row, as in
the above example: The first 2 lines would not be highlighted,
the third line would be, then lines 4,5,6 would not be highlighted,
the last 4 items would be highlighted.
1. Select Cell A2.
2. Go to menu -> Format -> Conditional Format.
3. Change the option from cell value is to "formula is".
4. In the formula field, enter the following formula without
quotes "=IF(MOD(SUM(IF(FREQUENCY(IF(LEN($A$2:$A2)>0,MATCH($A$2:$A2,$A$2:$A2,0),""),
IF(LEN($A$2:$A2)>0,MATCH($A$2:$A2,$A$2:$A2,0),""))>0,1)),2)=0,0,1)".
Without fixing the row allows us to copy the format down to the
rest of the list)
5. Format the cell based on your preference by clicking on the
format button located on the right.
6. Copy the format from A2 to the rest of the table.
7. You will notice that the format you indicated appears when the item #
changes.
Back to the top of conditional formatting
|
