MS Excel Full Tutorial By Abdulhanan

Introduction

• Excel is used for Spreadsheet and Formulization
• Excel Extension File : (.xlsx)
• Working Area : Sheet
• Excel Columns (XFD)
• Excel Rows (1048576)
• Open Method :
  1. Start Button >> Type >> "excel"
  2. Press Window + R >> Type >> Excel

Tools that Make Responsive

1. Border 

If we want to add border so we can add through clicking on arrow of border icon and then select border type ! Border arrow is near the highlight option in Home Tab.

2. Font 

There is an Font section in Home tab. There is many tools of customizing Font and Sheet !

Rules to add Formulas

It is important that we have to add "= (is equal mark)" before adding formula !

Sheets

• Result Sheet
• Daily Wadges
• Attendence Sheet
• Salary Sheet
• Salary Slip
• Table of Content

Result Sheet

Steps to Create :

• Select Some Cells and Merge them
• Write "RESULT SHEET" in the merged Cell and add thick box border
• Type Options writed below on No 1
• Select cells and add " All Border " 
• Add formulas writed below on No 2

No 1. Options in Result Sheet :

• SNO
• NAME
• FATHER NAME
• SUBJECTS
• TOTAL MARKS
• OBTAIN MARKS
• PERCENTAGE
• REMARKS
• GRADE

No 2. Formulas

• SNO [ =if(name cell="","",1) ]
• SNO Formula 2 in below cell [ =if(name cell="","",up cell+1) ]
• Name [No Formula]
• Father name [No Formula]
• Subjects [ No Formula ]
• Total Marks [ Depend you ]
• Obtain Marks [ =sum(drag all subject cells)
• Percentage [ =(obtain marks cell/total marks cell*100)
• Remarks [ =if(percentage cell>=90,"PASS","FAIL")
• Grade [ =if(percentage cell>=95,"A+",=if(percentage cell>=90,"A",=if(percentage cell>=85,"B",=if(percentage cell>=80,"C",=if(percentage cell>=75,"D",=if(percentage cell>=70,"E","FAIL" {count the brackets} ))))))

Daily Wadges

Steps to Create :

• Select Some Cells and Merge them
• Write "Daily Wadges" in the merged Cell and add thick box border
• Type Options writed below on No 1
• Select cells and add " All Border " 
• Add formulas writed below on No 2

No 1. Options in Result Sheet :

• SNO
• Employee No
• Name
• Designation
• Per Day Salary
• Leaves
• Working Days in Month
• Total Amount
• [ Overtime =
• Hours 
• Amount
• Total Amount ]
• [ Deduction = 
• Fine
• Fund
• Other 
• Total Deduction ]
• Net Pay
• Signature
• Remarks

No 2. Formulas

• SNO [ =if(employee cell="","",1) ]
• SNO Formula 2 in below cell [ =if(employee cell="","",up cell+1) ]
• Name [No Formula]
• Designation [ Go to > Data Tab > Data Validation > Select List > Add Designation for example = G.M,ACCOUNTANT,HELPER,GUARD,A.M,MANAGER,OFFICE BOY etc.
• Per Day Salary [ Depend You ]
• Working Days in Month [ =31-leave cell ]
• Total Amount [ =Per day salary cell * working days in month cell ]
• Hours [ Depend You ]
• Amount [ =totalamount/hours ]
• Total Amount [ =SUM(total amount,overtime amount)
• Deduction Fine  [ =if(and(perdaysalary>500,leaves>3),perdaysalary/2*(leaves-2),0) ]
• Deduction Fund [ =overtime totalamount*7/100 ]
• Deduction Other [ Depend You ]
• Deduction Total deduction [ =sum(fine,other) ]
• Netpay [ =(overtime total amount+fund-total deduction) ]
• Signature [ Depend You ]
• Remarks [ =IF(X15>=100000,"G.M",IF(X15>=90000,"ACCOUNTANT",IF(X15>=80000,"OFFICE BOY",IF(X15>=80000,"A.M",IF(X15>=70000,"MANAGER",IF(X15>=60000,"HELPER",IF(>=50000,"GUARD"))))))) ]     X15 MEANS TOTAL AMOUNT OF OVERTIME