Several common methods of making payroll with Excel

Several common methods of making payroll with Excel

Copy and Paste method

The easiest way to do this is to copy the header row, paste it on top of each employee data line, and manually complete the production of the pay bar:

1, select A1:g1 cell, copy header row.

2, right click on line number 3rd, select "Insert Copied Cells", complete the second member of the title row Add.

3, use the same method on the third employee line number right-click Insert copied title, manual completion of the payroll production.

This method of pure manual operation, slow, inefficient, boring operation process, in the face of more employees pay form will face overtime trouble situation.

Second, the editorial skill method

Fully deploy Excel editing function, you can easily complete, its production idea is, first for the table rows, for the copied title to provide paste space, and then use the positioning function to select all blank cells, copy the title paste can:

1. Create secondary data on the right side of the table (left below), and then use the fill handle to fill down to the last row of the table.

2. Use positioning function (ctrl+g), select "Null value" in "Positioning Criteria", select all empty cells.

3. Insert a new line: Use the shortcut key ctrl+shift++, add a new row at the location where you select the cell, select "Whole row" in the pop-up window, add the effect of the blank row, and delete the secondary data in the H and I columns as shown below.

4, copy the title A1:g1 area, select a1:g18 the entire table area, ctrl+g positioning null value, paste the title can complete the production of the pay bar;

This method makes full use of Excel's powerful data editing function and skill, in view of the actual problem demand, skillfully will the various editing function integration use, the skilled shortcut key application may enhance the editing speed greatly, increases the work experience the joyful feeling, lets the work relaxed and the mood is comfortable.

Method of formula function

Often use the formula function of Excel users, always want to use the calculation formula to prepare a template for once and for all purposes, we can create a payroll table with the Wage table link, when the payroll data changes, the payroll template in the corresponding data will be updated in real time, so that you can achieve the ultimate efficiency.

1, add a new worksheet, named "Wage Bar Table", where you create a formula function.

2, table A1 Cell input formula: =if (MOD (ROW (), 3) = 1, Payroll! A$1,if (MOD (ROW (), 3) =0, "", INDEX (Payroll!) $A $: $G $, (ROW () +4)/3,column ())), select the formula cell to copy the formula to the right, until all staff pay is present and the salary terms are made.

Four, Macro and VBA programming method

The use of macros and the method of writing VBA code can also easily achieve the production of payroll, the macro to assign to the button, each time as long as the button can be used to achieve the production of the pay bar.

1, press ALT+F11 to open the VBA window, select the "Insert" menu, click "Module" command, add a new module.

2, in the Code window to the right of the module, enter the following code:

Sub Pay Bar ()

Dim x as Integer

Range ("A1"). Select

For x = 1 to Range ("a1048576"). End (Xlup). Row-2

Activecell.rows ("1:1"). Entirerow.select

Selection.Copy

ActiveCell.Offset (2, 0). Rows ("1:1"). Entirerow.select

Selection.insert Shift:=xldown

Activecell.select

Next x

End Sub

3, in the first line of the payroll blank, add a button (but pictures, graphics or controls), right-click the button, select "Specify the macro", in the pop-up window select the "Pay bar" macro program, the next click button can automatically execute the program to complete the production of the pay bar.

A small pay bar production, in Excel there are so many ways to complete, can be seen in Excel features strong and flexible, learning to use good Excel is really a must for each professional staff skills.