# Conditional Formatting

Course: [Excel](https://www.devtutorials.ai/learn/excel) · Section: Visualizing Data
Source: https://www.devtutorials.ai/learn/excel/visualizing-data/conditional-formatting

> Highlight values greater or less than a limit, mark duplicates, and add data bars, color scales, and icon sets. Then build a sales dashboard in the Chapter 7 project.

In a long list of numbers, the important ones can be hard to spot. **Conditional formatting** colors cells automatically based on their values: overdue amounts turn red, top scores turn green, and repeated entries stand out. When the numbers change, the colors update by themselves. In this lesson, you'll highlight values greater or less than a limit, mark duplicates, and add data bars, color scales, and icon sets. At the end, you'll build a sales dashboard in the Chapter 7 project.

Open the **Excel sandbox** and follow along. All of these options are under **Conditional Formatting** on the **Home** tab.

Conditional formatting is **live**. Unlike a fill color you apply by hand, it checks the rule again every time a value changes, so the right cells stay highlighted.

## Greater Than

1. Select the numbers, such as **B2:B20**.
2. Choose **Conditional Formatting** > **Highlight Cells Rules** > **Greater Than**.
3. Type the limit, such as `1000`, and choose a format, like **Green Fill with Dark Green Text**.
4. Click **OK**.

Every cell over 1000 turns green.

## Less Than

Use the same steps for **Less Than**. For example, highlight stock levels below `10` in red so you know what to reorder.

Other rules in the same menu include **Between**, **Equal To**, and **Text that Contains**.

## Duplicate Values

**Highlight Cells Rules** > **Duplicate Values** colors every value that appears more than once. It's a quick way to catch the same invoice number or email address entered twice.

## Data Bars

**Data Bars** draw a bar inside each cell. Bigger values get longer bars, so the table looks like a mini bar chart.

Choose **Conditional Formatting** > **Data Bars** and pick a color.

## Color Scales

**Color Scales** shade each cell with a color gradient, such as **green** for the highest values, **yellow** for the middle, and **red** for the lowest. This is useful for spotting patterns across a large table, like a heat map of monthly temperatures.

Choose **Conditional Formatting** > **Color Scales** and pick a scale.

## Icon Sets

**Icon Sets** put a small icon in each cell based on its value: up, sideways, and down **arrows**, traffic **lights**, or **stars**. They are useful for showing performance at a glance.

Choose **Conditional Formatting** > **Icon Sets** and pick a set.

Icon sets and color scales compare each cell with the **others in the range**, so the same value can get a different color in a different range. Apply them to the whole column of similar values at once.

## Managing and clearing rules

- **Clear Rules** > **Clear Rules from Selected Cells** removes rules from the selection; **Clear Rules from Entire Sheet** removes them all.
- **Manage Rules** lists every rule so you can edit, reorder, or delete it.

Use one or two kinds of conditional formatting per table. Data bars, color scales, and icons on the same cells can make the table harder to read, not easier.

## Common mistakes

| Mistake | What happens | How to fix it |
|---|---|---|
| Rule applied to one cell | Only that cell is highlighted | Select the whole range, then apply the rule |
| Headings included in the range | Headings get colored or skew data bars | Select the numbers only |
| Numbers stored as text | Greater Than rules do not trigger | Convert them to real numbers |
| Too many rules on the same cells | Colors conflict and confuse | Clear rules and keep one or two |
| Expecting manual fill to update | Old highlights stay on changed values | Use conditional formatting instead |

## Exercise

Click **Reset** in the sandbox header. Type in **A1:B7**:

- `Product`, `Stock`
- `Pens`, `150`
- `Paper`, `8`
- `Folders`, `45`
- `Pens`, `60`
- `Tape`, `5`
- `Staples`, `90`

Then do each task and press **Check**.

## Chapter project

Put Chapter 7 together. You'll turn a year of sales figures into a small dashboard with a chart and conditional formatting.

## What to remember

- Conditional formatting colors cells **automatically**, and updates when values change.
- **Highlight Cells Rules**: Greater Than, Less Than, Between, Duplicate Values, and more.
- **Data Bars** draw bars in cells; **Color Scales** shade a gradient; **Icon Sets** add arrows, lights, or stars.
- Select the data only, not the headings.
- **Clear Rules** removes rules from a selection or the whole sheet.

That's the end of Chapter 7. In Chapter 8, you'll set up pages, print, and share your workbooks.