Excel Formatting | Excel Digest

April 16, 2009 By admin

To format a cell based on the value of another cell follow these steps:

1. Select the cell you want to format, “A1″ in this example.
2. In the Home tab, Styles group, click on Conditional Formatting.
3. Select New Rule.

xl7cformat3

4. The New Formatting Rule window will open.

xl7cformat4

5. Click Use a formula to determine which cells to format.
6. The New Formatting Rule window will now look like this:

xl7cformat5

7. In the edit box labeled “Format values where this formula is true“, enter “=” followed by a formula referencing the other cell.

In this example the formula: “=B1<0″ is entered.
8. Click the Format button. The Format Cells window will open.

xl7cformat6

9. Specify the formatting you want. In this example I changed the cell color to “RED”.
10. Click OK to close The Format Cells window.

11. Click OK to close The New Formatting Rule window.

To summarize this example: cell A1 will have a RED color when cell B1 value is less than zero.

May 18, 2008 By admin

In the first combo box select Formula is.
In the text box next to it write the formula as follows: =$A$1<100. An equal sign followed by the cell reference followed by a comparison or a condition.
Click the format button, the Format Cells dialogue will open.

Select the formatting you want to apply when the cell value meets the condition e.g. you can set the font color to red.

In this example, if the value of cell A1 is less than 100, then the color of the selected cells will be red.

Applies to: Excel 2003

Conditional formatting in Excel 2007 (based on value of another cell)