Excel Salary Sheet and formula
Excel salary statement includes employees basic pay,house Rent,
Medical allowance, Convince Allowances, Income Tax and some other allowances.
this Method i have applied in this salary statement is very basic and comprehensive.
Excel gives us the flexibility to configure data for different objectives like “SUM”, “AVERAGE”, “Multiplication”,”Subtraction”.
I am going to explain excel salary statement with complete detail this post
1st of all we will write Serial Number, Employee ID,Employees Name,
Designation, Basic Pay, House Rent, Medical Allowance, Convenience Allowance,
Gross Pay, Income Tax and Net Pay.In serial Number, Write 1 and drag from the write corner,
Press control Button Key of Keyboard with the mouse right button.
Employee ID Which is provided by the company.names of the employees and their designation. Basic pay is without formula, all the allowances have “IF” Formula.
its format is =IF(E3>=30000,E3*20%,IF(E3>=20000,E3*15%,IF(E3>=15000,E3*10%,IF(E3>=10000,E3*7%,E3*5%)))),
while E3 is the excel sheet location E is the Column and 3 is the spreadsheet row Number.
you can provide different percent but the formula structure shouldn’t be changed.the last 5% means all those whose basic salary below 10000 must be applied by 5%.
Again apply this formula in all other allowances excluding income tax but the percentage could be different.
In Gross pay we will add Basic pay with all the allowances. Income tax formula structure is the same one,only there will be the percentage differ which depend upon every country Tax ratio. here you can also watch the video.
After all these in the Net Pay subtract income tax from The Gross Pay.
in the bottom of each column apply “SUM” formula one by one. create Border from the border menu top Navigation.
Also provide different background color and font color to the Heading of the salary statement.
then make it save.this statement is one of the basic format, you can include some other allowances, that totally depend upon the organization or company, what they want to do.