In this article, I will provide a tutorial on how to create an employee salary report using the Excel program. Initially, to make employee salary reports, people usually use particular calculations. In this case, there are several things that we need to take into account. Some of these things are components such as allowances and taxes.
By using the Microsoft Excel formula, the employee salary calculation are much more easier. For us to better understand, I will explain this tutorial based on a simple case example, which is as below:
The data that will be entered are :
- Number
- Name
- Employment
- Main Salary
- Employees receive an allowance of 5% of their main salary
- Tax is obtained from 10% * (Main salary + Allowance)
- Receive net salary (net) is obtained based on (Main salary + Allowance) – Tax
Before starting, for those of you who still want to know the function of buttons on Microsoft Excel, you can read the explanation here Learn Functions of Excel Buttons.
We will solve the example case above using Microsoft Excel formulas. The steps are as follows :
1. Create Table
The first step we take is to create a table of 7 columns starting from Number, Name, Employee, Main Salary, Allowances, Taxes, Net Salary. Regarding the lines, we make as many as 11 lines. For more details, look at the image below;
2. Enter Formula for Allowances
In the example case instruction, the allowance is 5% of the main salary. For that, we need to multiply 5% by the main salary. The trick is to select cell F6 then type = 5% * E6 press Enter. The meaning of the math formula function is that we multiply 5% by cell E6. In this case, cell E6 is the first row for the main salary column.
After we press Enter, the calculation results for allowances will automatically appear. Then, for the row below, we don’t need to repeat the formula. We move the pointer to the lower right corner of cell F6, then the mouse pointer will change to a plus (+) sign drag the mouse pointer down to the last row in the data table that we want, as in the image below:
Then, after we drag the pointer down, the calculation results will automatically appear for the row below, as shown below:
3. Enter the formula for tax
In the instructions for the example case, Tax calculation from 10% * (main salary + allowance). It is necessary to enter the excel formula function to complete the formulas. That way, click cell G6, type = 10% (E6 + F6) then, press Enter on the keyboard. The meaning of addition * in a formula is the multiplication of 10% of the calculation result of cell E6 with cell F6.
After we press Enter, the calculation results for taxes will automatically appear. Then, for the row below, we don’t need to repeat the formula. Just pointing the mouse pointer to the lower right corner of cell G6, then after the pointer changes to +, drag the mouse pointer down to the last row in the table, as shown below:
Then, after we drag the pointer down it will automatically appear the calculation result for the row, as below screenshot shown:
4. Enter formula to receive net salary
In the instructions above, the net income is obtained based on (main salary + allowance) – Tax. Therefore we need to enter the excel formula function to solve the calculation. To do this, click cell H6, then type =(E6 + F6)-G6, then press Enter. The meaning of this formula is that we add cell E6 to F6 and then subtract cell G6.
After we press Enter, the calculation results will automatically appear to accept the net. Then, for the row below, we don’t need to repeat the formula. Just move the pointer to the lower right corner of cell H6, then after the pointer changes to +, drag the mouse pointer down to the last row in the table, as shown below:
Then, after we drag the pointer down, the calculation results will automatically appear for the row below, as shown below:
That’s how to make a salary report in excel that you can learn on your respective laptops. For those of you who want to know other formula functions on Microsoft Excel, you can read it here:
- How to Find Average, Maximize, and Minimize Score in Microsoft Excel 2016
- Taking Multiple Characters In Cell? Excel LEFT MID and RIGHT formulas
- Quick And Easy! How to Make Charts or Graph on Google Spreadsheets
Finish for a tutorial on how to create employee salary financial reports in Microsoft Excel. Thank you for visiting…
