Step by Step Guide for Beginners - How to use Excel Formula?

EXCEL WORKBOOK
Chapter 1
Day 1

MICROSOFT EXCEL

SESSION - 1

Open excel have a look at the excel program window and get familiar with various components of the  excel

a window such as "Title Bar", "Formula Bar", "Status Bar", "Task Bar" and finally the "Workbook", "Ribbon"

and "Quick access tools bar"

1) Total rows & columns in the excel sheet.

 2) Go to the following cells without scrolling

     a) A6528 

     b) IU626 

     c) Go to the first and the last row 

     d) Go to the first and last column 

 3) Create the following table. 

↣ Salary Sheet!

Employee ID	Name Of Employees	Salary
Employee 01	Sanjay Gupta	INR              10,500.00
Employee 02	Sonu K	INR                9,800.00
Employee 03	Roshan Kumar	INR                8,800.00
Employee 04	Arti Tiwari	INR                8,700.00
Employee 05	Sudhanshu Desai	INR                8,600.00
Employee 06	Satender Jain	INR                7,500.00
Employee 07	Satish Sood	INR                6,600.00
Employee 08	Satveer Powar	INR                5,500.00
Employee 09	Shambhu Nath	INR                5,500.00
Employee 10	Suraj Kumar	INR                5,000.00

The Shortcut Keys - This Table.

Alt + Enter = To Start Multiple Line In A Single Cell.

Alt + O,C,A = To Best Fit (Column)

Alt + O,R,A = To Best Fit (Raw)

Ctrl + Space = To Column Select

Shift + Space = To Raw Select

Ctrl + Shift + Arrow Keys = Select Range Towards Arrow Key.

Alt + I + C = Insert Column

Alt + I + R = Insert Raw

Ctrl + (+) = Insert (Raw & Column)

Ctrl + (-) = Delete (Raw & Column)

Ctrl + A/Ctrl + * = To Select Whole Table

Alt + O + C + I + W = To High & Down Column Width

Alt + H + O + H = To High & Down Raw Height

Alt + H + H = To Start Column Color

To Shortcut Keys Of this Table

Alt + H + L + H + G = Format Cells That Are GREATER THAN

Alt + H + L + H + L = Format Cells That Are LESS THAN

Ctrl + D = To Start Full Fill (DA/HRA/CA) Salary Sheet

Alt + H + F + M = To Start Open The Format Cell Page

Alt + H + F + P = To Start Copy Formatting From One Place And Apply It To Another

FORMULA'S OF THIS TABLE

1 DA(Dearness Allowance) Is 34% Of Basic Salary

Formula] =Basic Salary * 34%

2 HRA(House Rent Allowance) Is 20% Of Basic Salary

Formula] =Basic Salary * 20%

3 CA(Conveyance Allowance) Is 20% For Those Who's

Basic Salary Is More Than 6500 Otherwise 15%

Formula] =IF(Basic Salary>6500,Basic Salary*20%,Basic Salary*15%

Formula] =IF(Logical Test, (Value If True), Value If False)

1st Name	Lst Name	Full Name	With " & "	1st Name	Lst Name	Upper	Lower	Proper
Narendra	Modi	Narendra Modi	Narendra Modi	Narendra	Modi	NARENDRA MODI	narendra modi	Narendra Modi
Virat	Kohli	Virat Kohli	Virat Kohli	Virat	Kohli	VIRAT KOHLI	virat kohli	Virat Kohli
Ms	Dhoni	Ms Dhoni	Ms Dhoni	Ms	Dhoni	MS DHONI	ms dhoni	Ms Dhoni
Kapil	Dev	Kapil Dev	Kapil Dev	Kapil	Dev	KAPIL DEV	kapil dev	Kapil Dev
Sanjay	Dutt	Sanjay Dutt	Sanjay Dutt	Sanjay	Dutt	SANJAY DUTT	sanjay dutt	Sanjay Dutt
Akshay	Kumar	Akshay Kumar	Akshay Kumar	Akshay	Kumar	AKSHAY KUMAR	akshay kumar	Akshay Kumar
Jaqline	Farnandis	Jaqline Farnandis	Jaqline Farnandis	Jaqline	Farnandis	JAQLINE FARNANDIS	jaqline farnandis	Jaqline Farnandis
Mohit	Raina	Mohit Raina	Mohit Raina	Mohit	Raina	MOHIT RAINA	mohit raina	Mohit Raina
Rohit	Sharma	Rohit Sharma	Rohit Sharma	Rohit	Sharma	ROHIT SHARMA	rohit sharma	Rohit Sharma
Pol	Science	Pol  Science	Pol  Science	Pol	Science	POL  SCIENCE	pol  science	Pol  Science
Rajesh	Kumar	Rajesh Kumar	Rajesh Kumar	Rajesh	Kumar	RAJESH KUMAR	rajesh kumar	Rajesh Kumar
Shakti	Maan	Shakti Maan	Shakti Maan	Shakti	Maan	SHAKTI MAAN	shakti maan	Shakti Maan
Samrat	Ashok	Samrat Ashok	Samrat Ashok	Samrat	Ashok	SAMRAT ASHOK	samrat ashok	Samrat Ashok
MBA	English	MBA English	MBA English	MBA	English	MBA ENGLISH	mba english	Mba English
LLB	Advocate	LLB Advocate	LLB Advocate	LLB	Advocate	LLB ADVOCATE	llb advocate	Llb Advocate
Jolly	LLB	Jolly LLB	Jolly LLB	Jolly	LLB	JOLLY LLB	jolly llb	Jolly Llb
Chandni	Chowk	Chandni Chowk	Chandni Chowk	Chandni	Chowk	CHANDNI CHOWK	chandni chowk	Chandni Chowk
Badarpur	Sbway	Badarpur Sbway	Badarpur Sbway	Badarpur	Sbway	BADARPUR SBWAY	badarpur sbway	Badarpur Sbway
Electicity	Bill	Electicity Bill	Electicity Bill	Electicity	Bill	ELECTICITY BILL	electicity bill	Electicity Bill
Water	Bill	Water Bill	Water Bill	Water	Bill	WATER BILL	water bill	Water Bill
Educat.	Center	Educat. Center	Educat. Center	Educat.	Center	EDUCAT. CENTER	educat. center	Educat. Center
Pizza	Off49Rs.	Pizza Off49Rs.	Pizza Off49Rs.	Pizza	Off49Rs.	PIZZA OFF49RS.	pizza off49rs.	Pizza Off49Rs.
Limka	Cheeld	Limka Cheeld	Limka Cheeld	Limka	Cheeld	LIMKA CHEELD	limka cheeld	Limka Cheeld
Hundred	Rupees	Hundred Rupees	Hundred Rupees	Hundred	Rupees	HUNDRED RUPEES	hundred rupees	Hundred Rupees
Zarin	Khan	Zarin Khan	Zarin Khan	Zarin	Khan	ZARIN KHAN	zarin khan	Zarin Khan

↠ FORMULA'S OF THIS TABLE
↠	Marge First Name And Last Name With CONCATENATE Condition.
↠	Marge First Name And Last Name With " & " Condition.
		Formula] =CONCATENATE(First Name," ",Last Name)
		Formula] =First Name&" "&Last Name
↠	Separate First Name With LEFT And FIND Condition.
		Formula] =LEFT(Full Name,FIND(" ",Full Name)-1)
↠	Separate Last Name With RIGHT , LEN And FIND Condition.
		Formula] =RIGHT(Full Name,LEN(Full Name)-FIND(" ",Full Name))
↠	Change The Text Of First Name In UPPER CASE, LOWER CASE, And PROPER CASE And See The Difference.
		Formula] =UPPER(Full Name)
		Formula] =LOWER(Full Name)
		Formula] =PROPER(Full Name)