Printing in Excel is pretty straightforwardas long as your spreadsheet fits on a normal 8.5 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 default settings Excel uses when you click the print button:
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 will never print 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.
Printing a worksheet is just like printing in any other Windows application. Follow these steps:
Select File Print.
Figure 6-10. The Excel Print dialog box looks more or less like the Print dialog box in other Windows applications. 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.
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.
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 will print 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 will print.
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. Alternately, 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.
| GEM IN THE ROUGH |
Printing Parts of a Spreadsheet
When working with large worksheets, you'll 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, 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 you've highlighted will now have a thin dashed outline, indicating this is the only region Excel will print. You can only have one print area at a time, and setting a new one will always clear the previous one. To remove your print area so that you can print the entire worksheet, choose File Print Area Clear Print Area.
If you want to print more than one identical copy of your data, change the "Number of copies" text box accordingly . The Collate option determines whether Excel will duplicate each page separately. For example, if you print 10 pages and Collate is not turned on, Excel prints 10 copies of page 1, 10 copies of page 2, and so on. If Collate is turned on, Excel prints the entire 10-page document, and then prints out another copy, and so on. You'll still end up with 10 copies of each page, plus, for added convenience, they'll be grouped together.
Click OK to send the spreadsheet to the printer.
Excel prints your document using the settings you've selected.
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. The dialog box is divided into four tabs of options, which are described in the following sections.
The Page tab contains two of Excel's most useful print toolsOrientation and Scalingplus a handful of other, less frequently used options:
Orientation lets you control 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'll fit twice as many columns and rows on a page. (Keep in mind that the font size will be 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. This 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.
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 only has an effect when you've inserted pages numbers in headers or footers, as explained on Section 184.108.40.206.) For example if you were printing out a book and wanted the first page to be numbered 4, you would just enter that into the box. If you leave this setting at Auto (rather than entering a number), the first page will be 1.
The Margins tab (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 (e.g., 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.
Logically enough, if you reduce the size of your margins, you can accommodate more information. 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'll just stick with the smallest margin your current printer allows. This behavior is different than other Microsoft Office applications (like Word). To see this 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.
When you only have 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.
The Header/Footer tab lets you create a new header or footer using one of the settings Excel provides, or by creating your own. The header contains text that's printed at the top of every page in your printout, while the footer contains text that's printed 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 items already available in either the Header or Footer pull-down menu. When you make a selection, you'll 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 you can use 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 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 matches what you need, you can click the Custom Header or Custom Footer button to create your own. There are two types of information you can add 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.
You can insert these codes by clicking the appropriate button, as shown in Figure 6-13. This dialog box also has options that let you set the font or insert a graphic.
The Sheet tab 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 Sidebar 6.2). Some people find the Selection setting in the Print dialog box (Section 6.2.2) 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 this setting (see Figure 6-14) to print column titles on the top of every page.
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. Excel can either append them to the cells in the printout or add them at the end of the printout, depending on the option you select. For the lowdown on Comments, see Section 21.2.
Cell errors lets you configure how Excel should print a cell if it contains 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'll 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, and 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.
You don't have to be a tree-loving environmentalist to want to minimize the number of pages you print out. Enter the Print Preview tool (Figure 6-15), which lets you see what your printout looks like before it appears on paper. The tool is especially helpful if you've just tweaked a bunch of the Page Layout settings (described above) and you first want to see what the effects will be before pressing print. To see the print preview for a worksheet, select File Print Preview from the menu. You can also click the Preview button in the Print dialog box.
How does the Print Preview differ from your worksheet view? There are several differences, including:
The Print Preview paginates your data. That means that you'll see exactly what fits on each page and how many pages your printout will require.
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 shows the margins that Excel will use for your pages. In fact, you can even modify these margins right in the print preview window, as you'll see shortly.
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'll 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 (you'll notice that the pointer has become a magnifying glass), or click the Zoom button. This magnifies the sheet to 100 percent zoom, so you can more clearly see the text and details. The preview window only supports 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.
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 learn more about the Page Break Preview feature on Section 6.2.4.
If you want to adjust the page margins right in the preview window, click Margins. Dotted lines will 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.
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.
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 will 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.
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.
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'll 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.)