Use Excel to do payroll statistics, is a lot of small and medium enterprises will do, but how to make the pay bar printing is fast and beautiful is a lot of people headache problem, the following provides a number of comprehensive solutions, the need for friends can be based on their actual situation to choose a scheme to operate.
One, people less good affairs
For some small business or office type of company, the company's total number is not much, a few people, more than more than 10, 20 people look like, even if everyone's pay bar into a piece of paper, it will not cause any big waste, so we can simply set its every record to print on a piece of paper, This is the simplest and quickest way.
A similar "header" is used here to achieve a "header row" after each page is printed. This guarantee the salary of the itemized items in the pay line, and then each "heading line" below with a record, the final printout of the effect is the payroll, the specific operation is as follows.
In Excel's payroll, execute the "file" → "Page Setup" command, switch to the Sheet tab in the Open Page Setup dialog box, click the button to the right of the top header row, select the header area of the payroll on the worksheet, and then click Enter to make the Page Setup dialog box as shown in Figure 1. Then click the OK button to make the settings effective.
Figure 1 Selecting the head of a payroll table
Next click the Print Preview button to make the worksheet print Preview. Then click the Margins button in the Print Preview dialog box to display a dashed line to adjust the margins in the Print Preview dialog box, and drag the bottom margin by using the mouse to dash to the second row of the worksheet, that is, the final effect is the only two rows displayed on the page. A line is a payroll breakdown, a row is the data (Figure 2), then print it, printed on each page is an employee's pay bar.
Figure 2 dragging the bottom margin computer tutorial
Tip: This print payroll method is the simplest, most secure (not error-prone), the quickest way, of course, the drawback is too waste of paper, if the company a little more than a little, it is not appropriate, but you can try the following methods.
Second, the magical sort, easy to print the pay bar
The end result of the payroll is a line of salary, a line of employee data, usually a good payroll is only the first line of data payroll, all of the following data, if you can start from the second line, each line at the end of each row to add a line of salary details. This can be easily implemented using sorting, the specific implementation of how, please continue to look down.
Assuming that there are 300 rows of data in the current payroll, it is clear that using the first method is not feasible. First copy the payroll details for the first row, paste from any line after 300 rows, and use the fill handle to drag to copy 300 rows.
Next, insert two columns in front of column A, and the first column uses the fill handle to enter the ordinal number, from 11 straight to N (this example operation is 1000, this n can be based on their own preferences and actual needs, basically as long as the data in the payroll table is more than twice times), mainly used to print the next time the payroll after the return of the table order. The second column starts with the second row, enters 1, 3, 5, 7, and then selects the four cells and fills the end position of the employee data with the fill handle. In the Payroll detail section below, enter 2, 4, 6, 8 from the first row, then select the four cells, fill to the end with the fill handle, and the effect is as shown in Figure 3.
Figure 3 Enter a number to prepare for the next step
Perform the data → filter → auto filter command, click the Drop-down button in column B, and select Ascending in the Drop-down menu that pops up so that the pay bar is done (Figure 4).
Figure 4 Final effect
As can be seen from the above figure, the payroll effect has been achieved, at which point A, b two columns hidden, and then print, our work is completed. When you need to use this payroll form to recalculate your salary next month, you can restore the original order by simply removing the hidden columns A and B, using AutoFilter, and then following the column A, "ascending order".
Three, easily insert payroll breakdown data Line
In fact, print the payroll with Excel, in the final analysis is to let Excel make the payroll form, a line of payroll details, a line of employee records. If a line of payroll data is inserted above each employee data it is clear that our request is complete, of course, a purely manual line insert is obviously an "impossible task", here need a little skill.
The first step: in the two columns to the right of the payroll details, cross-enter any number (mainly for the "empty" null value later, so the number can be arbitrarily lost), then select the intersection of four cells, double-click the lower right corner of the fill handle, so that this format is filled to the end of the payroll line (Figure 5).
Figure 5 Populating the data
Step two: Perform the edit → position command, click the "Go to" button in the Open Positioning dialog box, select Null in the Open Positioning Condition dialog box, and then click OK (Figure 6).
Figure 6 Locating a blank line
Step three: Execute the Insert → line command, which starts at the third row and inserts a blank line before each row.
Step Fourth: Copy the Payroll detail data for the header, and select the data area in column A of payroll table A. To perform the edit → position command, click the Locate Condition button in the Open Positioning dialog box, select Blank value in the Open Criteria dialog box, and then click OK, and then select the blank line you just inserted. , perform the edit → Paste command, when the payroll is finished (Figure 7).
Figure 7 Final effect
When it comes to printing payroll in Excel, many people think of advanced VBA or a function that is difficult to understand, often looking at the VBA code in the solution given by others or a function, because it is difficult to understand its meaning, so always worry about error.
In fact, many times as long as the need to grasp, careful analysis, and then simulate the day-to-day operation, through a number of additional operations to improve efficiency, our requirements are reached. Because it is simple, easy to use, and easily mastered, it is not easy to make mistakes, even if the occasional error, it is easy to find out where the fault.
Here are three ways to print the pay bar can be said to be very simple and easy to use. Hope that there is a need to help, even if the use of friends can also through the above examples, in the face of relevant applications, can be more to solve the problem of ideas.