If you have a worksheet with thousands of rows, it can be hard to see patterns in the data. Conditional formatting helps you highlight important information. It makes data easier to read, just like charts and sparklines.

Download our Practice Workbook.

What is Conditional Formatting?

Conditional formatting changes the look of cells based on their values. You can use colors, icons, and data bars to highlight important data. This is done by setting up rules. For example, you can create a rule that makes all values below $2000 turn red. This helps you quickly find low values in a worksheet.

How to Apply Conditional Formatting

Let’s say you want to find salespeople who meet their sales goal of $4000 per month. You can set a rule to highlight values greater than 4000.

1. Select the cells you want to format.
   
   
   
2. Click on the Home tab, then choose Conditional Formatting.
3. Hover over a formatting type and pick a rule.
   
   
   
4. A box will appear. Enter 4000 in the value field.
5. Choose a style (e.g., Green Fill with Dark Green Text).
   
   
   
6. Click OK.
   
   
   

Now, all sales above $4000 will be highlighted in green.

Using Preset Conditional Formatting

Excel offers ready-made styles to make formatting faster. These include:

• Data Bars – Add a horizontal bar inside the cell (like a bar graph).
  
  
  
• Color Scales – Change cell colors based on values (e.g., red for low, green for high).
  
  
  
• Icon Sets – Add icons (arrows, checkmarks, etc.) based on values.
  
  
  

To use a preset:

1. Select your cells.
   
   
   
2. Click Conditional Formatting.
   
   
   
3. Choose a preset style from the menu.
   
   
   

Removing Conditional Formatting

If you want to remove formatting:

1. Click Conditional Formatting > Clear Rules.
   
   
   
2. Choose to clear rules from selected cells or the whole sheet.
   
   
   

To edit or delete specific rules, click Manage Rules. This is useful if you have many rules in a worksheet.