Advance Excel (Conditional Formatting)

CONDITIONAL FORMATTING

INTRODUCTION

Conditional formatting tool is an important feature of Microsoft Excel and it is used to change the format of selected content by a given condition.

For example, we can highlight all numbers which are less than 33 (<33) or name which ends with "Kumar" by using conditional formatting tool.


Example 1: Highlight all the marks, which "<33"

Step 1: Select the marks.

Step 2: Click on "Home" menu.

Step 3: Click on "Conditional Formatting" tool.

Step 4: Click on "Highlight Cells Rules"

Step 5: Click on "Less Than"

Step 6: Enter 33 in the "Format cells that are LESS THAN" box.

Step 7: Select the format color.

Step 8: Click on "OK" button.

Example 2: Highlight all the names which contain "Kumar" as the last name.

Step 1: Select the marks.

Step 2: Click on "Home" menu.

Step 3: Click on "Conditional Formatting" tool.

Step 4: Click on "Highlight Cells Rules"

Step 5: Click on "Text that Contains"

Step 6: Enter "Kumar" in the "Format cells that contain the text" box.

Step 7: Select the format color.

Step 8: Click on "OK" button.

Example 3: Highlight duplicate values. 

Step 1: Select the data.

Step 2: Click on "Home" menu.

Step 3: Click on "Conditional Formatting" tool.

Step 4: Click on "Highlight Cells Rules"

Step 5: Click on "Duplicate Values"

Step 6: Select the format color.

Step 7: Click on "OK" button.

TOP BOTTOM RULES

The top-bottom feature is used to find the number of largest and smallest values from selected cell range.

Example 1: For example, we want to find top three students, those mark's total is largest in order First, Second and Third.

Step 1: Select total of marks.

Step 2: Click on "Home" menu.

Step 3: Click on "Conditional Formatting" tool.

Step 4: Click on "Top/Bottom Rules"

Step 5: Click on "Top 10 Items"

Step 6: By default its show digit 10 as the number, change it, to digit 3.

Step 7: Select the format and click on "OK" button.

Example 2: In this example, we will find lowest one, which salesman earned lowest sales in the year with the help of conditional formatting tool.

Step 1: Select total of marks.

Step 2: Click on "Home" menu.

Step 3: Click on "Conditional Formatting" tool.

Step 4: Click on "Top/Bottom Rules"

Step 5: Click on "Bottom 10 Items"

Step 6: By default its show digit 10 as the number, change it, to digit 1.

Step 7: Select the format and click on "OK" button.

HOME