How to Highlight the Highest Value in Google Sheets

Google Sheets may not be as advanced as Excel, but it offers a very approachable alternative to Microsoft’s spreadsheet tool, and also happens to be free to use. As part of the Google Drive suite, Google Sheets can be used to create, edit, and share spreadsheets.

It can be used in any browser and the created spreadsheets are compatible with Microsoft Excel. Even as a more simplistic web-based version of Excel, Google Sheets still allows you to manipulate data in various ways, including applying unique formatting to different sets of values.

What Is Conditional Formatting?

Conditional formatting is a feature in Google Sheets that allows you to apply customized formatting to various data sets. This is done by creating or using existing conditional formatting rules. One of the most common uses of this feature is highlighting specific values in a spreadsheet for easier identification.

Conditional Formatting for Highest Value

1. Click ‘Format’.
   
2. Select ‘Conditional Formatting’.
   
3. Go to the ‘Single Color’ tab under the ‘Conditional Format Rules’ menu.
   
4. Click on the table icon located under the ‘Apply to range’ tab.
   This allows you to select the column from which you want to highlight the highest value. When done, click “OK”.
   
5. In the ‘Format cells if’ dropdown list, select the ‘Custom formula is’ option.
   
6. Use the following formula ‘=$B:$B=max(B:B)’. Click “Done”
   B stands for the column you want to search for the highest value.
   

That’s all nice and easy, but what if you need more than highlighting the highest value. What if you need to see more values, say the top three of five values? You can make use of conditional formatting method to do this. This method involves using the same path but a different formula.

1. Click ‘Format’.
   
2. Select ‘Conditional Formatting’.
   
3. Go to the ‘Single Color’ tab under the ‘Conditional Format Rules’ menu.
   
4. Click on the table icon located under the ‘Apply to range’ tab.
   
5. When the ‘Format cells if’ list drops down, select the ‘Custom formula is’ option.
   
6. Use this formula instead of the previous one ‘=$B1>=large($B$1:$B,3)’
   

What this formula does is highlight the top three values from column B. Replace B with any other column letter you wish.

Conditional Formatting for Lowest Value

No matter what data you’re looking at, when you want to find the highs it also pays off to look at the lows in order to better understand the data sheet.

Conditional formatting can be used to highlight the low values too, if you use the right formula.

Follow the previously mentioned steps to reach the ‘Custom formula is’ option. Type the following formula ‘=$B:$B=min(B:B)’. If you want to highlight the lowest N values then modify the formula from the previous example ‘=$B1>=large($B$1:$B,3)’ which highlights the three highest to ‘=$B1<=small($B$1:$B,3)’.

Formatting Options

You’re also in charge of how you want the values highlighted in your spreadsheet. After giving the conditional formatting formula parameters, you can choose a custom formatting style and change the appearance of the text.

You can bold it, make it italic, underline it, and even change the color. After customizing the font, click done in order to start the function and highlight the values you’re looking for.

What Can You Use Conditional Formatting for?

Conditional formatting can be used with a variety of custom formulas. You can also highlight high values under a certain threshold. For example, you can use conditional formatting to show who scored under a certain percentage on a test.

Example for Highlighting Grades

1. Open a test score spreadsheet.
   
2. Click on ‘Format’ then on ‘Conditional formatting’.
   
3. Click on the table icon located under the ‘Apply to range’ tab to select cell range.
   
4. Select ‘Less than’ under the ‘Format cells if’ tab.
   
5. Check for any existing rule.
6. If one exists, click on it, it not, click on ‘Add new rule’.
   
7. Then add ‘Less than’.
   
8. Click on the ‘Value or formula’ option.
   
9. Enter 0.8, 0.6, 0.7 etc. in order to highlight values under 80%, 60%, 70%.
   

This particular formula should be very useful to teachers or even students that wish to know the percentile in which they’ve scored.

Other areas to which you can apply conditional formatting include sales, purchasing, and pretty much any other area where you may need to filter data.

Using Third-Party Apps

If you find that Google Sheets is not complex enough for you, you can ratchet things up a notch by using third party apps or extensions that let you make full use of spreadsheets. An app like Power Tools will let you use a function similar to the Autosum feature in Excel.

What is Autosum? It’s an Excel function which allows you to get the sum of different rows. Google Sheets only lets you do this for individual rows, one at a time. Although you may not need Power Tools or similar to highlight the highest value(s) in a spreadsheet, it’s good to know that you can get more out of this web-based app than meets the eye.

Excel the Easy Way

If you can’t afford to use Microsoft Office, Google Sheets has you covered for most of your spreadsheet needs. Although most companies don’t use the web-based app, preferring a more professional solution, a lot of freelancers and regular users turn to Google Sheets to record and analyze data.

Let us know how often do you turn to Google Sheets to handle information and how well versed you are in Google Sheet functions? Many people claim that they’re a bit difficult to learn. Do you agree?