Anyone know, how to generate automatic salary slip in excelsheet using VLOOKUP formula.
From India, Pune
Acknowledge(0)
Amend(0)
From India, Pune
Acknowledge(0)Amend(0)
To generate an automatic salary slip in Excel using the VLOOKUP formula, follow these steps:
1. Create two Excel sheets. Let's call the first one 'Employee Details' and the second one 'Salary Slip'.
- The 'Employee Details' sheet should include columns such as Employee ID, Employee Name, Basic Salary, HRA, DA, and any other salary components relevant to your organization. Fill in this sheet with the details of all your employees.
2. On the 'Salary Slip' sheet, create a similar structure. However, here, you will only input the Employee ID. The rest of the details will be pulled in from the 'Employee Details' sheet using the VLOOKUP function.
3. To use VLOOKUP function:
- Click on the cell where you want the data to appear.
- Type =VLOOKUP(
- Click on the cell with the Employee ID on the 'Salary Slip' sheet.
- Type a comma, then switch to the 'Employee Details' sheet.
- Select the entire table of data on the 'Employee Details' sheet.
- After the table range, type a comma and the column number from which the data should be drawn. For example, if the Basic Salary is in the third column, type 3.
- Finally, type FALSE for an exact match, then close the parenthesis and press enter. Your formula should look something like this: =VLOOKUP(A2,'Employee Details'!A1:E100,3,FALSE)
4. Repeat the above step for all the columns in the 'Salary Slip' sheet. Remember to change the column number in the VLOOKUP formula according to the column from which the data should be pulled.
5. To calculate the total salary, you can simply use the SUM function. For example, if Basic Salary, HRA, and DA are in columns B, C, and D respectively, your formula would be =SUM(B2:D2)
6. Now, whenever you enter an Employee ID in the 'Salary Slip' sheet, all the relevant details will be automatically pulled in from the 'Employee Details' sheet, and the total salary will be calculated automatically.
Remember, in India, certain components of the salary such as HRA and DA are governed by the Income Tax Act, 1961. So, ensure you're adhering to the guidelines prescribed by the law while preparing the salary slip.
From India, Gurugram
Acknowledge(0)Amend(0)
401. Create two Excel sheets. Let's call the first one 'Employee Details' and the second one 'Salary Slip'.
- The 'Employee Details' sheet should include columns such as Employee ID, Employee Name, Basic Salary, HRA, DA, and any other salary components relevant to your organization. Fill in this sheet with the details of all your employees.
2. On the 'Salary Slip' sheet, create a similar structure. However, here, you will only input the Employee ID. The rest of the details will be pulled in from the 'Employee Details' sheet using the VLOOKUP function.
3. To use VLOOKUP function:
- Click on the cell where you want the data to appear.
- Type =VLOOKUP(
- Click on the cell with the Employee ID on the 'Salary Slip' sheet.
- Type a comma, then switch to the 'Employee Details' sheet.
- Select the entire table of data on the 'Employee Details' sheet.
- After the table range, type a comma and the column number from which the data should be drawn. For example, if the Basic Salary is in the third column, type 3.
- Finally, type FALSE for an exact match, then close the parenthesis and press enter. Your formula should look something like this: =VLOOKUP(A2,'Employee Details'!A1:E100,3,FALSE)
4. Repeat the above step for all the columns in the 'Salary Slip' sheet. Remember to change the column number in the VLOOKUP formula according to the column from which the data should be pulled.
5. To calculate the total salary, you can simply use the SUM function. For example, if Basic Salary, HRA, and DA are in columns B, C, and D respectively, your formula would be =SUM(B2:D2)
6. Now, whenever you enter an Employee ID in the 'Salary Slip' sheet, all the relevant details will be automatically pulled in from the 'Employee Details' sheet, and the total salary will be calculated automatically.
Remember, in India, certain components of the salary such as HRA and DA are governed by the Income Tax Act, 1961. So, ensure you're adhering to the guidelines prescribed by the law while preparing the salary slip.
From India, Gurugram
Acknowledge(0)Amend(0)Join Our Community and get connected with the right people who can help. Our AI-powered platform provides real-time fact-checking, peer-reviewed insights, and a vast historical knowledge base to support your search.
Vlookup in Excel.xls
SALARY EXCEL.zip