Section 6.2. Printing

6.2. Printing

Printing in Excel is pretty straightforwardas long as your spreadsheet fits on a normal 8.5 x 11-inch piece of paper. If you're one of the millions of spreadsheet owners who don't belong to that club, then welcome to the world of Multiple Page Disorder: the phenomenon in which pages and pages of apparently unrelated and noncontiguous columns start spewing from your printer. Fortunately, Excel comes with a slew of print-tweaking tools designed to help you control what you're printing. First off, though, it helps to understand the settings Excel uses when you click the print button:

Note: You can change most of the settings listed; the following is just a list of what happens if you don't adjust any settings before printing a spreadsheet.

In the printout, Excel uses all the formatting characteristics you've applied to the cells, including fonts, fills, and borders. However, Excel's gridlines, row headers, and column headers don't appear in the printout.
If your data is too long (all the rows won't fit on one page) or too wide (all the columns won't fit), Excel prints the data on multiple pages. If your data is both too long and too wide, Excel prints in the following order: all the rows for the first set of columns that fit on a printed page, then all the rows for the next set of columns that fit, and so on (this is known as "down, then over"). When printing on multiple pages, Excel never prints part of an individual column or row.
Excel prints your file in color if you use colors and you've got a color printer.
Excel sets margins to 1 inch on the top and bottom of the page and 0.75 inches on the left and right sides of the page. It doesn't include headers and footers (so you won't see any page numbers).
Excel doesn't include hidden rows and columns in the printout.

6.2.1. How to Print an Excel File

To print an Excel file, follow these steps:

Select File Print.
Select a printer from the drop-down list.
When the Print dialog box first appears, Excel automatically selects your default printer. If you have more than one printer installed and you want to use a different printer, you need to select this printer from the Name pull-down menu. You can also adjust printer settings by clicking the Properties button. Every printer has its own set of options here, but common Properties settings include print quality, paper handling, and page order.

Figure 6-10. The Excel Print dialog box looks more or less like the Print dialog box in other Windows programs. The key difference is the "Print what" box, which lets you choose to print the current worksheet, all worksheets, or a selected range of cells.
Choose what you want to print from the "Print what" box.
The standard option, "Active sheet(s)," prints the current worksheet. If you've grouped more than one worksheet together using the techniques described on Section 5.1.3, Excel prints all the selected worksheets, one after the other. If you select "Entire workbook," Excel prints all the worksheets in your file. Finally, to print out just a portion of a worksheet, select a range of cells, columns, or rows, and then choose Selection.
Use the "Print range" box to limit the number of pages that Excel prints.
If you choose All in the "Print range" box, Excel prints as many pages as it needs to output all the data you've chosen in the "Print what" box. As an alternative, you can choose a range of pages using the Page(s) option. For example, you can choose to print only the first three pages by printing pages from 1 to 3. You can also print just the fourth page by printing from 4 to 4.
Note: To use the "Print range" box effectively, you need to know how many pages your worksheet takes up, print-wise, and what data appears on each page. Excel's Print Preview feature, described on Section 6.2.3, is just the ticket.
Use the "Number of copies" box to print multiple copies of your data.
If you want to print more than one copy of your data, change the "Number of copies" text box accordingly. The Collate option determines whether Excel duplicates each page separately. For example, if you print 10 pages and don't turn on Collate, Excel prints 10 copies of page 1, 10 copies of page 2, and so on. If you do turn Collate on, Excel prints the entire 10-page document, then prints out another copy, and so on. You still end up with 10 copies of each pageplus, for added convenience, they're grouped together.
Click OK to send the spreadsheet to the printer.
Excel prints your document using the settings you've selected.

Tip: If you're printing a very large worksheet, Excel shows a Printing dialog box for a few seconds as it sends the pages to the printer. If you decide to cancel the printing processand you're quick enoughyou can click the Cancel button in this Printing dialog box to stop the operation. If you don't possess the cat-like reflexes you once did, you can also open your printer queue to cancel the process. Look for your printer icon in the notification area at the bottom right of your screen, and double-click that icon. Then, select the offending print job in the list, and press the Delete key (or choose Document

Cancel from the menu). Some printers also provide their own cancel button that lets you stop a print job even after its left your computer.

GEM IN THE ROUGH
Printing Parts of a Spreadsheet

When working with large worksheets, you often want to print only a small portion of your total data. Excel gives you several ways to limit your printout. You can hide the rows or columns you aren't interested in (as described on Section 6.1.4), or you can select the cells you want to print and choose Selection from the "Print what" box in the Print dialog box. But if you frequently need to print the same area, a better option is to define and use a print area.

A print area designates a portion of your worksheet as the only region that Excel will print. (The one exception is if you choose Selection from the "Print what" box, in which case Excel prints the selected cells, not the print area.) Once you define a print area, Excel retains it until you remove it.

That means you can make changes, save, close, and open your spreadsheet, and the same print area remains in place.

To set a print area, select the rows, columns, or group of cells, and choose File Print Area Set Print Area from the menu. The portion of the worksheet that youve highlighted now has a thin dashed outline, indicating it's the only region Excel will print. You can have only one print area at a time, and setting a new one always clears the previous one. To remove your print area so that you can print the entire worksheet, choose File Print Area Clear Print Area.

6.2.2. Customizing Print Settings

Excel's standard print settings are fine if you've got a really small amount of data in your worksheet. But most times, you'll want to tweak these settings so that what you print out is easy to read when it's on paper. The Page Setup dialog box is your control center: it lets you do everything from adding headers and footers to shrinking the size of your data so you can cram more information onto a single printed page.

To display the Page Setup dialog box (Figure 6-11), select File Page Setup. Excel divides the dialog box into four tabs of options, which are described in the following sections.

Figure 6-11. The Page Setup dialog box, on the Page tab.

6.2.2.1. Page settings

The Page tab of the Page Setup dialog box contains two of Excel's most useful print toolsOrientation and Scalingplus a handful of other, less frequently used options:

Orientation is the most important setting in the Page tab. This setting lets you tell Excel whether you're printing on pages that are upright (in portrait mode) or turned horizontally on their sides (in landscape mode). If Excel is splitting your rows across multiple pages when you print out (using the "down then over" process just described), changing the orientation to landscape prints your columns across a page's long edge.
Scaling lets you fit more rows and columns on a page, by shrinking everything proportionally. For example, if you reduce scaling to 50 percent (using the "Adjust to" box), you fit twice as many columns and rows on a page. (Keep in mind that the font size is smaller, and it may be hard to read.) Conversely, you can use scaling to enlarge your data.
But the options don't end there: you can also use the "Fit to" options to cram your data into a fixed number of pages. For example, if you choose one page tall and one page wide, Excel shrinks your entire worksheet so that everything fits into one page. "Fit to" is tricky to get right (and can lead to hopelessly small text), so it's wise to click Print Preview to see what you've done before you print out your worksheet.

Note: Page Break Preview mode, described on Section 6.2.4, gives you yet another way to squeeze more data onto a single page.

Paper size lets you specify other paper dimensions (besides the standard Letter size), such as large legal-size sheets of paper. Remember, though, that you need to have this paper in your printer before you start the print job.
Print quality lets you adjust the resolution your printer uses for printing text. The options here depend on your printer's abilities, although any value of 300 dots per inch (dpi) or greater is sufficient for text, while 600 dpi or greater produces the best quality for charts, bitmaps, or finely detailed shading patterns.
First page number tells Excel the page number to begin with when it's printing page numbers. (This setting has an effect only when you've inserted page numbers in headers or footers, as explained on Section 6.2.2.3.) For example, if you're printing out a workbook and want the first page to be numbered 4, type 4 into the box. If you leave this setting at Auto (rather than typing in a number), the first page is always 1.

6.2.2.2. Margins settings

The Margins tab of the Page Setup dialog box (Figure 6-12) lets you adjust the size of your printed page's margins (the space between your worksheet data and the edge of the page). The margin numbers indicate the distance between the item indicated (for example, the Top of the page, or the Bottom Footer) and the edge of the paper. All you need to do is modify the numbers in the various boxes around the Margin tab.

Figure 6-12. Excel allocates space at the top and bottom of your printout for a header or footer. In this example, Excel has set the header margin to 0.5, which means that any header information appears half an inch below the top of the page; and it's set the top margin to 1, meaning the worksheet data appears one inch below the top of the page.

Note: The units Excel uses for margins depend on the regional settings on your computer (which you can adjust through the Regional and Language Options icon in the Windows Control Panel). Unfortunately, Excel doesn't indicate the type of units it uses in the Page Setup dialog box, and it doesn't give you any choice to override your regional settings and use different units.

As an example, if you set the header margin to 0.5, when you print your worksheet any header information appears half an inch below the top of the page. If you set the top margin to 1, the worksheet data appears one inch below the top of the page.

Tip: When adjusting either of these settings, be careful to make sure that you always set the top margin larger than the header margin; otherwise, your worksheet's data prints on top of your header. The same holds true with footers when changing the bottom margin. If you aren't using headers or footers, it doesn't matter what you set the margins at.

Logically enough, if you reduce the size of your margins, you have more room to print your stuff. However, you can't completely eliminate your margins. Most printers require at least a little space (usually no less than .25 inches) to grip onto the page, and you won't be able to print on this part (the very edge of the page). If you try to make the margins too small, Excel won't inform you of the problem; instead, it just sticks with the smallest margin your current printer allows. This behavior is different than other Microsoft Office programs (like Word). To see it in action, try setting your margins to 0 and looking at the result in the print preview window. You'll see there's still a small margin left between your data and the page borders.

Tip: A good rule of thumb is to adjust margins symmetricallyprintouts tend to look nicest that way. So, for example, if you shrink the left margin to 0.5, make the same change to the right margin. Generally, if you want to fit more data and you don't need any header or footer space, you can safely reduce all your margins to 0.5. If you really want to cram in the maximum amount of data, you can try 0.25, but that's the absolute minimum margin that most printers allow.

When you have only a few rows or columns of information, you may want to use one of the "Center on page" options at the bottom of the tab. Select Horizontally to center your columns between the left and right margins. Select Vertically to center your data between the top and bottom of the page.

6.2.2.3. Creating headers and footers

The Header/Footer tab of the Page Setup dialog box lets you create a new header or footer using one of the settings Excel provides, or by creating your own. The header contains the text you want Excel to print at the top of every page in your printout, while the footer contains text you want Excel to print at the bottom of every page. You can use one, both, or neither in a printout.

The easiest way to create a header or footer is to use one of Excel's suggestions, which you find when you click either the Header or Footer pull-down menu. When you make a selection, you see a preview indicating what the header and footer will look like on the page. (Another way to see exactly what they'll look like is via the Print Preview window, described on Section 6.2.3.)

Some of the options Excel suggests for a header or footer include:

Page numbering (for example, Page 1 or Page 1 of 10)
Worksheet name (for example, Sheet 1)
File name (for example, myfile.xls or c:\MyDocuments\myfile.xls)
The name of the person who created the document and the date it was created
A combination of the above information

The header and footer options are the same. It's up to you to decide whether you want page numbering at the bottom and a title at the top, or vice versa.

If none of the standard options sounds enticing, you can click the Custom Header or Custom Footer button to display the Header and Footer dialog boxes (respectively) and create your own header or footer. You can add two types of information to a custom header or footer: literal text and placeholders. Literal text consists of ordinary characters that you type, which Excel then copies directly into the header or footer. Placeholders are special codes Excel replaces with a corresponding value. For example, you can insert codes that represent the current page number, the total number of pages, the date, the file name, and so on.

In the Header (or Footer) dialog box you see three sections, representing left-aligned, centered, and right-aligned text. You can use one section, or any combination of all three. Inside these boxes, simply type the text and codes that you want to appear in your header or footer. Fortunately, you don't have to remember Excel's codes for the page number, the current date, yadda yadda yadda. Instead, you can click the appropriate button in the Header or Footer window (Figure 6-13) to insert the code you want.

The Header and Footer dialog boxes also offer options that let you set the font or insert a graphic.

6.2.2.4. Sheet settings

The Sheet tab of the Page Setup dialog box includes a collection of miscellaneous settings that let you control things like whether the gridlines in a spreadsheet appear in a printout. It also contains a handy tool for printing column titles on the top of every page.

The Sheet tab includes the following settings:

Print area lets you specify the range of cells you want to print. While this tool definitely gets the job done, an easier way is to use the Print Area tool (described in the box on Section 6.2.2). Some people find using the Selection setting in the Print dialog box (Section 6.2.1) is also a more efficient method.
Print titles lets you print specific rows at the top of every page, or specific columns on the left side of every page. For example, you could use the setting shown in Figure 6-14 to print column titles on the top of every page.

Figure 6-13. When you create your own header or footer, a special dialog box appears with three sections, representing left-aligned, centered, and right-aligned text. Inside these boxes, simply type the text (or click to insert the codes) that you want to appear in your header or footer.

Note: The first three settings of the Sheet tab"Print area" and the two "Print titles" settingsask you to type in a range of cells. If you click the icon with the arrows at the right end of the text box, you can select the cells directly from the worksheet, rather than typing in the range manually. (See Section 3.1.2 for more on manually indicating cell ranges.)

Figure 6-14. The Sheet tab lets you set a bunch of miscellaneous of options. In this example, Excel uses the "Print titles" section to ensure that every page in this printout will display the first row of the spreadsheet as well as the first column.

Gridlines prints the grid of lines separating columns and rows that you see on your worksheet.
Row and column headings prints the column headers (which contain the column letters) at the top of each page and the row headers (with the row numbers) on the left side of each page.
Black and white tells Excel to render all colors as a shade of gray, regardless of your printer settings.
Draft quality tells Excel to use lower-quality printer settings to save toner and speed up printing, assuming your printer has these features, of course.
Comments lets you print the comments that you've added to a worksheet. (You add a comment to a worksheet by clicking on a cell and selecting Insert Comment.) Excel can either append comments to the cells in the printout or add them at the end of the printout, depending on the option you select.
Cell errors lets you tell Excel how you want it to print a cell that happens to contain a formula with an error. You can choose to print the error that's shown (the standard option), or replace the error with a blank value or the text #NA (meaning not available). You learn much more about formulas in Chapter 7.
Page order sets the way Excel handles a large worksheet that's too wide and too long for the printed page's boundaries. If you choose "Down, then over" (the standard option), Excel starts by printing all the rows in the first batch of columns. Once it's finished this batch, Excel then moves on to the next set of columns, prints those columns for all the rows in your worksheet, and so on. If you chose "Over, then down," Excel moves across your worksheet first. That means it prints all the columns in the first set of rows. After it's printed these pages, it moves to the next set of rows, and so on.

6.2.3. Getting a Print Snapshot

You don't have to be a tree-loving environmentalist to want to minimize the number of pages you print out. Thanks to the Print Preview tool (Figure 6-15), you can see what your printout looks like before it appears on paper. This tool is especially helpful if you've just tweaked a bunch of the Page Layout settings (described in the preceding sections) and you first want to see what the effects will be before clicking Print. To see the Print Preview for a worksheet, select File Print Preview from the menu. (You can also click the Preview button you find in the Print dialog box.)

How does the Print Preview differ from your worksheet view? There are several notable differences, including:

The Print Preview paginates your data. That means that you see exactly what fits on each page and how many sheets of paper your printout will require.

Figure 6-15. A print preview shows you exactly what your printout looks like. In this example, the worksheet has 21 columns, but since 21 columns are wider than the width of a single printed page, Excel starts by printing the leftmost four columns.

The Print Preview reveals any headers and footers you've set as part of the page setup. These details don't appear in the Normal worksheet view (View Normal).
The Print Preview doesn't show anything that Excel won't print, like the cell gridlines or the letters at the top of each column (unless you've specified otherwise). On the other hand, Excel does display the details that do make a difference, like the text font.

When you first display the Print Preview window, you see a replica of the first page of your current worksheet as it would appear if you printed it out. To jump in for a closer look, click anywhere on the preview page (notice that the pointer has become a magnifying glass), or click the Zoom button. Doing so magnifies the sheet to 100 percent zoom, so you can more clearly see the text and details. (The preview window supports only two levels of zoom: 100 percent and full page view. To return to full-page view, click the page or click the Zoom button again.)

To move from page to page, you can use the scroll bar at the side of the window, or you can click the Next button at the top of the window. You can also use the Page Up and Page Down keys.

Tip: If you only want to see how a select group of cells will print out, first set a Print Area (Section 6.1.4). Now Print Preview displays only the data that's inside the print area you've designated.

Here are some of the tasks you can perform from the Print Preview window:

If the print preview meets with your approval, click Print to send the document to the printer.
If you want to tweak some of the page setup options, click Setup. You can also click Page Break Preview to look at a different type of preview, which shows page regions on the worksheet. You can find out more about the Page Break Preview feature on Section 6.2.4.
If you want to adjust the page margins right there in the preview window, click Margins. Dotted lines appear on the sides of the page to indicate where the page-margin and header and footer regions are, as shown in Figure 6-16. You can drag these lines to change the margins. Tab markers also appear, which let you adjust the width of columns.

Figure 6-16. The Print Preview window lets you set margins using your mouse.

Note: When setting the top and bottom margins, make sure you don't drag the page margin line beyond the line used to delineate the header or footer. If you do, your header or footer overlaps your worksheet's data.

If you're ready to return to the normal worksheet view, where you can edit data and use all of Excel's features, click Close.

Note: If you look carefully, you can get a hint about page breaks before you even open the Print Preview window. In Normal view (View

Normal), Excel uses a dashed line in lieu of a normal grid line to indicate where a page break occurs. If youre having trouble spotting these dashed lines, you can also use the Page Break Preview window, which is described in the next section.

6.2.4. Page Break Preview: A Bird's-Eye View of Your Worksheet

Print Preview is good for getting a snapshot of how an individual page is going to print out, but it's not the only print preview tool that Excel gives you. Page Break Preview is a great way to get a bird's-eye view of how an entire worksheet is going to print out. It's particularly useful if your worksheet is made up of lots of columns. That's because Page Break Preview zooms out so you can see a large amount of data at once, and it uses thick blue dashed lines to show you where page breaks occur, as shown in Figure 6-17. In addition, the Page Break Preview numbers every page, placing the label "Page X" ("X" is the page number) in large gray lettering in the middle of each page.

Note: Excel never breaks a printout in the middle of a column or row.

To preview the page breaks in your data, select View Page Break Preview. A window appears, informing you that you can use Page Break Preview mode to move page breaks. You can choose whether you want to see this message again; if not, turn on the "Do not show this dialog again checkbox before clicking OK.

Once you're in Page Break Preview mode, you can do all of the things you do in Normal view mode, including editing data, formatting cells, and changing the zoom percentage to reveal more or fewer pages. You can also click the blue dashed lines that represent page breaks and drag them to include more or less rows and columns in your page.

Figure 6-17. Here's a large worksheet in Page Break Preview mode. The worksheet is too wide to fit on one page (at least in portrait orientation), and the thick dashed line indicates that the page breaks after column D and before row 53. You can change the zoom percentage (View Zoom) to reveal more or fewer pages.

There are two types of changes you can make with page breaks:

You can make less data fit onto a page. To do so, drag the bottom page break up or the left-side page break to the right. Usually, you perform these steps if you notice that a page break occurs in an awkward place, like just before a row with some kind of summary or subtotal.
You can make more data fit onto a page. To do so, drag the bottom page break down or the left-side page break to the left.

Of course, everyone wants to fit more information onto their printouts, but there's only so much space on the page. So what does Excel do when you expand a page by dragging the page break? It simply adjusts the Scaling setting in the Page tab of the Page Setup dialog box. The larger you make the page, the smaller the Scaling percentage setting becomes. That means your printed text may end up too tiny for you to read. (The text on your computer's display won't change, however, so you won't have any indication of just how small your text has become until you view a printout.)

Tip: Scaling affects all the pages in your printout. That means when you drag one page break to expand a page, you actually end up compressing all the pages in your workbook. However, the page breaks won't change for other pages, which means you may end up with empty, unused space on some of the pages.The best advice is this: if your goal is merely to fit more information into an entire printout, change the Scaling percentage manually (in the Page Setup dialog box), instead of using the Page Break view. On the other hand, if you need to squeeze just a little bit more data onto a specific page, use the Page Break view.