Normal view

Most of the time when you work in Excel, you use Normal view. Normal view can display 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, insert 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.

Figure 7.1 shows a part of a worksheet in Normal view, zoomed to about 50%, and with gridlines turned off. You can see the dotted line between columns D and E that indicates a page break.

Page Layout view

Unlike the preview in Backstage view (choose File ➪ Print), Page Layout view 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 7.2 shows a worksheet in Page Layout view, zoomed to about 40% to show multiple pages. Contrary to Normal view, you can see the margins, the page header and footer (if any), and even some space separating each page. If you've specified any repeated rows and columns, they are also displayed—giving you a true preview of the printed output.

Page Break Preview

Page Break Preview displays the worksheet and the page breaks. Figure 7.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. You can also drag the edges of the print area to change its size (if you've set a print area). Unlike Page Layout view, Page Break Preview does not display margins, headers, or footers.

When you enter Page Break Preview, 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 areas appear 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.

To exit Page Break Preview, just click one of the other View icons on the right side of the status bar.

Choosing your printer

To switch to a different printer or output device, choose File ➪ Print, and use the drop-down control in the Printer section to select a different installed printer.

Specifying what you want to print

Sometimes you may want to print only part of the worksheet rather than the entire used area. Or you may want to reprint selected pages of a report without printing all the pages. Choose File ➪ Print and use the controls in the Settings section to specify what to print.

You have several options:

• Print Active Sheets  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.
• Print Entire Workbook  Prints every sheet in the workbook, including chart sheets.
• Print Selection  Prints only the range that you selected before choosing File ➪ Print.
• Print Selected Chart  Appears only if a chart is selected. If this option is chosen, only the chart will be printed.
• Print Selected Table  Appears only if the active cell is within a table (created by choosing Insert ➪ Tables ➪ Table) when the Print settings screen is displayed. If this option is chosen, only the table will be printed.

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 by using Pages controls in the Settings section. You can either use the spinner controls or type the page numbers in the text boxes.

Changing page orientation

Page orientation refers to the way 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 onscreen page breaks adjust automatically to accommodate the new paper orientation.

Page orientation settings are also available when you choose File ➪ Print.

Specifying paper size

Choose Page Layout ➪ Page Setup ➪ Size to specify the paper size you're using. The paper size settings are also available when you choose File ➪ Print.

Printing multiple copies of a report

Use the Copies control at the top of the Print tab in Backstage view to specify the number of copies to print. Just enter the number of copies you want and then click Print.

Adjusting the page margins

Margins are the unprinted areas along the sides, top, and bottom of a printed page. Excel provides four “quick margin” settings; 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.

In Page Layout view, a ruler is displayed above the column headers and to the left of the row headers. 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.

From the Page Layout ➪ Page Setup ➪ Margins drop-down list, you can select Normal, Wide, Narrow, or the Last Custom Setting if you previously customized the margins. These options are also available when you choose File ➪ Print. If none of these settings does the job, choose Custom Margins to display the Margins tab of the Page Setup dialog box, as shown in Figure 7.4.

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.

You can also adjust margins in the preview window in Backstage view (choose File ➪ Print). Click the Show Margins button in the bottom-right corner to display the margins in the preview pane. Then drag the margin indicators to adjust the margins.

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.

By default, Excel aligns the printed page at the top and left margins. If you want the output to be centered vertically or horizontally, select the appropriate check box in the Center on Page section of the Margins tab.

Understanding page breaks

When printing lengthy reports, controlling where pages break is often important. For example, you probably don't want a row to print on a page by itself, nor do you want a table header row to be the last line on a page. 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. For example, if your worksheet consists of several distinct sections, you may want to print each section on a separate sheet of paper.

Printing row and column titles

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 don't appear. To resolve this problem, you can choose to print selected rows or columns as titles on each page of the printout.

Row and column titles serve pretty much the same purpose on a printout as frozen panes do in navigating within a worksheet. Keep in mind, however, that these features are independent of each other. In other words, freezing panes doesn't affect the printed output.

You can specify rows to repeat at the top of every printed page or 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, as shown in Figure 7.5.

Figure 7.5 shows that row 1 will repeat at the top of the page and columns A and B will repeat on the left of the page. Even if you only want one row or column, you have to include the colon. To set these properties, activate the appropriate box (either Rows to Repeat at Top or Columns to Repeat at Left) 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.

Scaling printed output

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, choose Page Layout ➪ Scale to Fit ➪ Scale. 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 ➪ Scale to Fit ➪ Width and Page Layout ➪ Scale to Fit ➪ Height. When you change either one of these settings, the corresponding scale factor is displayed in the Scale control.

Printing cell gridlines

Typically, cell gridlines aren't printed. If you want your printout to include the gridlines, choose Page Layout ➪ Sheet Options ➪ Gridlines ➪ Print.

Alternatively, you can insert borders around some cells to simulate gridlines. Change the border color to White, Background 1, 25% Darker to get a pretty good simulation of a gridline. To change the color, choose Home ➪ Font ➪ Borders ➪ More Borders. Make sure you change the color before you apply the border.

Printing row and column headers

By default, row and column headers for a worksheet are not printed. If you want your printout to include these items, choose Page Layout ➪ Sheet Options ➪ Headings ➪ Print.

Using a background image

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 misleading. Background images placed on a worksheet are never printed.

Selecting a predefined header or footer

You can choose from a number of predefined headers or footers by using either of the two drop-down lists in the Header & Footer ➪ 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 7.6 shows an example of a header that uses all three sections.

Understanding header and footer element codes

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 ➪ Header & Footer Elements group. Each button inserts a code into the selected section. For example, to insert the current date, click the Current Date button. Table 7.2 lists the buttons and their functions.

You can combine text and codes and insert as many codes as you like into each section.

You can also use different fonts and sizes in your headers and footers. Just select the text 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.

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 VBA macro to perform this operation before the sheet is printed.

Exploring other header and footer options

When a header or footer is selected in Page Layout view, the Header & Footer ➪ Options group contains controls that let you specify other options:

• Different First Page  If this option is checked, you can specify a different header/footer for the first printed page.
• Different Odd & Even Pages  If this option is checked, you can specify a different header/footer for odd and even pages.
• Scale with Document  If this option is checked, the font size in the header and footer will be sized accordingly if the document is scaled when printed. This option is enabled by default.
• Align with Page Margins  If this option is 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. This option is enabled by default.

Copying Page Setup settings across sheets

Each Excel worksheet has its own print setup options (orientation, margins, headers and footers, and so on). These options are specified in the Page Setup group of the Page Layout tab.

When you add a new sheet to a workbook, it contains the default page setup settings. Here's an easy way to transfer the settings from one worksheet to additional worksheets:

1. Activate the sheet that contains the desired setup information. This is the source sheet.
2. Select the target sheets, and Ctrl+click the sheet tabs of the sheets that you want to update with the settings from the source sheet.
3. Click the dialog box launcher in the lower-right corner of the Page Layout ➪ Page Setup group.
4. When the Page Setup dialog box appears, click OK to close it.
5. Ungroup the sheets by right-clicking any selected sheet and choosing Ungroup Sheets from the shortcut menu. Because multiple sheets are selected when you close the Page Setup dialog box, the settings of the source sheet will be transferred to all target sheets.

Preventing certain cells from being printed

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:

• Hide rows or columns  When you hide rows or columns, the hidden rows or columns aren't printed. Choose Home ➪ Cells ➪ Format drop-down list to hide the selected rows or columns.
• Hide cells or ranges by making the text color the same color as the background color  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.
• Hide cells or ranges by using a custom number format  You can hide cells by using a custom number format that consists of three semicolons (;;;). See Chapter 2, “Entering and Editing Worksheet Data,” for more information about using custom number formats.
• Mask an area  You can mask 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 views of your worksheet.”) This feature allows you to create a named view that doesn't show the confidential information.

Preventing objects from being printed

To prevent objects on the worksheet (such as charts, shapes, and SmartArt) from being printed, you need to access the Properties tab of the object's Format task pane (see Figure 7.7):

1. Right-click the object and choose Format xxxx from the shortcut menu. (xxxx varies, depending on the object.)
2. In the Format task pane that opens for the object, click the Size & Properties icon.
3. Expand the Properties section of the task pane.
4. Remove the check mark for Print Object.

Creating custom views of your worksheet

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 Views feature enables you to give names to various views of your worksheet. 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 Layout ➪ Sheet Options groups
• Hidden rows and columns
• The worksheet view (Normal, Page Layout, 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 before printing and then changing them back, using custom named views can save you some work.

To create a named view, follow these steps:

1. Set up the view settings the way you want them. For example, hide some columns.
2. Choose View ➪ Workbook Views ➪ Custom Views. The Custom Views dialog box appears.
3. Click the Add button. The Add View dialog box (shown in Figure 7.8) appears.

   

4. Provide a descriptive name. 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.
5. Click OK to save the named view.

Then when you're ready to print, open the Custom Views dialog box to see 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.

Creating PDF files

The PDF file format is widely used as a way to present information in a read-only manner with precise control over the layout. If you need to share your work with someone who doesn't have Excel, creating a PDF is often a good solution. Free software to display PDFs is available from a number of sources.

XPS is another “electronic paper” format, developed by Microsoft as an alternative to the PDF format. Currently, there is little third-party support for the XPS format.

To save a worksheet in PDF or XPS format, choose File ➪ Export ➪ Create PDF/XPS Document ➪ Create PDF/XPS. Excel displays the Publish as PDF or XPS dialog box, in which you can specify a filename and location and set some other options.