How to Use Conditional Formatting in Excel

By Sonia Waring

Microsoft Excel is an application used for creating spreadsheets, which present data in columns and rows. Spreadsheets are a very efficient way of storing and manipulating data, but not always the easiest way to visually absorb information. Often a chart or graph will make some kinds of data easier to view and understand. In other cases, conditional formatting is the way to go. Conditional formatting is just what it sounds like: Excel will alter the formatting--color, typeface, and so forth--or add other visual touches based on conditions you specify, such as if the number is greater or less than a certain threshold, or if the text includes certain characters.

Step 1

Open a new or existing spreadsheet in Excel 2007. Enter any necessary data.

Step 2

Select the cells that require conditional formatting. Select all cells if you don't need any cells to be exempt from the conditions.

Step 3

On the Home tab of the Ribbon, click "Conditional Formatting." Choose "Highlight Cells Rules," then select the condition that matches your needs: For text, choose "Text that contains...," or for numbers, one of the other options. Let's assume you want to highlight cells containing numbers greater than 10: Choose "Greater than..."

Step 4

In the resulting dialog box, type "10" into the empty field, then to the right, click the drop-down arrow and choose either an existing format or "Custom Format" to choose your own cell background and border, font color and more.

Tips & Warnings

  • Excel 2003 users also have access to conditional formatting, and the process is quite similar. Instead of pressing a Ribbon button, users should begin by clicking "Format," "Conditional Formatting."
  • When user-defined conditional formatting won't cut it but a full-blown chart isn't necessary, consider the "Data Bars" option. It creates small bars corresponding to number values within each selected cell.
  • You can add different conditions for different areas, or multiple conditions per area. Simply select the appropriate cells and add more conditions. New conditions don't overwrite old rules.