How to make Salary Slip in Excel with Formula?

Salary Slip is made by every Organisation for their employees. It is very easy to make. You just need to follow the steps below and become an expert in making salary slip or pay slip.

<iframe width="560" height="315" src="https://www.youtube.com/embed/oLdFnV2EavI" frameborder="0" allow="accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>

OR

     https://youtu.be/oLdFnV2EavI

Watch this video with the help of above link and follow the steps given below. You will learn it very easily with the help of these steps

Steps for Making Salary Slip in Excel:

Step 1: Make your Salary Sheet in Excel of all your employees.

 Provide Employee ID, Employee Name, Working Days, Basic salary, House Rent Allowance, 

 TDS, Provident fund, Net Salary, etc in your salary sheet. Provide every data which you need in              your Salary Sheet.

Step 2: Start making Salary Slip Format in Excel. 

Step 3: First, Select 'Page Layout' option from the bottom of the sheet.

Step 4: Merge the cells and type the Company name, Address, etc.

Step 5: Type all the Heading like Earnings, Deductions, and Net pay. Type all the data.

Step 6: Give your Sheet, Thick borders from the Home Tab.

Step 7: Now, after making the format as per your choice. Start using the formulas to make your Salary   Slip fully automatic. First, put the cursor on the cell beside the EMP_ID and use DATA                            VALIDATION as shown in the video.

Step 8: Click on DATA TAB- Select DATA VALIDATION- Allow: LIST- Source:                                                 =Sheet1!$A$3:$A$23 (Select the EMP_ID Column, i.e. Select EMP_ID of all your                    Employees)- Press OK

Step 9: Now, Your DROP DOWN List is ready. You will see all the EMP_ID in one cell. 

Step 10: Select any EMP_ID. Let's say, 001

Step 11: It's the time to use VLOOKUP Formula to make it fully automatic.

Step 12: Use Vlookup formula in the cell beside EMP_Name.

              =Vlookup($B$10,Sheet1!$A$2:$Q$22,2,0)

               

               $B$10 - Select the cell of EMP_ID i.e. 001

              

               Sheet1!$A$2:$Q$22 - Select the whole Table of your salary data in Sheet 1 of Excel.

          

               2 - It means that you are finding EMP_NAME with the help of Vlookup formula. And EMP_NAME is in column 2 of your Salary Data in Sheet 1

 

               0 - It means you need accurate results. So, simply type 0

Step 13: Now simply Copy And Paste ( CTRL + C And CTRL + V) in all the cells.

              For example, In Designation, Basic Salary, House rent allowance, TDS, etc, just simply copy and paste the formulas.

Step 14: It's time to change the Col_Index_num i.e. Column no. of all the copy and paste formula.

              For example, Type 3 in Column No. in case of EMP_NAME because it is in Column no. 3 of Salary table in sheet 1.

Step 15: Simply follow the Step 14 in all the cells to get your desired results.

Step 16: Now your Fully Automatic Salary Slip or Pay Slip is ready. You can use it very easily. It    will save your time and help you do your work in seconds.

Step 17: Just change the EMP_ID and you will get the Salary of another employee in a fraction of        seconds.

Step 18: Select EMP_ID - 002 and get the salary slip of that Employee. Do like this and get the Salary  Slip Instantly.

Hope, This will help you and make your work easier.

Just watch the video and understand it better.