How to Highlight Highest Value in Excel (3 Quick Ways)

 

Method 1 – Use Conditional Formatting to Highlight the Highest Value in a Column in Excel

The sample database contains fruit prices in 2 columns and 8 rows. We’ll use Conditional Formatting to highlight the highest value in the range.

Dataset for Highlighting Highest Value in Excel

Steps:

  • Select the data range.
  • Go to Home, select Conditional Formatting, and choose New Rule.
  • A dialog box named New Formatting Rule will open up.

Using Conditional Formatting to Highlight Highest Value in Column in Excel

  • Choose Format only top or bottom ranked values from the Select a Rule Type box.
  • Put 1 in the box under the Format values that rank in the option.
  • Press the Format button.
  • A dialog box named Format Cells will appear.

Changing settings inside Opening New Formatting Rule Box

  • Choose your desired highlight color from the Fill tab and press OK.

Opening Format Cells Box to Select Color for Conditional Formatting

  • Press OK to close the Formatting Rule window.

Finalizing Format of Highest Value in a Column in Excel

  • The highest value is highlighted with green color.

Dataset After Highlighting Highest Value in Excel

Read More: How to Highlight Lowest Value in Excel


Method 2 – Apply Conditional Formatting to Highlight the Highest Value in Each Row in Excel

We’ll modify the dataset and use three columns to show prices for three consecutive months.

Steps:

  • Select all three columns.
  • Go to the Home tab, select Conditional Formatting, and choose New Rule.
  • A dialog box will appear.

Applying Conditional Formatting to Highlight Highest Values in Each Row

  • Choose Use a formula to determine which cells to format from the Select a Rule Type box.
  • Copy the following formula in the Format values where this formula is true box.
=C5=MAX($C5:$E5)
  • Press on the Format button. The Format Cells dialog box will open up.

Changing Settings Inside New Formatting Rule Box

  • Select your desired fill color and press OK. We have chosen the green color.

Selecting Color to Highlight Highest Values in Each Row

  • Press OK again.

Finalizing Format in New Formatting Rule Window

  • The highest value of each row is highlighted with green.

Dataset After Highlighting Highest Values in Each Row in Excel


Method 3 – Create an Excel Chart to Highlight the Highest Value in Excel

We have modified the sample dataset, adding two extra columns to represent the maximum value and the rest of the values.

Steps:

  • Copy the following formula in Cell D5
=IF(C5=MAX($C$5:$C$11),C5,"")
  • Hit the Enter button and use the Fill Handle tool to copy the formula to the other cells.

Using IF & MAX Functions to Get Highest Value in Excel

  • In Cell E5, insert the following formula:
=IF(D5="",C5,"")
  • Press Enter and use the Fill Handle tool to copy the formula for the other cells.
  • The maximum value and the rest of the values are separated into two columns.

Using IF Function for Rest of the Values in Excel

  • Press and hold the Ctrl key and select the Products, Max, and Rest columns.
  • Go to the Insert ribbon and click the arrow icon from the Charts bar.
  • A dialog box named Insert Chart will appear.

Creating Chart Using Insert Tab in Excel

  • Select the chart type you want from the Recommended Charts option and press OK.

Selecting Stacked Column from Recommended Charts

  • The chart shows the highest value in a different color.

Excel Chart After Highlighting Highest Values

Read More: How to Highlight Text in Excel


Practice Section

You will find an Excel worksheet in the download file which you can use to practice on different datasets.

Practice Section to Highlight Highest Value in Excel


Download the Practice Book


Related Articles


<< Go Back to Highlight in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo