27. Printing
You’ve finished your workbook model and now it is time to print. In reality, you are most likely going to email the workbook or export it to a PDF. But there are still some workbooks that you will print on real paper. This chapter gives you some tips to making that printout look great.
Printing in One Click
If you’re a keyboard enthusiast, you might be upset that in Excel 2016 Ctrl+P takes you to the Print panel instead of performing a quick print. In a few steps, you can bring Quick Print back to Excel 2016.
The Quick Access Toolbar (QAT) is the row of small icons that appears just above or just below the ribbon. At the right edge of this toolbar is a drop-down menu. Open the drop-down at the right edge of the Quick Access Toolbar to display a short list of popular commands. Choose Quick Print, as shown in Figure 27.1.
When you click the Quick Print icon, one copy of the current worksheet is sent to the last printer you used in Excel. If you have not previously printed in this Excel session, the worksheet is sent to the default printer.
Although this brings the Quick Print back as a mouse click, it still isn’t great for keyboard-centric people. If you press and release the Alt key in Excel, you see a row of shortcuts for the first nine items in the Quick Access Toolbar. Because Quick Print is the eighth icon in Figure 27.1, Alt+8 does a Quick Print.
Finding Print Settings
There are at least nine places in Excel where you can change the print settings or page setup. The most common tasks are found in multiple places. For example, you can change margins in five of the nine places. You can change paper size and orientation in four of the nine places.
As you move down to the obscure settings, you might be able to find them in only one or two places. Figure 27.2 shows a cross-reference. For any given task, you can locate where you might be able to change the setting.
Figure 27.2 Various printing tasks are spread throughout the Excel interface. The superscript in the Page Setup Dialog column refers to the tab within the dialog.
Nine places are listed across the top of Figure 27.2. Here is where to find each place:
File, Print—Open the File menu and choose Print to display the Print panel. This panel has a mix of Printer and Page Setup settings in the center and a large Print Preview on the right. Introduced in Excel 2010, it aims to be a one-stop place for getting your printout to look right.
Page Layout Tab of the Ribbon—Click the Page Layout tab in the ribbon. You find three groups related to printing: Page Setup, Scale to Fit, and Sheet Options.
Page Setup Dialog—Click the diagonal arrow icon in the lower-right corner of the three groups in the Page Layout ribbon tab to launch the legacy Page Setup dialog. This dialog contains four tabs. The superscript next to each bullet in Figure 27.2 identifies the tab: 1 for Page, 2 for Margins, 3 for Header/Footer, 4 for Sheet. You can also reach this dialog by clicking the Print Titles icon in the Page Layout tab of the ribbon.
Page Layout View—Choose Page Layout on the View tab. This icon also appears in the lower right of the Excel screen.
Header & Footer Tools Design Tab—When you are in Page Layout view, click one of the three header or three footer zones on any page to have the Header & Footer tools Design tab appear in the ribbon. Note that you have to click away from the header or footer zone to exit Page Layout view. Although this tab is the most hidden, it offers an easier way to control headers and footers (see Figure 27.3).
Figure 27.3 After you click into a header in Page Layout view, you can access the Header & Footer tools tab in the ribbon.
Page Break Preview View—Click Page Break Preview on the View tab. This icon also appears at the bottom right of the Excel window.
Printer Properties Dialog—Use Ctrl+P to display the Print panel. A Printer Properties link appears just below the printer name.
Excel Options—Open the File menu and then choose Options, Advanced. This is the only place to turn off the display of automatic page breaks after you’ve done a Print Preview.
Print Preview Full Screen—Add this icon to the Quick Access Toolbar to reach a full-screen version of Print Preview similar to older versions of Excel.
The rest of this chapter covers most of the tasks along the left side of Figure 27.2.
Previewing the Printed Report
Before you start adjusting the page settings, you can take a quick look at how the worksheet currently will print.
Using the Print Preview on the Print Panel
One method to view the printed document is to use File, Print or Ctrl+P. For now, ignore the settings in the middle panel and look at the Print Preview pane on the right.
If your document is larger than one page, you have a vertical scrollbar to the right of the Print Preview. Use this scrollbar to move to other pages.
Four icons are available at the bottom of the Print Preview window (see Figure 27.4):
To navigate to a new page, use the left-arrow or right-arrow icon in the lower left. You can also type a new page number in the page number text box and press Enter or Tab. The PgDn and PgUp controls still work, but only when you click the preview first.
The Zoom check box feels like it is reversed. If you clear this check box, Excel zooms in to a smaller section of your printout.
Select the Show Margins check box to have Excel draw draggable margins in the page. Drag any of the margin lines to change the page margins. Drag any of the column handles to resize columns.
When you first look at the print preview, check for these obvious problems:
Does one column or a few rows spill over to a second page when you want everything to fit on one page? See “Repeating the Headings on Each Page” on p. 567.
On a multipage report, go to page 2: Are the titles and headings appearing on pages after the first page? If not, see “Adding Print Titles,” later in this chapter.
Are page numbers appearing where you want them? If not, see the “Adding Headers or Footers to the Printed Report” section.
To close the File menu and return to your document, click the large left-pointing arrow in the top-left corner of the File menu.
Using Full Screen Print Preview
Some people develop macros in Excel where they want someone to preview a report in Print Preview. The new Print Preview on the Print panel doesn’t work with these macros, so the Excel team added a command that gets you to a full-screen Print Preview.
The full-screen Print Preview works particularly well with wide reports in a landscape orientation.
You must add the Print Preview Full Screen to your ribbon or Quick Access Toolbar. For instructions, see Chapter 3, “Customizing Excel.”
Making the Report Fit on the Page
Before you print, you want to make sure your data is going to fit on the page. You can control the paper size, orientation, and margins. You can make a few heading rows print at the top of each page. You can add information such as page number, file location, date, and time in the header or footer.
Setting Worksheet Paper Size
You can choose from a variety of paper size options in the Size drop-down in the Page Layout tab, as shown in Figure 27.5. When you encounter a report that is too wide for a regular sheet of paper, you can switch to a larger page size, such as Legal paper. You can choose one paper size or select More Paper Sizes from the bottom of the list to specify a new size.
Tip
Some paper sizes, such as 11”×17”, are available only if your selected printer offers that size. If your default printer cannot print large-format paper, you should change the printer selection in the Print panel and then return to the Page Setup dialog to select the larger-format paper.
Adjusting Worksheet Orientation
Changing a report to print sideways, which is also referred to as landscape, takes just a couple of mouse clicks. From the Page Layout tab, select Page Setup to see the Orientation drop-down, which offers Portrait and Landscape options.
Adjusting Worksheet Margins
When you are trying to squeeze an extra column into a report, you can tweak the report to have smaller margins. Figure 27.2 shows that there are five different places you can adjust the margins for your worksheet. Here are my favorite three methods:
Choose Page Layout, Margins—This drop-down offers three settings: Normal, Wide, and Narrow. If you have previously used custom margins, another setting appears with the last custom margins you used. To apply one of these standard setups, choose the setup you want to use from the Margins drop-down. To apply a different custom margin, select Custom Margins from the bottom of this menu. Selecting Custom Margins takes you to the Page Setup dialog, discussed next.
Use the Page Setup dialog—When you click the dialog launcher icon in the bottom right of the Page Setup group, Excel displays the Page Setup dialog. Use the Margins tab to adjust the margins at the top, left, right, and bottom, as well as the margins for the footer and header. This dialog offers precise control of the six margin settings.
Choose View, Page Layout View—When you use this option, gray margins appear on each edge of the ruler. You can drag the gray margins in or out to decrease/increase the margin.
Either version of the Print Preview window has a Show Margins setting. After you’ve displayed the margins in Print Preview, you can move the margins in or out.
Repeating the Headings on Each Page
For reports that span more than one page, you might want the headings from the report to print at the top of each page. Although the Print Titles icon was promoted to a large icon on the Page Layout tab in the ribbon, this command leads back to the somewhat confusing Page Setup dialog, as shown in Figure 27.6. Suppose that you have a report that is two pages wide and several pages long. However, you notice that the printed page 2 of the printed report does not include title or column headings. If you want to have the titles and column headings repeat at the top of each row, you need to select 1:4 in the Rows to Repeat at Top option and A:B in the Columns to Repeat at Left option. When you return to the Page Setup dialog later, Excel will have added dollar signs to these settings. You don’t need to type the dollar signs; Excel will add them.
Note
To specify rows to repeat at the top, you can indicate either a single row using 1:1 or a range of rows using 1:4. Similarly, columns to repeat at left might be a single column (A:A) or a range of columns (A:B).
Excluding Part of Your Worksheet from the Print Range
By default, Excel prints all the nonblank cells on a worksheet. Sometimes, you have a nicely formatted table of data to print but the spreadsheet also includes some work cells in an out-of-the way location that you do not want to print. To prevent the work cells from being printed, follow these steps:
1. Select the range of cells to be included in the print range, such as cells A1:Z99. Alternatively, you can print everything in certain columns. For example, you might select columns C:X to be printed.
2. From the Page Layout tab, select Page Setup, Print Area, Set Print Area.
To clear the print area and to print everything on the worksheet, you can use the Clear Print Area option from the Set Print Area drop-down.
Occasionally, you will want to ignore the print areas and print everything on the worksheet. As described in the “Choosing What to Print” section later in this chapter, you can use the Ignore Print Areas setting to temporarily override the print area. Alternatively, you can print a certain range by selecting the range and then using Selection in the Print panel.
Forcing More Data to Fit on a Page
You will often have worksheets in Excel that are a few columns too wide or a few rows too long to fit on a page.
The Scale to Fit group on the Page Layout tab provides options for width, height, and a percentage scale. In most cases, you change the height, width, or both to achieve the desired effect.
If your worksheet is a few columns too wide, change the Width drop-down to specify that the worksheet should fit on one page. If you have a report that is too tall, change the Height drop-down to specify that the worksheet should be one page tall. When you select either of these options, the Scale option is grayed out, but it still shows the scaling percentage used to make the report fit.
Sometimes, a report cannot fit into one page and still be readable. This is when you can make intelligent decisions about the best location for page breaks.
Working with Page Breaks
The two varieties of page breaks are automatic and manual. An automatic page break occurs when Excel reaches the bottom or right margin of a physical page. These page breaks change automatically as you adjust margins, add rows, delete rows, or even change the height of certain rows on the page.
Initially, automatic page breaks are not shown in the worksheet. However, after you go to Print Preview and return to Normal view, automatic page breaks are shown using a thin dashed line in the document. Automatic page breaks are also evident in Page Layout view and Page Break Preview mode. To turn off the page breaks, use File, Options, Advanced, Display Options for This Worksheet to find a check box for Show Page Breaks.
You can manually insert page breaks at rows or columns where you want to start a new page. For example, you might want to insert a manual page break at the start of a new section in a report. A manual page break does not automatically change in response to changes in the worksheet rows.
1. Select an entire row by clicking the row number that should be the first row on the new page. Alternatively, select the cell in column A in that row.
2. From the Page Layout tab, select Page Setup, Breaks, Insert Page Break.
To add a page break manually at a certain column, follow these steps:
1. Select an entire column by clicking the letter above the column that should be the first column on the new page. Alternatively, select row 1 in that column.
2. From the Page Layout tab, select Page Setup, Breaks, Insert Page Break.
Caution
If you insert a page break while the cell pointer is outside row 1 or column A, Excel simultaneously inserts a row page break and a column page break. This is rarely what you want. Make sure to select a cell in column A to insert a row break or to select a cell in row 1 to insert a column break.
Manual Versus Automatic Page Breaks
In Normal view, a subtle visual difference exists between manual and automatic page breaks. The dashed line used to indicate a manual page break is more pronounced than the line used to indicate an automatic page break.
To see a better view of page breaks, you can select View, Page Break Preview to switch to Page Break Preview mode. In this mode, automatic page breaks are shown as dotted blue lines. Manual page breaks are shown as solid lines.
Using Page Break Preview to Make Changes
An advantage of Page Break Preview mode is that while you are in this mode, you can move a page break by dragging the line associated with the page break. If you drag an automatic page break to expand the number of rows or columns on a page, Excel automatically changes the Scale percentage for all pages.
Removing Manual Page Breaks
To remove a manual page break for a row, follow these steps:
1. Position the cursor in the row below the page break.
2. From the Page Layout tab, select Page Setup, Breaks, Remove Page Break.
To remove a manual page break for a column, follow these steps:
1. Position the cursor in the column to the right of the page break.
2. From the Page Layout tab, select Page Setup, Breaks, Remove Page Break.
To remove all manual page breaks, from the Page Layout tab select Page Setup, Breaks, Reset All Page Breaks. Note that clearing the page breaks also resets the scaling back to 100%.
Adding Headers or Footers to the Printed Report
Although you might describe the row of labels that appear at the top of the report as “headings,” in this section “headers” are elements that are not in the cells of the worksheet but print at the top of the page. Excel offers three header areas: left, center, and right. Similarly, there are three footer areas.
You can build headers and footers using the third tab of the legacy Page Setup dialog, but Excel now offers a graphical method for building your headers.
The only entry point for the new Header & Footer Tools Design tab of the ribbon is in Page Layout view. From the View tab, select Page Layout View. Excel displays the worksheet with white space for margins.
At the top of each page, you see gray Click to Add Header text. Hover the mouse in this area and you see that there are three header zones. Click in the Left, Center, or Right header to display the Header & Footer Tools Design tab in the ribbon.
You can either type a static header in the box or use the icons on the ribbon tab to add text that will change at print time. For example, if you insert the code for Date or Time, the printed header reflects the date or time that the report was printed. You can use the formatting tools on the Home tab to format the text in the header.
To exit Header/Footer mode, click in any cell of the worksheet.
Adding an Automatic Header
For a quick header or footer, you can click the Header or Footer drop-down in the Header & Footer Tools Design tab. The drop-down offers 16 different automatic headers, including various page-numbering styles, the system date, your name, your company name, the sheet name, and the file path and filename.
Some of the Header entries include values separated by commas. These entries put header values into the left, center, and right header sections.
Tip
Although you cannot add to the automatic Header list, you can select an automatic header that is close to what you want and then customize it.
Note
The process for adding footers is to the same as the process for adding headers. Throughout the rest of this chapter, several sections include additional information about headers. Keep in mind that the identical instructions apply to footers as well.
Adding a Custom Header
You can type any text you want into the three header areas. One of the automatic headers reads Confidential, but you can customize this in any way dictated by your company. No matter what type of header you need, you can add it by clicking in any header area and then typing the desired text. To start a new line, press Enter.
Icons for dynamic fields are located in the Header & Footer Tools Design tab. To add an element, click in a header or footer area, position the cursor in the proper place, and click the appropriate icon in the ribbon. As long as the insertion cursor is in the header area, the screen displays the code for that field, such as &[Date] or &[Time].
Tip
To include an ampersand in the header or footer, you must use the code &&. For example, to add the header Profit & Loss, type Profit && Loss.
You can mix static text and dynamic text. For example, you could type Page, and then click the Page Number icon. Type of and then click the Number of Pages icon. Type of the, and then click the Sheet Name icon. Type Worksheet. The resulting text shows Page &[Page] of &[Pages] of the &[Tab] Worksheet. When you print, the actual text might be Page 3 of 5 of the Sales 2018 Worksheet.
Inserting a Picture or a Watermark in a Header
You can add a picture to a header or footer. It can be either a small picture that prints in the header area or a large picture that extends below the header area and acts as a watermark behind the worksheet.
To add a picture to a header, follow these steps:
1. Select View, Page Layout View.
2. Click in the header area of the document.
3. From the Header & Footer Tools Design tab, select Header & Footer Elements, Picture. Excel displays the Insert Online Picture dialog.
4. Select the picture to include in the header. Select a picture and click Insert. Excel adds the text &[Picture] to the header. You cannot tell how large the picture will print at this point.
Tip
Keep in mind that you won’t see how large the picture will be until you click outside the header.
5. Click in the spreadsheet to see the size of the picture.
6. If you discover that the picture is too large, click in the header area.
7. From the Header & Footer Tools Design tab, select Header & Footer Elements, Format Picture. The Format Picture dialog appears.
8. In the Format Picture dialog, use the Size section to reduce the scale of the picture.
9. If you want your picture to appear as a watermark behind the spreadsheet, you need to lighten the picture. To do so, click the Picture tab of the Format Picture dialog. Change the Color drop-down to Washout.
Note
If you use the spin button to change the height in the Scale section, the width is automatically changed as well, in order to keep the scale proportional.
10. None of the picture items in the header features Live Preview. To preview your picture, close the dialog and then click outside the header. If the picture is not the way you want it, repeat steps 6 through 9 as necessary.
Using Different Headers and Footers in the Same Document
Excel 2016 allows the following four header and footer scenarios:
The same header/footer on all pages
One header/footer on page 1 and a different header/footer on all other pages
One header/footer on all odd pages and a different header/footer on all even pages
One header/footer on page 1, a second header/footer on even pages, and a third header/footer on all odd pages from 3 on
Excel manages these scenarios by storing three headers for each worksheet. The first header is variously called the odd page header or just the header. As you select and clear the options’ check boxes, the contents of each header remain constant, even though they might be used on different pages. Table 27.1 shows the details of each header option.
If you add a header in Page Layout view, it is known as the odd page header. In the default configuration, Excel displays the odd page header on all pages of the printout.
Tip
To minimize confusion, it is best to select the Options section check boxes Different First Page and Different Odd & Even before entering headers.
Excel has two other sets of headers that are initially hidden. One set is called the First Page Header. The other set is called the Different First Page, which you can select from the Options group on the Header & Footer Tools Design tab. When this option is used, Excel displays the first page header above page 1 and uses the odd page header everywhere else.
Scaling Headers and Footers
Settings in the Page Layout tab allow you to force a worksheet to fit a certain number of pages. If the scaling options require a 75% scale on Sheet1 and a 95% scale on Sheet2, your headings are scaled as well. This causes your page numbers to appear at a different point size in various sections of the report.
Excel offers an option to force all headers and footers to print at 100% scale, regardless of the zoom for the sheet. To select this option, from the Header & Footer Tools Design tab, select Options and clear the Scale with Document check box.
Printing from the File Menu
To access the Print panel, you can either select File, Print or press Ctrl+P. The panel merges settings from the Print and Page Setup dialogs in the middle of the screen and the Print Preview on the right side of the screen. As you update settings in the middle of the screen, the Print Preview updates, which enables you always to see the current preview (see Figure 27.7).
The left side of the screen starts with a very large Print button. Click this button to print the document. The spin button next to the Print button enables you to control the number of copies to print.
The rest of the left panel contains a new kind of gallery. You can see the current choice of the gallery without opening the gallery. If the correct printer is already selected, there is no need to open the drop-down.
Choosing a Printer
When you open the Printer drop-down, Excel displays all the current printers and indicates if the printer is currently online and/or available. This handy improvement enables you to detect if the department printer is in a Paper Jam condition so you can print to a different printer.
Choosing What to Print
As shown in Figure 27.8, the Print What gallery offers Active Sheets, Entire Workbook, and Selection settings. You can further modify these settings by choosing Ignore Print Area.
If you choose the Active Sheets option, the currently selected sheet prints. If you have specified a print area, only that range prints; otherwise, Excel prints the entire used range of the document. However, if you select multiple sheets in Group mode, all the selected sheets print.
If you choose the Entire Workbook option, all the nonhidden worksheets in the workbook print. One advantage to this option is that the pages are numbered consecutively as the printout moves from Sheet1 to Sheet2.
Choosing the Selection option enables you to override the print area temporarily. However, if you need to print one small range of a large report, select that range and then choose the Selection option in the Print What gallery. This prevents you from having to change the Print Area twice.
The Ignore Print Area option causes Excel to ignore any print areas specified previously. This causes the entire used area of the worksheet to be printed.
You can select specific pages to print using the Pages spin buttons. To print a single page, enter that page number in both the Pages and To boxes.
Tip
If a specific property does not appear, you can click the Printer Properties hyperlink at the bottom of the left panel to access the vendor-supplied Printer Property dialog box.
Changing Printer Properties
After you choose a printer, the remaining galleries on the left side of the Print panel are redrawn. If you are printing to an office printer that supports collating and stapling, use the galleries to select each of these options. If you are printing to a home printer that does not have these options, Excel does not show those galleries.
Changing Some of the Page Setup Settings
Even though it might seem like they are out of place, the last settings on the left side of the Print panel are used to control portrait versus landscape, paper size, and margins. If you change a setting here, it will also change in the Page Setup dialog.
If your initial reaction is to wonder why these settings are repeated here, you might also wonder why your favorite Page Setup settings are not also repeated. Even though it is nice to switch from portrait to landscape here, it would also be nice to be able to change the Page Scaling or Rows to Repeat at Top settings here. However, this cannot be done because those settings require you to close the Print panel and to use the Page Layout tab of the ribbon.
Using Page Layout View
When you open Excel, the default view is called Normal view. In legacy versions of Excel, your only choices were Normal view and Page Break Preview mode. However, beginning with Excel 2007, Microsoft added the Page Layout view, which works well when you are preparing a document for printing.
In Excel 2016, the three views are available either in the View tab or on the right side of the status bar.
In Page Layout view, you have a fully functioning worksheet. For example, the formula bar works and you can scroll around the worksheet. However, listed next are the differences between Page Layout and Normal view:
White space appears to show the margins on each page. This is usually an advantage because you have a clear view of any page breaks between columns or rows. If you want to hide the white space, you can click the white space and choose Hide White Space.
A ruler appears below the formula bar that you can use to change margins by dragging the gray areas of the ruler.
Areas are marked Click to Add Header and Click to Add Footer. Whereas headers and footers are buried in legacy versions of Excel, in the Page Layout view of Excel 2016 it is obvious that headers and footers are available.
Areas outside the data area of a worksheet are marked with Click to Add Data. One of the problems with Page Break Preview mode is that areas outside the data area were grayed out. However, the Click to Add Data labels option invites you to continue adding pages to your worksheet.
Tip
Keep in mind that Excel does not restore the Freeze Panes settings when you return to Normal view.
The only disadvantage to Page Layout view is that Excel turns off your Freeze Panes settings in Page Layout view. Excel warns you that this is happening. Excel does this to emphasize that Print Titles are different from Freeze Panes.
Exploring Other Page Setup Options
Other page setup options are scattered throughout the various interface areas. Although some of these are fairly obscure, you might need to use them in certain situations.
Printing Gridlines and Headings
To print the gridlines on a worksheet, from the Page Layout tab select Sheet Options, Gridlines, Print.
You can also print the A-B-C column headings and 1-2-3 row headings. To do this, from the Page Layout tab, select Sheet Options, Headings, Print. This option is helpful when you are printing formulas using the FORMULATEXT function and you need to see the cell address of each cell.
Centering a Small Report on a Page
Small reports can look out of place printed in the upper-left corner of a page. Rather than increasing margins, you can choose to center the report horizontally or vertically on a page.
Select Page Layout, Margins, Custom Margins to display the Page Setup dialog. Two check boxes at the bottom of the dialog center the report on the page.
Replacing Error Values When Printing
Excel calculations sometimes result in various errors such as #N/A! or DIV/0. Although these error values help you determine how to fix the errors, they look out of place on a printed page. You can choose to replace any error cells with a blank or two hyphens.
Choose View, Print Titles to open the Sheet tab of the Page Setup dialog. Open the Cell Errors As drop-down and choose <blank> or --.
Printing Comments
Cell comments often appear as a tiny red triangle in a cell. You can print a table of all the comments at the bottom of your report. Use the Comments drop-down in the Sheet tab of the Page Setup dialog and choose At End of Sheet.
Excel prints your report and then starts a new page listing each comment. The new page shows the cell and the comment content.
The other option for printing comments is to print any visible comments where they are currently displayed. To show all comments, choose Review, Show All Comments. When comments are displayed, you can drag them to a new location so they are not covering up important cells.
Controlling the First Page Number
You might be inserting a printed Excel worksheet in the middle of a printed Word document. If the Excel worksheet is appearing as the tenth page in the Word report, for example, you would want the Excel page numbers to start at 10 instead of 1.
From the Page Layout tab, choose the dialog launcher at the bottom right of the Page Setup group. Excel displays the Page tab of the Page Setup dialog. The last setting is First Page Number and is initially set to Auto. Type 10 in this box, and Excel prints the Excel worksheet using page numbers 10, 11, 12, and so on.