Despite predications of the “paperless office,” reports printed on paper remain commonplace, and they will be around for a long time. Many of the worksheets that you develop with Excel can easily serve as printed reports. You’ll find that printing from Excel is quite easy and that you can generate attractive, well-formatted reports with minimal effort. In addition, Excel has many options that provide you with a great deal of control over the printed page so that you can make your printed reports even better. These options are explained in this chapter.
If you simply want to print a copy of a worksheet with no fuss and bother, use Excel’s Quick Print option. One way to access this command is to choose Office 
Print Quick Print. But if you like the idea of one-click printing, take a few seconds to add a new button to your Quick Access Toolbar (QAT):
Click the downward-point arrow to the right of the QAT, which displays a menu.
Choose Quick Print from the menu. Excel adds the Quick Print icon to your QAT.
Clicking the Quick Print button prints the current worksheet on the currently selected printer, using the default print settings. If you’ve changed any of the default print settings, Excel uses the new settings; otherwise, it uses the following default settings:
Prints the active worksheet (or all selected worksheets), including any embedded charts or objects.
Prints one copy.
Prints the entire worksheet.
Prints in portrait mode.
Doesn’t scale the printed output.
Uses letter-size paper with .75-inch margins for the top and bottom and .70-inch margins for the left and right margins (for the U.S. version).
Prints with no headers or footers.
Doesn’t print cell comments.
Print with no cell gridlines.
For wide worksheets that span multiple pages, prints down and then over.
Note
When you print a worksheet, Excel prints only the active area of the worksheet. In other words, it won’t print all 17 billion cells—just those that have data in them. If the worksheet contains any embedded charts or other graphic objects (such as SmartArt or Shapes), they’re also printed.
Tip
To quickly determine the active area of the worksheet, press Ctrl+End to move to the last active cell in the worksheet. The active area is between cell A1 and the last active cell. You may notice that Ctrl+End isn’t always accurate. For example, if you’ve deleted some rows, Ctrl+End will take you to the last row that you deleted. However, when the sheet is printed, the active area is reset, so the empty rows are not printed.
Using Print Preview
Excel’s Print Preview feature displays a worksheet exactly as it will be printed. To use Print Preview, choose File Print Print Preview. Excel displays the first page of your printed output. To view subsequent pages, choose Print Preview Preview Next Page (or, use the vertical scrollbar).
For easier access to Print Preview add a button to your Quick Access Toolbar (QAT):
Click the downward-point arrow to the right of the QAT, which displays a menu.
Choose Print Preview from the menu. Excel adds the Quick Print icon to your QAT.
The Ribbon displayed in the Print Preview window has a few other commands that you can use while previewing your output. For example, choose Print Preview Preview Show Margins to display margins. Excel adds markers to the preview that indicate column borders and margins. You can drag the column or margin markers to make changes that appear on-screen.
Print Preview is certainly useful, but you may prefer to use the new Page Layout View introduced in Excel 2007. to preview your output (see “Changing Your Page View”). The Print Preview feature remains, but it probably won’t get much usage.
One of the slickest new features in Excel 2007 is Page Layout View, which shows your worksheet divided up into pages. In other words, you’re able to visualize your printed output as you work.
Page Layout View is one of three worksheet views, which are controlled by the three icons in the right side of the status bar. These views are also available in the View Workbook Views group of the Ribbon. The three view options are
Normal View: The default view of the worksheet. This view may or may not show page breaks.
Page Layout View: A view that shows individual pages.
Page Break Preview: A view that lets you manually adjust the page breaks.
Just click one of the icons to change the view. You can also use the Zoom slider to change the magnification from 10% (a very tiny bird’s eye view) to 400% (very large, for showing fine detail).
The following sections describe how these views can help with printing.
Most of the time when you work in Excel, you’ll use Normal View. Normal View displays page breaks in the worksheet. The page breaks are indicated by horizontal and vertical dotted lines. These page break lines adjust automatically if you change the page orientation, add or delete rows or columns, change row heights, change column widths, and so on. For example, if you find that your printed output is too wide to fit on a single page, you can adjust the column widths (keeping an eye on the page-break display) until the columns are narrow enough to print on one page.
Tip
If you’d prefer not to see the page break display in Normal View mode, choose Office Excel Options and select the Advanced tab. Scroll down the section titled Display Options For This Worksheet and remove the check mark from Show Page Breaks. This setting applies only to the active worksheet. Unfortunately, the option to turn off page break display is not in the Ribbon, and it’s not even available for inclusion on the Quick Access Toolbar.
Figure 10.1 shows a worksheet in Normal View mode, zoomed out to show multiple pages. Notice the dotted lines that indicate page breaks.
Page Layout View is the ultimate print preview. Unlike the old print preview, this mode is not a view only mode. You have complete access to all Excel commands. In fact, you can use Page Layout View all the time if you like.
Figure 10.2 shows a worksheet in Page Layout View, zoomed out to show multiple pages. Notice that The page header and footer (if any) appear on each page, giving you a true preview of the printed output.
Page Break Preview displays the worksheet and shows where the page breaks occur. Figure 10.3 shows an example. This view mode is different from Normal View mode with page breaks turned on. The key difference is that you can drag the page breaks. Unlike Page Layout View, Page Break Preview does not display headers and footers.
When you enter Page Break Preview mode, Excel performs the following:
Changes the zoom factor so that you can see more of the worksheet.
Displays the page numbers overlaid on the pages.
Displays the current print range with a white background; nonprinting data appears with a gray background.
Displays all page breaks as draggable dashed lines.
When you change the page breaks by dragging, Excel automatically adjusts the scaling so that the information fits on the pages, per your specifications.
Tip
In Page Break Preview mode, you still have access to all of Excel’s commands. You can change the zoom factor if you find the text to be too small.
To exit Page Break Preview mode, just click one of the other View icons in the status bar.
Simply clicking the Quick Print button (or choosing Office Print Quick Print) may produce acceptable results in many cases, but a little tweaking of the print settings can often improve your printed reports. You can make the most common print adjustments directly from the Page Layout tab of the Ribbon.
Margins are the unprinted areas along the sides, top, and bottom of a printed page. Excel provides four “quick margin” settings, and you can also specify the exact margin size you require. All printed pages have the same margins. You can’t specify different margins for different pages.
If you’re in Page Layout View, a ruler is displayed above the column header and to the left of the row header. Use your mouse to drag the margins in the ruler. Excel adjusts the page display immediately. Use the horizontal ruler to adjust the left and right margins and use the vertical ruler to adjust the top and bottom margins. See Figure 10.4.
If you use the Page Layout Page Setup Margins drop-down list, you can select either Normal, Wide, or Narrow (or the last-used custom margin settings). If none of these settings does the job, choose Custom Margins, which displays the Margins tab of the Page Setup dialog box, shown in Figure 10.5.
To change a margin, click the appropriate spinner (or you can enter a value directly). The margin settings that you specify in the Page Setup dialog box will then be available in the Page Layout Page Setup Margins drop-down list, referred to as Last Custom Setting.
Note
The Preview box in the center of the dialog box is a bit deceiving because it doesn’t really show you how your changes look in relation to the page; rather, it displays a darker line to let you know which margin you’re adjusting.
In addition to the page margins, you can adjust the distance of the header from the top of the page and the distance of the footer from the bottom of the page. These settings should be less than the corresponding margin; otherwise, the header or footer may overlap with the printed output.
Normally, Excel aligns the printed page at the top and left margins. If you want the output to be centered vertically or horizontally, check the appropriate check box in the section of the Margins tab labeled Center On Page.
Page orientation refers to the way in which the output is printed on the page. Choose Page Layout Page Setup Orientation Portrait to print tall pages (the default) or Page Layout Page Setup Orientation Landscape to print wide pages. Landscape orientation is useful when you have a wide range that doesn’t fit on a vertically oriented page.
If you change the orientation, the on-screen page breaks adjust automatically to accommodate the new paper orientation.
Use the Page Layout Page Setup Size control to specify the size of the paper you are using.
By default, Excel prints the entire used range of a worksheet. In some cases, you may want to print only part of the worksheet. To do so, select the range to print and then choose Page Layout Page Setup Print Area Set Print Area.
If you specify a print area for a worksheet, Excel automatically give the print area a name: Print_Area.
If you print lengthy reports, controlling where pages break is often important. For example, you normally wouldn’t want a row to print on a page by itself. Fortunately, Excel gives you precise control over page breaks.
Excel handles page breaks automatically, but sometimes you may want to “force” a page break—either a vertical or a horizontal one—so that the report prints the way you want it to. For example, if your worksheet consists of several distinct sections, you may want to print each section on a separate sheet of paper.
To insert a horizontal page-break line, move the cell pointer to the cell that will begin the new page, but make sure that you place the pointer in column A; otherwise, you’ll insert a vertical page break and a horizontal page break. For example, if you want row 14 to be the first row of a new page, select cell A14. Then choose Page Layout Page Setup Breaks Insert Page Break.
Note
Page breaks are visualized differently, depending on which View mode you’re using. See “Changing Your Page View,” earlier in this chapter.
To insert a vertical page-break line, move the cell pointer to the cell that will begin the new page, but in this case, make sure that you place the pointer in row 1. Choose Page Layout Page Setup Breaks Insert Page Break to create the page break.
To remove a manual page break, move the cell pointer to the first row beneath (or the first column to the right) of the manual page break and then choose Page Layout Page Setup Breaks Remove Page Break.
To remove all manual page breaks in the worksheet, choose Page Layout Page Setup Breaks Reset All Page Breaks.
Would you like to have a background image on your printouts? Unfortunately, you can’t. You may have noticed the Page Layout Page Setup Background command. This button displays a dialog box that lets you select an image to display as a background. Placing this control among the other print-related commands is very misleading. Background images placed on a worksheet are never printed.
Tip
In lieu of a background image, you can insert a Shape, WordArt, or a picture on your worksheet and then adjust its transparency. Then copy the image to all printed pages. Alternatively, you can insert an object in a page header or footer. See the sidebar titled, “Inserting a Watermark.”
Inserting a Watermark
A watermark is an image (or text) that appears on each printed page. A watermark can be a faint company logo, or a word such as DRAFT. Excel doesn’t have an official command to print a watermark, but you can add a watermark by inserting a picture in the page header or footer. Here’s how to do it.
Locate an image on your hard drive that you want to use for the watermark.
Choose View
Workbook Views Page Layout View.Click the center section of the header.
Choose Header & Footer Tools
Header & Footer Elements Picture.Using the Insert Picture dialog box, locate the image from Step 1.
Click outside of the header to see your image.
To center the image in the middle of the page, click the center section of the header and add some carriage returns before the
&[Picture]code. You’ll need to experiment to determine the number of carriage returns.If you need to adjust the image (for example, make it lighter), click the center section of the header and then choose Header & Footer Tools
Header & Footer Elements Format Picture. Use the Image controls in the Picture tab of the Format Picture dialog box to adjust the image. You may need to experiment with the settings to make sure that the worksheet text is legible.The accompanying figure shows an example of a header image (a globe) used as a watermark. You can do a similar thing with text, of course.
If your worksheet is set up with titles in the first row and descriptive names in the first column, it can be difficult to identify data that appears on printed pages where those titles do not appear. To resolve this problem, you can choose to print selected rows or columns as titles on each page of the printout.
Cross-Ref
Row and column titles serve pretty much the same purpose on a printout as frozen panes do in navigating within a worksheet. See Chapter 4 for more information on freezing panes. Keep in mind, however, that these features are independent of each other. In other words, freezing panes does not affect the printed output.
Caution
Don’t confuse print titles with headers; these are two different concepts. Headers appear at the top of each page and contain information, such as the worksheet name, date, or page number. Row and column titles describe the data being printed, such as field names in a database table or list.
You can specify particular rows to repeat at the top of every printed page or particular columns to repeat at the left of every printed page. To do so, choose Page Layout Page Setup Print Titles. Excel displays the Sheet tab of the Page Setup dialog box, shown in Figure 10.6.
Activate the appropriate box and then select the rows or columns in the worksheet. Or you can enter these references manually. For example, to specify rows 1 and 2 as repeating rows, enter 1:2.
In some cases, you may need to force your printed output to fit on a specific number of pages. You can do so by enlarging or reducing the size. To enter a scaling factor, use the Page Layout Scale To Fit Scale control. You can scale the output from 10% up to 400%. To return to normal scaling, enter 100%.
To force Excel to print using a specific number of pages, choose Page Layout Width And Page Layout Height. When you change either one of these settings, the corresponding scale factor is displayed in the Scale control.
Excel doesn’t care about legibility, however. It will gladly scale your output to be so small that no one can read it.
Normally, cell gridlines aren’t printed. If you want your printout to include the gridlines, choose Page Layout Sheet Options Gridlines Print.
A header is information that appears at the top of each printed page. A footer is information that appears at the bottom of each printed page. By default, new workbooks do not have any headers or footers.
You can specify headers and footers by using the Header/Footer tab of the Page Setup dialog box. But this task is much easier if you switch to Page Layout View, where you can click the section labeled Click To Add Header or Click To Add Footer.
Note
If you’re working in Normal view, you can choose Insert Header & Footer. Excel switches to Page Layout View and activates the center section of the page header.
You can then type the information and apply any type of formatting you like. Note that headers and footers consist of three sections: left, center, and right. For example, you can create a header that prints your name at the left margin, the worksheet name centered in the header, and the page number at the right margin.
When you activate the header or footer section in Page Layout View, the Ribbon displays a new context tab called Header & Footer Tools Design. Use the controls in this tab to work with headers and footers.
You can choose from a number of predefined headers or footers by using either of the two drop-down lists in the Header & Footer Tools Design Header & Footer group. Notice that some items in these lists consist of multiple parts, separated by a comma. Each part goes into one of the three header or footer sections (left, center, or right). Figure 10.7 shows an example of a header that uses all three sections.
When a header or footer section is activated, you can type whatever text you like into the section. Or, to insert variable information, you can insert any of several element codes by clicking a button in the Header & Footer Tools Design Header & Footer Elements group.
For example, to insert the current date, click the Current Date button. Each of these buttons inserts a code into the selected section. Table 10.1 lists the buttons and their functions.
Table 10.1. Header & Footer Buttons and Their Functions
Button | Code | Function |
|---|---|---|
Page Number | &[Page] | Displays the page number |
Number of Pages | &[Pages] | Displays the total number of pages to be printed |
Current Date | &[Date] | Displays the current date |
Current Time | &[Time] | Displays the current time |
File Path | &[Path]&[File] | Displays the workbook’s complete path and filename |
File Name | &[File] | Displays the workbook name |
Sheet Name | &[Tab] | Displays the sheet’s name |
Picture | Not applicable | Enables you to add a picture |
Format Picture | Not applicable | Enables you to change the picture’s settings |
You can combine text and codes and insert as many codes as you like into each section.
Note
If the text that you enter uses an ampersand (&), you must enter the ampersand twice (because Excel uses an ampersand to signal a code). For example, to enter the text Research & Development into a section of a header or footer, enter Research && Development.
You also can use different fonts and sizes in your headers and footers. Just select the text that you want to change and then use the formatting tools in the Home Font group. Or use the controls on the Mini toolbar, which appears automatically when you select the text. If you don’t change the font, Excel uses the font defined for the Normal style.
Tip
You can use as many lines as you like. Press Enter to force a line break for multiline headers or footers. If you use multiline headers or footers, you may need to adjust the top or bottom margin so the text won’t overlap with the worksheet data. See “Adjusting the page margins,” earlier in this chapter.
Unfortunately, you can’t print the contents of a specific cell in a header or footer. For example, you may want Excel to use the contents of cell A1 as part of a header. To do so, you need to enter the cell’s contents manually—or write a macro to perform this operation.
The Header & Footer Design Options group contains controls that let you specify other options:
Different First Page: If checked, you can specify a different header/footer for the first printed page.
Different Odd & Even Pages: If checked, you can specify a different header/footer for odd and even pages.
Scale With Document: If checked, the font size in the header and footer will be sized accordingly if the document is scaled when printed.
Align With Page Margins: If checked, the left header and footer will be aligned with the left margin, and the right header and footer will be aligned with the right margin.
Additional print settings are available in the Print dialog box. To display the Print dialog box, select Office Print (or press Ctrl+P). Use this dialog box to select which printer you wish to use, to choose what part of the worksheet you want to print, to specify the number of copies you want, and to access the properties settings for your printer. After you select your print settings, click OK in the Print dialog box to print your work.
Tip
Clicking OK in the Print dialog box without adjusting any settings is the equivalent of clicking Excel’s Quick Print button.
Figure 10.8 shows the Print dialog box; the following sections describe the settings in this dialog box.
The Printer section of the Print dialog box enables you to choose which printer you want to use as well as to access the settings that are specific to the selected printer.
Make sure that you have selected the correct printer (applicable only if you have access to more than one printer). You select the printer from the Name drop-down list. This section of the dialog box also lists information about the selected printer, such as its status and where it’s connected.
Note
If you want to adjust the printer’s settings, click the Properties button to display a property box for the selected printer. The exact dialog box that you see depends on the printer. The Properties dialog box lets you adjust printer-specific settings, such as the print quality and the paper source. In most cases, you won’t have to change any of these settings, but if you’re having print-related problems, you may want to check the settings.
Sometimes you may want to print only a part of the worksheet rather than the entire active area. Or you may want to reprint selected pages of a report without printing all the pages. You can make both of these types of selections in the Print dialog box, too (see Figure 10.8).
The Print What section of the Print dialog box lets you specify what to print. You have several options:
Selection: Prints only the range that you selected before choosing Office
Print.Active Sheet(s): Prints the active sheet or sheets that you selected. (This option is the default.) You can select multiple sheets to print by pressing Ctrl and clicking the sheet tabs. If you select multiple sheets, Excel begins printing each sheet on a new page.
Entire Workbook: Prints the entire workbook, including chart sheets.
Table: Enabled only if the cell pointer is within a table when the Print dialog box is displayed. If selected, only the table will be printed.
Tip
You can also choose Page Layout Page Setup Print Area Set Print Area to specify the range or ranges to print. Before you choose this command, select the range or ranges that you want to print. To clear the print area, choose Page Layout Page Setup Print Area Clear Print Area. To override the print area, select the Ignore Print Areas check box in the Print dialog box.
If your printed output uses multiple pages, you can select which pages to print by indicating the number of the first and last pages to print in the Print Range section of the Print dialog box. You can either use the spinner controls or type the page numbers in the edit boxes.
The Copies section of the Print dialog box lets you specify the number of copies to print. Simply enter the number of copies you want and then click OK to print them.
If your worksheet contains confidential information, you may want to print the worksheet but not the confidential parts. You can use several techniques to prevent certain parts of a worksheet from printing:
When you hide rows or columns, the hidden rows aren’t printed. Use the Home
Cells Format drop-down list to hide the selected rows or columns.You can hide cells or ranges by making the text color the same color as the background color. Be aware, however, that this method may not work for all printers.
You can hide cells by using a custom number format that consists of three semicolons (;;;). See Chapter 24 for more information about using custom number formats.
You can mask off a confidential area of a worksheet by covering it with a rectangle Shape. Choose Insert
Illustrations Shapes and click the Rectangle Shape. You’ll probably want to adjust the fill color to match the cell background and remove the border.
If you find that you must regularly hide data before you print certain reports, consider using the Custom Views feature, discussed later in this chapter (see “Creating Custom View of Your Worksheet”). This feature allows you to create a named view that doesn’t show the confidential information.
To prevent objects on the worksheet (such as charts, Shapes, and SmartArt) from being printed, you need to access the Size And Properties dialog box for the object (see Figure 10.9):
If you need to create several different printed reports from the same Excel workbook, setting up the specific settings for each report can be a tedious job.
For example, you may need to print a full report in landscape mode for your boss. Another department may require a simplified report using the same data, but with some hidden columns in portrait mode. You can simplify the process by creating custom named views of your worksheets that include the proper settings for each report.
The Custom View feature enables you to give names to various views of your worksheet, and you can quickly switch among these named views. A view includes settings for the following:
Print settings, as specified in the Page Layout
Page Setup, Page Layout Scale To Fit, and Page Page Setup Sheet Options groups.Hidden rows and columns
The worksheet view (Normal, Page Layout View, Page Break Preview)
Selected cells and ranges
The active cell
The zoom factor
Window sizes and positions
Frozen panes
If you find that you’re constantly fiddling with these settings and then changing them back, using named views can save you lots of effort.
Caution
Unfortunately, the Custom View feature does not work if the workbook contains at least one table. When a workbook that contains a table is active, the Custom View command is grayed out. This limitation severely limits the usefulness of the Custom View feature.
To create a named view, begin by setting up the view settings the way you want them (for example, hide some columns). Then choose View Workbook Views Custom Views to display the Custom Views dialog box. Click the Add button and provide a descriptive name in the Add View dialog box that appears (see Figure 10.10). You can also specify what to include in the view by using the two check boxes. For example, if you don’t want the view to include print settings, remove the check mark from Print Settings. Click OK to save the named view.
The Custom Views dialog box displays a list of all named views. To select a particular view, just select it from the list and click the Show button. To delete a named view from the list, click the Delete button.