Accentuating cell values with “Conditional formatting” and “Formulas”.

In a series of data, to highlight the minimum and maximum values, we can use “Conditional formatting” with formulas.

In the example file here , you can download the dates with the lowest and highest total births in a single color.

Select cells A3 to D60, click on “Conditional Formatting” (1) in the “Ribbon” and select “Manage Rules” (2).

In the “Conditional Formatting Rules Manager” click on “New Rule”.

In the “New Formatting Rule”, select “Use a formula to determine which cells are formatted” (1) and as formula, (2) enter, = $ D3 = MAX ($ D $ 3: $ D $ 60)
Then click on “Format” button(3).

In “Format Cells”, select a color (or you can experiment with the “Fill Effects”) and click OK.

Again, select “New Rule”.
In the “New Formatting Rule” dialog box, re-select “Use a formula to determine which cells are formatted” and enter your formula = $ D3 = MIN ($ D $ 3: $ D $ 60).
Then click again on “Format”.
In the “Format Cells” dialogbox, select a color and click OK.
Then click “Apply and” OK “.

The MAX function returns the largest value in a set of values.
The MIN function returns the smallest value in a set of values.

The formula = $ D3 = MAX ($ D $ 3: $ D $ 60) used in conjunction with “Conditional formatting” operates as follows: If the value of the cell $ D3 is equal to the largest value of the cell area $ D $ 3: $ D $ 60, adjust the formatting for the selected cells = $ A $ 3: $ D $ 60 (We select the cells $ A $ 3 to $ D $ 60 for the format(1), and the condition (2) is applied to the cells, $ D $ 3 to $ D $ 60).

What about the cells D4 to D60? However, in the formula = $ D3, because we use the absolute column reference and relative row, reference. Excel automatically adjusts the formula for that cell. (See Relative and absolute cell references Excel 2007 Course, Lesson 27)

Featured Courses

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