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 example, we can highlight all cells whose value is above 10, or highlight the cell with the lowest value using conditional formatting in Google Sheets. If you want to explore more about 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 cell with the smallest number, it will highlight the student with the minimum mark.

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

Steps to 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 to highlight the highest value
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 to highlight highest value
Figure 3: Select Conditional Formatting

Step 3: Go To Single Colour → Apply To Range

  • After opening conditional formatting, navigate to the Single Colour tab.
  • Make sure that the range in the “Apply to range” is the same as the range you selected.
Verify the range to highlight the highest value
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 to highlight the highest value in Google Sheets
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 highlighted:
 The highest value is highlighted in Google Sheets
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 to highlight the lowest value in Google Sheets
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 to highlight the lowest value in Google Sheets
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 to apply the formula to highlight the highest and lowest value in Google Sheets
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 to highlight the lowest value in Google Sheets
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

X
Hire expert data analysts on-demand. Get 30 Days Free Trial