Assignment 3 Conditional Formatting Highlight Details by Name, Starting letters or Gender

 

Conditional Formatting

Highlight Details by Name, Starting letters or Gender)

Function Use in Starting letter of name,Name of student and Gender

Left Function :- This Function is use to find the value from left hand side.

Right Function :- This Function is use to find the value from right hand side.

Len Function :- This Function is use to print value of a cell.

Find Function :- This Function is use to Find the position of the asked "text".

If Function :- If function will check entire function and give the answer true or false.

Function for Starting Letter of Name

Syntax

Function use in this criteria

=Left(Right($C3,Len($C3)-Find("Space",$C3)),1)=$A$16

Logics

Left Function :- Left function print the first word of right function value.

Len function :- Len gives the value of cell.

Find function :- find the space in the cell.

Symbol of "$" :- Dollar sign fixes the reference to a given cell.

Function for Name of Student

Function use in this criteria

Syntax

=Right($C3,Len($C3)-Find("Space",$C3))=$E$16

Logics

Right Function :- Right function find the value after "Space".

Len Function :- Give the Value of cell.

Find Function :- Find Function , Find space in the cell.

Symbol of "$" :- Dollar sign fixes the reference to a given cell.

Function for Gender

Function use in this criteria

Syntax

=If(Left($C3,3)=" Mr. " , " M " , " F " ) =$I$16

Logics

Left Function :- Left function print the first word of right function value.

If Function :- Check the entire function.

Symbol of "$" :- Dollar sign fixes the reference to a given cell.

Note :-

All Function are apply to conditional formatting, to apply this function follow these steps.

• Go to conditional formatting tool.

• Select the New Rule.....

• Select the :use a formula to determine which cell to format".

• Click on " Edit the Rule Description" then type the function.

• After that go to the Format and choose the options which you want. It all depend on you and click on "ok" and again click on "ok".

• Again open Conditional formatting tool and choose manage rules. then you can edit your function.