How to use Excel functions to implement column printing

Occasionally need to print some of the list of a few special, example: Single score table, Shang list, construction staff annual inspection list, product Code table and so on. For the annual inspection list of the builders, only serial number, name and number 3 are listed. Printing directly because the 3-column table is much smaller than the width of the paper will appear a lot of blank, not only ugly but also a waste of paper. Therefore, it is necessary to fill out the table column page and then print, but Excel does not have the column function, how to achieve the column printing? In fact, in Excel 2007, you can easily implement the column printing of a table with functions.

1. Format settings

Open an "annual check List" worksheet that requires columns in Excel 2007. Hold down the CTRL key and drag the "Check List" sheet tab to copy an "annual Inspection list (2)" Worksheet and rename it to "columns." Press the CTRL + a key on the columns worksheet and press the DELETE key to delete the data to get a border, row height, column width, and a blank table with the same font as the original table.

Adjust the width of column 4th to the required compartment spacing, then select a1:d1 Copy, select the 1th row, right-click Select Paste, and choose column width in the Paste Selective window to make sure that the column width settings for the table a1:d1 are copied to all columns.

2. Input formula

Enter the formula in the A1 =if (a2= "", "", the Annual check list! $A 1), A2 the title of the "Annual check List" worksheet A1 otherwise null, and enter the same formula in B2, C3, but change the $a$1 in the formula to $b$1, $C to refer to the corresponding title.

In A2 input formula =if (INDIRECT) ("Annual inspection list!") A "&row () +int (COLUMN ()/4) *count ($D: $D)) =" "," ", INDIRECT (" Annual inspection list! ") A "&row () +int (COLUMN ()/4) *count ($D: $D)), enter the same formula in B2, C2, and change a in the formula to B and C to refer to the contents of the corresponding column.

Enter 1 in D2 as the basis for calculating the number of rows, and set the font color to white to let 1 disappear. Select A2:d2 Drag the fill handle down to the C20 cell to display 19 rows of data in the first column. Finally select A1:D20 Copy, and then select 1:20 to paste to generate all the columns table.

The row () +int (COLUMN ()/4) *count ($D: $D) In the formula calculates the line number of the current cell data in the original table. The indirect function refers to the cell by row, column number, and then if it is empty when the referenced cell is empty. Count ($D: $D) automatically counts the number of rows per page,/4 is an empty column separated by the number of columns in the original Table 3+1 column.

3. Hide Empty table

The above reference formula lets the column table that exceeds the total number of records in the original table not display the data, but the table line is still in, and you have to set the conditional formatting so that the empty table is automatically hidden. Select the 1:20 row and click Conditional Formatting on the Start tab to select Highlight cell Rules/other rules.

In the cell value Drop-down list in the New Formatting rule window, select NULL. Click the Format button, and then click None on the Borders tab of the Format Cells window to have no border, and the area without the data will not display a border.

4. Final adjustment

Switch to the View tab click Page Layout to see the columns for each page. Drag the left and right margin directly in the horizontal ruler so that the last column printed on page 1th is exactly the blank column used for the column, so that you can ensure that the columns in each page are the same number of columns.

If you want to adjust the spacing between columns, directly adjust the 4th column width, and then copy a1:d1 press the width "paste" to the 1th line, of course, you have to readjust the left and right margin to ensure that the number of columns consistent. To add a total title to each page, you can now click the header area on the top of the page to add a title directly to the header.

Now look at the number of rows printed on each page is appropriate, from the figure can be seen in fact can add another two lines. We select two lines (17:18 lines) directly between the rows of 2:20, right-click to select replication, and then right-click to select Insert copied cells to add 2 rows of tables. Conversely, if you have too many rows out of a page, you can reduce the number of rows by simply right-clicking on the 2:20 line and selecting Delete.

Now it's time to start printing. Although empty column tables are not displayed, blank pages are printed as a result of formulas. So when printing, you have to select all the columns that have data, click the Office button to select Print, and then print the selected area in the Print window before printing a blank page. Or you can directly specify the range of printed page numbers. Later on the annual Inspection List worksheet to fill in the modified data, open the "columns" worksheet can be directly to the column printing, no need to make changes.

Saving a document as a template can also be used to print columns to other tables. All you have to do is copy the table header with the data copied and pasted to the A1 cell of the annual Inspection List worksheet to print in columns. Also not necessarily 3 columns, less than 3 columns of the table to adjust the same can be divided into columns. In a 2-column table, just right-click the column 3rd column in the columns worksheet to select Hide, then copy a1:d1, "Paste" to line 1th by width, and then adjust the left and right margins to achieve the columns of the 2-column table.

Of course, more than 3 columns of the table will have to modify the number of columns in the formula, so want to make a template to start with the most columns of the table settings, and then on the need to hide redundant columns will be more convenient. In addition, if you paste a table with two rows of headings, simply select Row 1th in the column sheet and insert copied cells to add the header row. Then in the A1:C2 header row in the original table merge, formatting, select a1:d2 copy Paste to 1:2 rows.