Jinshan Table Quick Insert Page Subtotal

Because working relationships often use Jinshan form 2003 to make payroll and engineering budgets, in these tables you need to display a paging subtotal on the last line of each page for easy viewing. Manually inserting a paging subtotal is too cumbersome and not easy to modify, so it's a simple way to quickly insert a paging subtotal.

1. Click file → page Setup to switch to the Sheet tab, and in the format of the table you need to set the top header row to 4, that is, the first 4 rows appear repeatedly in each table. Set the margins, and then use Print Preview to confirm the number of lines that can be printed on each page except for the title, which assumes 16.

2. Table data finally add a column (k column) header Name page number, enter Formula =ceiling (ROW (F5) -3,16)/16 (as Figure 1), drag the sizing handle to copy the formula to the following cells.

Figure 1 Adding the "page numbering" cell

Tip: In the Formula 3 is the number of header rows minus 1 (4-1=3), and two 16 is the number of data rows that can be printed per page.

3. Select the Payroll data Area (b4:k200) and click data → subtotal. In the Subtotal interface that pops up, set the Category field item to the page number (by K), Sum to sum, and select all the columns you want to summarize in the selected totals list. Once you are sure you can see that the paging subtotals are all automatically inserted.

4. Right-click the k column of the page number, select Hide, so that when you print, you will not see our own added page number column. Then you can print directly, print and then press the "Ctrl+z" key to undo the hidden and subtotal operations to restore the original payroll, or you can save before the subtotal, subtotal remember not to save the same effect.

Sub-page subtotals with the above operation only merged data, and there is no page subtotal. If you need to display the text, select the Payroll data Area (b4:k200) and click data → filter → AutoFilter. Click the filtered drop-down arrow in a column that does not have subtotals, select blank, display only the row of the paging subtotal, and then enter a four-word subtotal in the first row, Drag the fill handle down to fill, and the column width is not sufficient to display automatically across rows (Figure 2). When you are done, click the same filter Drop-down arrow to select (All). You will not be able to undo the filter after you use it, so you must save it before subtotals, and be careful not to save it after printing. or click the following method to page out the subtotal you may not consider this issue.

Figure 2 Selecting the "Blank" item

If you do not want to repeat subtotals every time, you can hold down the CTRL key and drag the Payroll sheet tab to copy the other piece "Payroll (2)", and B5 the formula = Payroll in the "Payroll (2)" Form! B5, and then drag the fill handle to fill in the other cells that have data, so that the data changes in the payroll are all displayed in the Payroll table (2). Then we just need to page out the "Payroll (2)" by the method described earlier. If you modify the payroll, you can print it directly from payroll (2) to a payroll with a paging subtotal.