Are you an HR professional and know how to make an employee payroll in **Excel**? Then, look no further, because in the following tutorial we’ll demonstrate a step-by-step guide on **how to generate payroll** in Excel **VBA**. In addition, we’ll also learn to automate payroll in Excel.

## Download Practice Workbook

## 2 Steps to Generate Payroll in Excel VBA

First of all let’s consider the **Employee Information** dataset shown in the **B4:H15** cells which contains the *“Employee Name”*,* “Pay Rate”*, *“Total Work Time”*, *“Overtime Rate”*, *“Total Overtime”*, *“Health Insurance”,* and *“Other Deductions”* columns respectively. So, let’s explore how to generate payroll in Excel VBA with the necessary illustrations.

Here, we have used the *Microsoft Excel 365* version; you may use any other version according to your convenience.

### 📌 Step 01: Insert the VBA Code into a Module

- First, navigate to the
**Developer**tab >> click the**Visual Basic**button.

Now, this opens the **Visual Basic Editor** in a new window.

- Second, go to the
**Insert**tab >> select**Module**.

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

```
Sub Calculate_Gross_Pay()
Worksheets("VBA Code").Range("I6:I15").Formula = "=(C6*D6)+(E6*F6)"
Range("I6:I15").NumberFormat = "$#,###"
End Sub
Sub Calculate_Tax()
Worksheets("VBA Code").Range("J6:J15").Formula = "=0.2*I6"
Range("J6:J15").NumberFormat = "$#,###"
End Sub
Sub Calculate_Net_Payment()
Worksheets("VBA Code").Range("K6:K15").Formula = "=I6-(J6+G6+H6)"
Range("K6:K15").NumberFormat = "$#,###"
End Sub
```

**⚡**** Code Breakdown:**

Now, in the following section, we’ll explain the 3 **VBA** **Macros**.

- In the first macro,
*“Calculate_Gross_Pay”*, use the**Worksheet**and**Range**objects to apply the formula to the**I6:I15**range of cells located in the*“VBA Code”*worksheet. - Next, utilize the
**Number Format property**to format the results as**Currency**. - Similarly, compute the
*“Tax”*and*“Net Payment”*by executing the second and third macros respectively.

**Read More: ****How to Create Payroll Calculator in Excel (with Easy Steps)**

### 📌 Step 02: Run the VBA Macros

- Third, close the
**VBA**window >> click the**Macros**button.

This opens the **Macros** dialog box.

- Following this, select the
*“Calculate_Gross_Pay”*macro >> hit the**Run**button.

- In turn, choose the
*“Calculate_Tax”*macro >> press**Run**.

- Last but not least, execute the
*“Calculate_Net_Payment”*macro.

Subsequently, the results should appear in the animated GIF given below.

**Read More: ****How to Make a Payroll System in Microsoft Excel with Payslip**

## How to Automate Payroll in Excel

Alternatively, we can also automate payroll in Excel using the **VLOOKUP function** to pull in data from a source worksheet to calculate the employee payroll. Now, let’s assume the **Employee Information of Wilbur and Company** dataset shown in the **B4:J14** cells which depicts the *“Employee ID”*, *“Name”*, *“Department”*,* “Pay Rate”*, *“Total Work Time”*, *“Overtime Rate”*, *“Total Overtime”*, *“Health Insurance”,* and *“Other Deductions”*.

📌 __Steps:__

- At the very beginning, go to the
**C8**cell >> move to the**Data**tab and insert a**Data Validation**drop-down by following the steps shown in the GIF.

- Then, navigate to the
**C9**cell >> insert the expression below.

`=VLOOKUP(C8,'Employee Information'!B4:$J$14,2,FALSE)`

Here, the **C8** cell refers to the *“Employee ID”*.

**Formula Breakdown:**

**VLOOKUP(C8,’Employee Information’!B4:$J$14,2,FALSE) →**looks for a value in the left-most column of a table, and then returns a value in the same row from a column you specify. Here,**C8**(argument) is mapped from the*lookup_value***B4:J14**(argument) array. Next,*table_array***2**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***Exact match**of the lookup value.**Output → Max**

📃 *Note: **Please make sure to use **Absolute Cell Reference** by pressing the F4 key on your keyboard.*

- In a similar style, use the
**VLOOKUP**function to retrieve information related to*“Max”*and in each case specify the correct column number. For instance, the column number for*“Department”*is*“3”*, for*“Pay Rate”*is*“4”*, and so on. - Not long after, apply the
**SUM function**to calculate the*“Total Deductions”*.

`=SUM(E11:E12)`

In this case, the **E11** and **E12 **cells indicate the deductions for *“Health Insurance”* and *“Other Deductions”*.

- Afterward, copy and paste the formula into the
**C16**to obtain the*“Gross Pay”*.

`=(C11*C12)+(C13*C14)`

For example, the **C11**, **C12**, **C13**, and **C14** cells represent the *“Pay Rate”*, *“Total Work Time”*, *“Overtime Rate”*, and *“Total Overtime” *respectively.

- Later, calculate the
*“Tax”*using the following expression.

`=E13*C16`

In this situation, the **C16** and **E13** cells point to the *“Gross Payment”* and *“Tax Rate”*.

- Consequently, insert the formula into the
**Formula Bar**to compute the**Net Payment**.

`=C16-(C17+E14)`

For example, the **C16** and **C17** cells refer to the *“Gross Payment”* and *“Tax Rate”* whereas the **E14** cell represents the *“Total Deductions”*.

**Read More: ****How to Calculate Payroll Overtime with Formula in Excel**

## Things to Remember

There are a few things to keep in mind when running the **VBA Code**.

- In the first place, make sure to change the worksheet name to
*“VBA Code”*or specify the correct worksheet name.

- Second, specify the appropriate range of cells where to return the calculated output.

## Practice Section

We have provided a** Practice** section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

## Conclusion

To sum up, we hope this article helps you understand how to generate payroll in Excel VBA. Now, if you have any queries, please leave a comment below. And, you can read more articles like this on our website **ExcelDemy**.