When printing out copies of spreadsheets in Excel, it is sometimes very useful to add a header and/or footer with the date and time, page numbers, filename, etc. In this way, there no guess work involved in determining which print out is the most recent, what file the print out was made from, and page numbers can be referred to in meetings or other correspondence. In addition, when printing out a large spreadsheet, it may be necessary to have particular rows or columns print at the top or left side of each page. For example, when printing out employee information that has several columns, you would want the employee names to appear on the left hand side of each page so there is no confusion as to which employee the information belongs. Let's start with creating a header and/or footer. Are you ready?
Creating a Header and/or Footer
In this example, we are working with a schedule of software training sessions that will be offered to employees after regular business hours. The spreadsheet goes on for several pages and covers 3 months. Rows 3 & 4 will be repeated as the first rows of every printed page following the first in Part 2 of this tutorial:
To add a header and/or footer:
-
From the View menu, choose Header and Footer. The Page Setup dialog box allows you to choose a previously used header/footer with the drop-down arrow, or create a new header/footer with the Custom buttons.
-
The same steps are followed for both headers and footers. Let's add a footer. Choose the Custom Footer button. The Header/Footer dialog box displays as shown below.
Note there are three sections. Text in the Left section is left-aligned, the Center section is center-aligned, and the Right section is right-aligned. Excel uses codes to insert page numbers, dates, times, etc. These codes are placed in the header or footer by using the buttons described in the above diagram. Text and codes can be combined to obtain the desired result.
-
The cursor should be flashing in the Left section. If not, click inside the Left section. Type "Training Department" and press Enter. Choose the Date button to insert the date below the department name.
-
Press Tab to move to the Center section. In this section, we want the page numbers to read "Page 1 of 5", for example. Type "Page", press Space, choose the Page Number button, press Space, type "of", press Space, and choose the Total Pages button.
-
Press Tab to move to the Right section. In the Right section, choose the Sheet Tab Name button, press Enter, and choose the Filename button. This gives us the exact location of the printed information should we need it at some point in time.
If you want to change the font attributes, select the text/codes the same way you would select text in any document, and choose the Font button. Make your selections in the Font dialog box and choose OK.
-
Once all three sections are complete, choose OK on the Header/Footer dialog box. A preview of your new header or footer is shown in the Page Setup dialog box.
-
To see how it would look with the actual spreadsheet, choose Print Preview. The Options button allows you to change page orientation, paper size, etc. Choose OK to accept the footer and return to the spreadsheet.
Repeating Rows and Columns
In the software training schedule example, it would be helpful if rows 3 and 4 could be repeated as the first rows on every page following the first page. Here is our example again:
To repeat rows or columns:
-
From the File menu, choose Page Setup and move to the Sheet tab. In addition to repeating rows and columns (Print titles), the Sheet tab allows you to set a print area to print only part of the spreadsheet and gives you other print options such as printing the spreadsheet gridlines and changing the order in which pages are printed. For this tutorial, we are concerned with the Print titles area.
-
A range must be entered in either the rows or columns field. In our case, the Rows to repeat at top field. Even if you only want one row to repeat, you must still enter a range. For example, $3:$3, would repeat row 3. To make it easy, choose the Collapse dialog button as shown above.
-
This button collapses the dialog box and allows you to choose the rows from the spreadsheet. Click row 3 on the left and drag down to row 4. The chosen rows are outlined with a marquee. Notice the range is automatically placed in the Page Setup box.
-
Once the rows are selected, choose the Expand dialog button as indicated above. The Page Setup dialog box expands.
-
Choose Print Preview to check for the repeating rows on each page, or choose OK to close the dialog box and return to the spreadsheet.
Working with Page Breaks
There are times when pages break in inappropriate places. In the tutorial example, we may not want pages to break in the middle of a week's schedule. To adjust page breaks:
-
From the View menu, choose Page Break Preview. Page breaks are indicated with blue dotted lines. You can click and drag these lines to make the appropriate changes. Notice the following page break cuts through the middle of a weekend:
-
The mouse pointer changes to a double-headed black arrow when held over the dotted line. Click and drag the dotted line to a more appropriate place. In our case, in between rows 48 and 49. Continue until you are satisfied with all the page breaks.
-
From the View menu, choose Normal to return to the normal spreadsheet view.
The little "extras" really make your work look professional. Experiment with headers, footers, repeating rows/columns, and page breaks to put your best foot forward with a nice, clean print out. Have fun!