Thursday, August 21, 2014

Excel 2010 using If and Conditional Formatting

Often while I am using Excel I have the need to use conditional formatting and if statements to organise data.  I always end up having to lookup how to do these actions, so I am writing them down here so I can find them easier.

An If statement in Excel has the following format:
Given the following dataset:

We want to make the Column B have values of ‘READ’ or ‘WRITE’, depending on the value of column A.  If the value of the cell in column A is 0, the value of the cell in column B should be ‘READ’, a value of 1 in column A should have a value of ‘WRITE’ in column B.
To do this, we enter the following formula in cell B1:
This is evaluated as: if the cell in A1=0, then the value is “READ”, else the value is “WRITE'”

To use  conditional formatting based on the value of another cell, imagine the following dataset:

We want to highlight the values in column B with RED if the value in column A is 0, and GREEN if the value is 1.
To do this, select Conditional Formatting, and select New Rule…

Select Use a formula to determine which cells to format, and enter the formula:
Set the format fill to red.

Click OK
Select Conditional Formatting and select Manage Rules…

Enter the Applies to and drag the selection to the range of the cells you wish to have this rule apply to.

Click Apply.
You can see the rule take effect.

To create the second rule to highlight in green, follow the same steps above, but use the formula:
and set the format fill to green

You can also use rules to highlight based on the text or string value of a cell.  EG: to highlight a cell if the value in another cell is “RW”, use the formula =a1=”RW”

No comments:

Post a Comment