Conditional Formatting

Lesson Plan: Unit - 02 Microsoft Office

Subject: BELE2CFA: Computer Fundamentals And Applications
Topic of Study: Formatting worksheet using condition
Grade/Level: Bachelor of SCIENCE

Objective: To demonstrate and explain formatting of worksheet based different kinds of condition such as less than, greater than, between...

Time Allotment: 30 Minutes

17.0 Conditional Formatting

• It is a special features to formatting data / cells value based upon given condition.

• Example:
• Apply a red color on those value which are less than 33.
• Apply a green color on those value which are greater than 100.

17.0.1 Write following data in MS-Excel's cell.

17.0.2 How to do total of all subjects.

• Set the cursor in E2 i.e. address of Nayan's total.

• Write a following formula in it with = sign.

• Select the cell again and drag &drop with small right bottom square button of selected cell.

17.0.3 How to do percentage of subjects

• Set the cursor in F2 i.e. address of Nayan's percentage.
• Write a following formula with = sign in it.

• Select the cell again and Drag & drop with small right bottom square button of selected cell.

17.0.4 Formatting condition - greater than

• Example: Here, we are going to highlight those cell which have greater than 100 marks in any subjects.
• Step 01: Select the required cells.

• Step 02: Go to Home menu / ribbon > Conditional formatting.
• Step 03: Highlight Cell rules > Greater than option.

• Step 04: Set the parameter & formatting option.

17.0.5 Formatting condition - less than

• Example: Here, we are going to highlight those cell which have less than 33 marks in any subjects.
• Step 01: Select the required cells.

• Step 02: Go to Home menu / ribbon > Conditional formatting.
• Step 03: Highlight Cell rules > Less than option.

• Step 04: Set the parameter & formatting option.

17.0.6 Formatting condition - Clear Rules

• Example: Lets remove all the rules from cells.
• Step 01: Select the required cells.

• Step 02: Click on Conditional Formatting > Clear Rules and Select Clear Rules from Selected Cells.

• Step 03: Final output like this.