Conditional Formatting with the MIN function:

At the request of Kelly I show you how, the smallest number found in two or more columns can be given a different color.

The first thing we do is, to select the range in which we want to format the smallest number.
In this example, I select the columns, B and C.
It is important that you select the range from the top left corner to bottom right corner, because we will copy the formatting to the right and below.
Then click the “Conditional Formatting” button and select “New Rule” from the drop-down menu.

In the dialog box that appears, select the type “Use a formula to determine which cells to format” (1)
In the “Format values where this formula is true” text box, (2) type the following formula:
= B1 = MIN ($ B1: $ C1)

What does this mean?
When B1 is the smallest number in the row, which is row 1 in this case, and the condition is true, the formatting applied.
In this case, the MIN function is used, which determines the minimum value.
If you wish to color the maximum values, use the MAX function.

The column references in the range of the formula ($B1:$C1) are absolute, so we use the dollar sign, because we copy this formula to the right and down.
The row references are not absolute, for the same reason.
In other words, the column will always remain the same, whereas the row changes. It must be adapted.

Click the “Format” (3) button and select a format that you wish to use.
I have opted for a yellow fill color but, you can choose whatever you want.
Click the OK button in the “Cell Properties” dialog box when you’re done.
Then click the OK button in the “Layout Edit Rule” dialog box.

All cells with the minimum value are colored.

FYI: Swotster.com thinks of its students, so you also remember swotster.com

Copyright © 2013 – Swotster Ltd.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats