Highlight the Highest and Lowest value in Google Sheets

Highlight the Highest  and Lowest value in Google Sheets
Reading Time: 4 minutes

You can highlight the highest and lowest value in Google Sheets using Conditional Formatting.

What is Conditional Formatting?

Conditional formatting makes it easy to highlight certain values or make particular cells easy to identify. This is because conditional formatting colour codes the cells based on specified criteria, for e.g. highlight all cells whose value is above 10, or highlight cells with the minimum value to name a few examples. If you want to explore more in conditional formatting more, go through this article.

Why use Conditional Formatting to highlight the highest and lowest values in Google Sheets?

Example of highlighting the Highest & Lowest value in Google Sheets
Figure 1: Example of highlighting the Highest and Lowest value in Google Sheets

As mentioned above, conditional formatting helps the user in colour coding a particular cell or group of cells based on the criteria specified by the user. So let’s say you apply conditional formatting on a range of cells containing marks of students, and apply criteria to highlight the student with minimum marks, it will highlight the student with minimum marks.

How to highlight the highest and lowest value in Google Sheets using Conditional Formatting?

Highlight the Highest value in Google Sheets

Step 1: Select the Dataset

  • Select the top cell and drag the selection to the last cell.
Select Dataset of Interest
Figure 2: Select Dataset of Interest

Step 2: Select Format → Conditional Formatting

  • Navigate to the top menu.
  • Select Format.
  • Select Conditional Formatting.
Select Conditional Formatting
Figure 3: Select Conditional Formatting

Step 3: Go To Single Colour → Apply To Range

  • After opening conditional formatting, navigate to Single Colour Tab.
  • Make sure that the range in the “Apply to range” is the same as the range you selected.
Verify the range
Figure 4: Verify the range

Step 4: Apply the formula to highlight the highest and lowest value in Google Sheets

  • Go to Format Rules.
  • Scroll down to Custom Formula and select it.
Select custom formula
Figure 5: Select custom formula
  • Use this formula to highlight the highest value in the selected range:

=B2=MAX($B$2:$B$9)

Step 5: Select the Colour of interest and click Done

  • Go to formatting style.
  • You can change the highlight colour from here.
Select colour to highlight the maximum value
Figure 6: Select colour to highlight the maximum value
  • The final output is a cell with the highest value is highlighted:
 Highest value is highlighted
Figure 7: Highest value is highlighted

Highlight the Lowest value in Google Sheets

Step 1: Select the Dataset

  • Select the top cell and drag the selection to the last cell.
Select Dataset of Interest
Figure 8: Select Dataset of Interest

Step 2: Select Format → Conditional Formatting

  • Navigate to the top menu.
  • Select Format.
  • Select Conditional Formatting.
Select Conditional Formatting
Figure 9: Select Conditional Formatting

Step 3: Go To Single Colour → Apply To Range

  • After opening conditional formatting, navigate to Single Colour Tab.
  • Make sure that the range in the “Apply to range” is the same as the range you selected.
Verify the range
Figure 10: Verify the range

Step 4: Apply the formula to highlight the highest and lowest value in Google Sheets

  • Go to Format Rules.
  • Scroll down to Custom Formula and select it.
Select custom formula
Figure 11: Select custom formula

  • Use this formula to highlight the highest value in the selected range:

=B2=MIN($B$2:$B$9)

Custom formula to highlight cell with minimum value
Figure 12: Custom formula to highlight cell with minimum value

Step 5: Select the Colour of interest and click Done

  • Go to formatting style.
  • You can change the highlight colour from here.
  • The final output is a cell with the highest value is highlighted:
Cells with highest and lowest value in Google Sheets range are highlighted
Figure 13: Cells with highest and lowest value in Google Sheets range are highlighted

Important Note: If you want to highlight the highest and lowest value together, just select “Add Another Rule” after highlighting the highest value and follow the same procedure for the lowest value.

Conclusion

You can highlight the highest and lowest value in Google Sheets using Conditional Formatting. You can change the colours of the highest and lowest value in Google Sheets as per your preference. 

If you want to know more about Conditional formatting, you can go through the official documentation by clicking here.

See Also

Google Sheets: How to Use Pivot Tables: Learning how to create and use Pivot Tables for analysing data in Google Sheets.

Conditional Formatting in Google Sheets: Get started with Conditional Formatting in Google Sheets