Highlight the highest / lowest value in each row or column - 2 easy ways

When dealing with large data tables in Excel, it can be helpful to visually identify the highest or lowest values in each row or column. Instead of scanning the data manually, you can use Excel's built-in features or third-party tools to highlight these values automatically. This tutorial introduces two simple methods: one using Conditional Formatting and the other using Kutools AI Aide.

Use conditional formatting to highlight values

• Highlight highest or lowest values in each row
• Highlight highest or lowest values in each column

Use Kutools AI Aide for smarter highlighting

Use conditional formatting to highlight values

Conditional Formatting is a powerful feature in Excel that lets you apply formatting rules to cells based on their content. With the right formulas, you can use it to highlight the highest or lowest value in each row or column. It's a built-in tool that's dynamic—meaning the highlights will update automatically as the data changes.

Highlight highest or lowest values in each row

1. Select the range of cells where you want to highlight the highest or lowest values in each row.
2. Then, click "Home" > "Conditional Formatting" > "New Rule".
   
3. In the "New Formatting Rule" dialog box:
   1. Select "Use a formula to determine which cells to format";
   2. Enter one of the following formulas depending on your need:
      ● For highlighting the highest value in each row:
      

      =B2=MAX($B2:$E2)

      ● For highlighting the lowest value in each row:
      

      =B2=MIN($B2:$E2)

   3. Click "Format".

      

      📝 Note: In the above formula, "B2" indicates the first cell of your selected range. "B2:E2" is the first row that you want to highlight the largest or smallest cell. You can change them to your need.
   4. In the "Format Cells" dialog box, choose a color under the "Fill" tab, and click OK.

      

4. Confirm the dialog boxes by clicking "OK" again. And you will see the largest or smallest value has been highlighted in each row.
   

   Highlight the highest value in each row:	Highlight the lowest value in each row:

Highlight highest or lowest values in each column

To highlight the highest or lowest value in each column, the approach is similar but requires adjusting the formula to work vertically:

1. Select the cell range across multiple columns (e.g., B2:E10).
2. Then, click "Home" > "Conditional Formatting" > "New Rule".
   
3. In the "New Formatting Rule" dialog box:
   1. Select "Use a formula to determine which cells to format".
   2. Enter one of the following formulas depending on your need:
      ● For highlighting the highest value in each column:

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

      ● For highlighting the lowest value in each column:

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

      📝 Note: "B2" indicates the first cell of your selection, and "B2:B10" is the first column range that you want to highlight the largest or lowest value. You can change them to your need.
   3. Click "Format" to choose a color under the "Fill" tab.
   4. Click "OK" to apply the rule.

      

After applying the Conditional Formatting rule, you will get the results as shown in the following screenshots:

Highlight the highest value in each column:	Highlight the lowest value in each column:

Use Kutools AI Aide for smarter highlighting

If you prefer a quicker, no-formula method, Kutools for Excel offers an AI-powered tool that makes it even easier to highlight the highest or lowest values. This feature leverages natural language commands to automatically format your data—no coding or rule-setting required.

After installing Kutools for Excel, please click "Kutools AI" > "AI Aide" to open the "Kutools AI Aide" pane:

1. Select the data range, then type your requirement into the chat box, and click "Send"button or press "Enter" key to send the question;
   Highlight the largest values in each row of the selection with light blue color.
2. After analyzing, click "Execute" button to run. "Kutools AI Aide" will process your request using AI and highlight the largest value in each row directly in Excel.
   

• To highlight the lowest value in each row or highest / lowest value in each column, you just need to modify the command to your need. For example, use commands like "Highlight the lowest value in each row of the selection with light red color"
• This method does not support the undo function. However, if you wish to restore the original data, you can click Unsatisfied to revert the changes.

Highlighting the highest or lowest values in Excel helps you spot key insights instantly. Whether you choose to use Conditional Formatting or the faster Kutools AI Aide, both methods can improve your data analysis workflow. If you’re comfortable with formulas, Conditional Formatting offers flexibility. But if you value speed and simplicity, Kutools AI Aide is a smart, time-saving option worth trying.

Related Articles: