Conditional formatting in Excel allows you to change the color, border, and other properties of a cell based on rules that you specify. All you have to do is select the cell or range of cells that you want, then open the Format menu and choose Conditional Formatting. (In Excel 2007, click the Conditional Formatting button on the Home ribbon, then choose Highlight Cells Rules.) You can enter your criteria, and then click the Format button to tell Excel how you want cells formatted when they meet your conditions.
As a simple example, let’s say you have a spreadsheet with customer names in column A and their current balance in column B. If you want balances over $10,000 to stand out:
- Select column B
- Go to Format > Conditional Formatting.
- Leave the first option set at “Cell Value Is”.
- Change the second option to “greater than or equal to”.
- Enter 10000 in the third box.
- Click the Format button and make whatever formatting changes you want – bold, italic, or color, add a border, or set a background color (cell shading).
- Click OK to save your changes and the conditional formatting will be applied immediately to any cell with a balance of 10,000 or more.
Now let’s say that column C holds each customer’s credit limit. And you want to know who is over their limit. This time you would select just cell B2 (the balance for the first customer in the list) instead of the entire column.
- Go back to Format > Conditional Formatting.
- Leave the first option set at “Cell Value Is”.
- Change the second option to “greater than”.
- Enter “=C2” (without quotes) in the third box. You could click on cell C2 with your mouse, but you would need to remove the $ that Excel automatically adds.
- Click the Format button to make the formatting changes that you want.
- Click OK to save your changes.
Since you don’t want to have to repeat that process for every line, today’s tip within a tip is the format painter. With cell B2 still selected, click on the paintbrush button on your toolbar. Now use your mouse to select the cell or range of cells that you want to have B2’s formatting copied to. Excel will automatically adjust which cell you use for comparison as it applies the conditional formatting to the new cells. That is, the conditional formatting for cell B3 will automatically be adjusted to “greater than” C3
Feel free to explore the various options in Conditional Formatting. If you don’t like the results, the Delete button at the bottom of the Conditional Formatting window will delete the formatting but leave the cell contents in place.
