Conditional Formatting in Excel with Example

Conditional formatting in Excel is a feature that allows you to format cells based on specific conditions or criteria. It helps you visually analyze and highlight data in a spreadsheet by applying formatting rules to cells or ranges of cells. This makes it easier to identify trends, outliers, and important information in your data.

Conditional formatting can be used to change the font color, cell background color, border styles, and other formatting options of cells based on various criteria. 

Some common uses of conditional formatting include:

1. Highlighting values above or below a certain threshold.
2. Color-coding data based on specific criteria.
3. Identifying duplicates or unique values.
4. Creating data bars, color scales, or icon sets to represent data values.
5. Applying formatting based on text or date conditions.

To use conditional formatting in Excel, you typically follow these steps:

1. Select the range of cells you want to format.
2. Go to the "Home" tab on the Excel ribbon.
3. Click on the "Conditional Formatting" option.
4. Choose the type of formatting rule you want to apply (e.g., "Highlight Cells Rules," "Data Bars," "Color Scales," "Icon Sets," "New Rule," etc.).
5. Configure the rule settings, including the criteria and formatting options.
6. Click "OK" to apply the formatting.

Let's work on example

You have a list of test scores in column B from B3 to B13, and you want to highlight scores above 80 with a green fill color.

Here are the detailed steps:

1. Select the Range of Cells: First, select the range of cells that you want to format. In our example, select cells B3 to B13, which contain the test scores.

2. Access Conditional Formatting: Go to the "Home" tab on the Excel ribbon.

3. Choose Conditional Formatting: Click on the "Conditional Formatting" button in the "Styles" group. This will open a dropdown menu.

4. Select "New Rule": From the dropdown menu, select "New Rule." This will open the "New Formatting Rule" dialog box.

5. Choose Rule Type: In the "New Formatting Rule" dialog box, you'll need to choose the type of formatting rule. In our example, select "Format cells that contain" under the "Select a Rule Type" section.

6. Set Rule Criteria: Now, you need to set the criteria for the rule. In our example:

   • In the "Format cells that contain" section, you'll see options like "Format cells with," "Cell Value," and "between." Select "Cell Value."
   • In the next dropdown, choose "greater than."
   • Enter the threshold value, which is 80 in our example.

7. Define the Formatting: Click the "Format" button. This will open the "Format Cells" dialog box.

   • In the "Format Cells" dialog box, go to the "Fill" tab.
   • Choose a green color for the fill (or any formatting options you prefer).
   • Click "OK" to confirm the formatting.

8. Apply the Rule: Back in the "New Formatting Rule" dialog box, you'll see a preview of the formatting you've defined.

   • Click "OK" to apply the rule to the selected range.

Now, Excel will apply conditional formatting to the test scores in column B. Any score above 80 will have a green fill color. You should be able to see the formatting immediately.