How do I apply conditional formatting to the second highest value in Excel?

By /

Conditional formatting is a feature in Microsoft Excel that allows users to format cells based on specific criteria or rules. One of the common uses of conditional formatting is to highlight the highest or lowest values in a range of cells. However, if you want to highlight the second highest value, there is a slightly different process. To apply conditional formatting to the second highest value in Excel, you will need to use a formula or custom rule to identify the second highest value, and then apply the desired formatting to it. This will help you to easily identify and analyze data in a range of cells, and make informed decisions based on the second highest value.

Excel: Apply Conditional Formatting to Second Highest Value

To apply conditional formatting to cells in Excel if another specific cell is not empty, you can use the New Rule option under the Conditional Formatting dropdown menu within the Home tab.

The following example shows how to use this option in practice.

Example: Apply Conditional Formatting to Second Highest Value in Excel

Suppose we have the following dataset in Excel that shows the points scored by various basketball players:

Suppose we would like to apply conditional formatting to the second highest value in the Points column.

To do so, we can highlight the cells in the range B2:B13, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:

In the new window that appears, click Use a formula to determine which cells to format, then type =B2=LARGE(B$2:B$13,2) in the box, then click the Format button and choose a fill color to use.

Excel conditional formatting second highest value

Once we press OK, the second highest value in the Points column will be highlighted with a light green background:

We can manually verify that the correct cell is highlighted by identifying 40 as the highest value in the Points column and 35 as the second highest value:

Note #1: If multiple cells have the second highest value, then each of those cells will receive conditional formatting.

Note #2: We chose to use a light green fill for the conditional formatting in this example, but you can choose any color and style you’d like for the conditional formatting.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. Excel: Apply Conditional Formatting to Second Highest Value
  2. How to apply conditional formatting to a cell based on the value in another cell?
  3. How can I apply conditional formatting in Excel to highlight cells that contain partial text?
  4. How can I apply conditional formatting in Excel to display Yes/No values?
  5. Excel: Apply Conditional Formatting to Yes/No Values
  6. Excel: Apply Conditional Formatting if Cell Contains Formula
  7. How to Apply Conditional Formatting to Dates within 6 Months in Excel
  8. How to Apply Conditional Formatting Based on Adjacent Cell in Excel?
  9. How to Apply Conditional Formatting to Blank Cells in Excel?
  10. What is Excel: Apply Conditional Formatting if Cell Contains Text