To highlight the max value in a row in Google Sheets is useful to make the cell with the highest value in a row easier to identify.
Table of Contents
Google Sheets has a built-in feature for formatting cells in your sheets based on whether they meet certain criteria.
It’s called conditional formatting and is an extremely useful visual tool. It makes it much easier to gain some insight into the data just by glancing at the sheet.
For example, say we have a sheet that contains information about the number of people visiting a museum each day. 🏛
We would like to highlight and see easily the days where we had the most visitors of the week. We want to highlight the cells with the highest values in the row by giving a special colour background to them.
So how do we do that?
Easy. We can apply some simple dynamic conditional formatting rules and set a style of our choosing.
Now, let’s jump right into it to see how to highlight the max value in a row in Google Sheets.
How to Use Conditional Formatting
Conditional formatting is a very powerful way to quickly visualize your sheet content.
You can access conditional formatting by going to the menu and select Format > Conditional Formatting. The following toolbar will open:
This is where you can set the rules that will take care of the dynamic formatting of your cells.
In general, every rule you can set is an if-then statement. For example, if the cell’s content is greater than 10, then the background colour should change to red.
If the rule you set is evaluated as TRUE to a cell, then its appearance will be changed according to the style you choose.
So there are three basic things you need to set:
- Range: where would you like to use the conditional formatting in the sheet.
- Condition: what is the criteria that your cells should meet in order to format them.
- Style: which style should Google Sheets give the cells that meet the criteria.
There are some default conditions you can choose from, including criteria for both text values and numeric values.
For other cases that are not part of this list, you can write your own condition by selecting ‘Custom formula is’ in the drop-down list of ‘Format cells if’. Here you can create similar formulas as when working with functions in the sheets.
In this guide, we will show you how to write a custom formula to highlight the max value in a row in Google Sheets.
A Real Example of Using Conditional Formatting to Highlight the Max Value in a Row
Now let’s see how this works with real examples and how we can use conditional formatting to highlight cells based on our criteria.
So let’s get back to our museum!
The picture below shows the number of visitors in the museum each day.
We used conditional formatting to highlight the max value in each row with an orange background. This way we can easily see the days that had the highest number of visitors each week.
Since the list of basic conditions doesn’t include what we wanted to do, we used a custom formula to format the highest values.
The formulas we use for conditional formatting work similarly to the formulas we write in the spreadsheets. But they are somewhat different in the way we write them.
In a spreadsheet formula, we would use the
MAX function to return the highest number in a row. The same works in the formula of conditional formatting too, we just need to adjust the syntax a bit.
The following custom formula will highlight the max value in a row:
Let’s see what this custom formula does:
- First, we set our first row to start the formatting. Our first row is in range B2:H2.
- We put an equal sign ‘=‘ between the cell reference and the condition.
- Then, we wrote our custom formula. We used the
MAXfunction, similarly to the spreadsheet function, to calculate the highest value in the row. This is our condition that will be evaluated as TRUE or FALSE to each cell.
- The reference in the
MAXfunction is the same row, so B2:H2. The function returns the max (highest) number in the referenced row.
- We need to add a dollar sign ‘$‘ in front of the range reference. By doing that we apply the formula using absolute references instead of relative references. This means that while the condition will be applied to the whole range and will go row by row, its column references will remain the same.
- The conditional formatting then goes ahead and does the same formatting to the rest of the rows in our selected range, which here means until the 11th row.
Finally, we selected an orange background for the cells that meet this condition.
This is how our sheet is using conditional formatting and makes the highest number in each row visible with the orange background.
You can play around with it by yourself by making a copy of the spreadsheet using the link I have attached below:
How to Highlight the Max Value in a Row in Google Sheets
Let’s begin setting your own conditional formatting to highlight the max value in a row in Google Sheets.
- To start, select the range where you would like to apply conditional formatting. This should include all the data set where your values are. For this guide, I’m choosing the range B2:H11.
- Then, select Format from the upper menu and choose Conditional formatting. This will open the panel of conditional formatting where you can set your desired rules and styles.
- Now you should see the range you selected in ‘Apply to range’. Below that, you should set the rule. To write your own rule, choose the ‘Custom formula is’ option from the drop-down list as we did it in this example.
- After that, write the rule in the ‘Value or formula’ field. The formula should be written for one row and then it will be applied generally to the whole range. In our example, the formula to highlight the max value in a row is =B2:H2=MAX($B2:H2).
- Almost done! Now select the style you wish for your conditionally formatted cells. For example, you may change the font, the font style, the colour and the background colour. Here I’m changing the background colour to orange.
- Finally, click on ‘Done’ to close it. As a result, our highest values in each row are orange now. If you followed the steps, your cells that meet your criteria should now have your chosen style.
That’s it, well done! You can now highlight the max value in a row with conditional formatting together with the various other Google Sheets formulas to create even more useful formulas. 🙂