Table of Contents
Excel is a powerful tool that allows users to analyze, organize, and manipulate data in a variety of ways. One of the most powerful features of Excel is the ability to apply conditional formatting to cells in a spreadsheet. This feature allows users to highlight or change the appearance of cells based on certain criteria. In this tutorial, we will discuss how to apply conditional formatting to the second highest value in a range of cells. This technique can help you quickly identify and analyze important data in a spreadsheet. We will also discuss how to use the same technique to identify the second lowest value in a range of cells. By the end of this tutorial, you will have a better understanding of how to apply conditional formatting to the second highest and second lowest values in a range of cells.
To apply conditional formatting to the cell with the second highest value in Excel, 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.
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.