Find the Max and Min value in a column

Hi,

I have a sheet with a single column that has a large range of numbers. I would like to use conditional formatting to highlight the highest number and the lowest number within this column so its easy to see when viewing the sheet.

I can use the functions Max() and Min() to find the highest and lowest values, but these results are in separate cells to the main column.

How do I then use these two results to conditional highlight the corresponding cells / rows?

Regards

Find more posts tagged with

Sheets

Formulas

Conditional Formatting

Comments

Rob Hagan

Hi Scott,

Let's assume that your helper column is called "Helper" and your main column is called "Main".

Let's also assume that your Helper column contains a formula like =IF(MAX(Main:Main) = Main<n>, "Max", IF(MIN(Main:Main) = Main<n>, "Min", "")) in every row (where <n> is the row number). This will then show the text "Max" next to all values that are equal to the largest (there may be more than one) and likewise "Min" for those that are equal to the smallest.

Given the above, then the conditional format that you need would look like:

If Helper is "Max" the apply <format for the max colour> to the Main column

If Helper is "Min" the apply <format for the min colour> to the Main column

(and you fill in the bits which I've bolded above).

The conditional format is clever in that it allows you to test one column and format a different column.

Cheers,

Rob.

Scott Lynch

Hi Rob,

Thank you very much indeed, that tip has worked perfectly.

Smartsheet need to add Max Value and Min Value within their conditional formatting custom criteria list.

Thank you again for the help.

Scott

Kenyon Bajus

Ditto what @Scott Lynch wrote- Smartsheet needs to add this. We shouldn't need to waste time or sheet space with an additional column. Missing basic features we have in Excel is fristrating.