Section 6.2. Printing

6.2. Printing

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:

Note: You can change most of the settings listed; this 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 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.

6.2.1. How to Print an Excel File

Printing a worksheet is just like printing in any other Windows application. Follow these steps:

  1. Select File Print.

  2. 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.

    1. 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.

    2. 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.

    3. 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.

      Note: In order to use the "Print range" box effectively, you need to know how many pages will be required to print your worksheet and what data will appear on each page. Excel's print preview feature, (Section 6.2.3), is just the ticket.
      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.

    4. 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.

    5. 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 it's left your computer.

    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. The dialog box is divided into four tabs of options, which are described in the following sections.

    Figure 6-11. The most important setting in the Page tab is the Orientation setting, which lets you turn a page sideways to accommodate spreadsheets with lots of columns. It this still doesn't help you fit your data into place, try reducing the scaling percentage. Page settings

    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.

    Note: Page Break Preview mode, described below, 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 only has an effect when you've inserted pages numbers in headers or footers, as explained on Section 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. Margins settings

    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.

    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 Control Panel). Unfortunately, Excel doesn't indicate the type of units in the Page Setup dialog box, and it doesn't give you any choice to override your regional settings and use different units.

    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.

    Figure 6-12. Excel allocates space at the top and bottom of your printout for a header or footer. In this example, the header margin is set to 0.5, which means that any header information will appear half an inch below the top of the page. The top margin is set to 1, meaning the worksheet data will appear one inch below the top of the page. When adjusting either of these settings, be careful to make sure that the top margin is always larger than the header margin; otherwise your worksheet's data will print 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 their margins are set at.

    Tip: A good rule of thumb is to adjust margins symmetrically (printouts tend to look nicest that way). Thus, 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 minimum margin that most printers will allow.

    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. Creating headers and footers

    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. Sheet settings

    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.

    Figure 6-13. Top : You can set a header and footer using one of Excel's standard settings, or by creating your own.
    Bottom : When you create your own header or footer, a special dialog box appears with 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, and so on. Instead, you can click the appropriate button in the Header or Footer window to insert the code.

    • 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.

    Figure 6-14. The Sheet tab lets you set a slew of options. The first three settings"Print area" and the two "Print titles" settingsask you to enter 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 7.1.5 for more on manually indicating cell ranges.) 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.

    • 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.

    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. 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.

    Figure 6-15. This print preview shows the first of this worksheet's 96 printed pages. This worksheet has 21 columns, but since they're wider than the width of a single printed page, the worksheet will first print the leftmost four columns, as shown here. Additional rows and columns will turn up on other pages. To zoom in for a closer look, click anywhere on the page.

    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.

    Tip: If you only want to see how a select group of cells will print out, first set a Print Area (Sidebar 6.2). Now Print Preview will only display 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 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.

    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 will occur. If you're having trouble spotting these dashed lines, you can also use the Page Break Preview window, which is described in the next section.

    Figure 6-16. The Print Preview window lets you set margins using your mouse. 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 will overlap your worksheet's data.

    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 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.

    Figure 6-17. This example shows 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 dotted line clearly indicates that the page breaks after column D and before row 53. (Excel never breaks a printout in the middle of a column or row.)

    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 might end up with empty, unused space on some of the pages.

    Note: 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.

Excel. The Missing Manual
Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: