3 ways to make beautiful pay bars in Excel

How to use Excel to make payroll, maybe some netizens have been using for a long time, but how to make a beautiful pay bar faster and better, see the author's method.

"Little problems to the old financial", perhaps you have been working in the financial mouth for many years, maybe you have to face Excel every day, maybe you have been accustomed to using the salary list to do employee payroll statistics and report to the superior, the table of the items in a copy and paste after printing to each employee. But have you ever thought of finding a simpler and more efficient way? It seems to be difficult: In a payroll schedule, a salary item (i.e., a header) usually appears only on a line at the beginning of the table, while in the payroll, for readability, everyone's salary has a header, So how do you skillfully make a payroll bill that makes it easy to print? As shown, this is the work we are going to do (see Figure 1 for the original payroll, see Figure 2 for the effect of converting to a pay bar):

Figure 1 The original salary table

Figure 2 Effect after conversion

"All roads lead to Rome", but the road is far and near, the method is also difficult to easily, here to provide you with three of the easiest way to use.

　Method one: Macro Command control method

People may have heard about the macro features of the Office family, but because of the need to program with VBA, macros have been daunting to many people, but it's much simpler to use a ready-made macro. Open the Excel table you want to work with, select the tools → macros →visual Basic editor, double-click Sheet1 in the window to the left of the editor, the Code editing window appears, and the Code Editor window enters the following code (in order to not break the original payroll, so here's the use of the The Sheet1 content is copied to the Sheet2 method, so the final build result is displayed in Sheet2):

Sub makesalarylist () Dim I as Integer Dim Endrow as Integer

To measure the last line of data

Endrow = Sheet1.range ("a65536"). End (Xlup). Row-1

Stick the title over

Worksheets (1). Range ("1:1"). Copy (Worksheets (2). Cells (1, 1)) For i = 3 to Endrow

Put each piece of data up there.

Worksheets (1). Range (Cells (i, 1), Cells (I, 256)). Copy (Worksheets (2). Cells (3 * i-6, 1)) Next I End Sub

Close the editor, will automatically return to the Excel table just now, select "tools → macros → macros", will pop up the following dialog box: (see Figure 3)

Figure 3 Adding macros

Click "Execute", will be generated in the Sheet2 as shown in Gz-2 into the payroll, how, not complicated bar. Of course, if your table total Sheet2 has entered other content, you can copy him to Sheet3 to generate, code modification is also very simple, the Sheet2 modified into Sheet3 can be, other code unchanged. The next page is more exciting