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?
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.
Step 2: Select Format → Conditional Formatting
- Navigate to the top menu.
- Select Format.
- 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.
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.
- 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.
- The final output is a cell with the highest value 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.
Step 2: Select Format → Conditional Formatting
- Navigate to the top menu.
- Select Format.
- 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.
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.
- Use this formula to highlight the highest value in the selected range:
=B2=MIN($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.
- The final output is a cell with the highest value is 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