Section 7.2. Printing

7.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, 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 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 0.75 inches at the top and bottom of the page, and 0.7 inches on the left and right sides of the page. Ordinarily, Excel doesn't include headers and footers (so you don't see any page numbers ).

  • Excel doesn't include hidden rows and columns in the printout.

7.2.1. How to Print an Excel File

Printing a worksheet is similar to printing in any other Windows application. Follow these steps:

  1. Choose Office button Print .

    The Print dialog box appears, as shown in Figure 7-10.

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

  2. 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, then 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 and paper handling (like double-sided printing for those lucky enough to have a printer that supports it).

  3. 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 in Section 4.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.

    If you've set a print area on your worksheet (see the box "Printing Parts of a Spreadsheet" in Section 7.2.2), you can choose "Ignore print areas" to print the full worksheet, not just the print area.

  4. 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. 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 you need to print your worksheet and what data will appear on each page. Excel's Page Layout view, (Section 7.2.2), is just the ticket.
  5. Use the "Number of copies" box to print multiple copies of your data .

    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 duplicates each page separately. For example, if you print 10 pages and Collate isn't 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.

  6. Click OK to send the spreadsheet to the printer .

    Excel prints your document using the settings you've selected.

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 to open a print window. Then, select the offending print job in the list, and then press Delete (or choose Document Cancel from the print windows menu). Some printers also provide their own cancel button that lets you stop a print job even after it's left your computer.

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, in the Print dialog box's "Print what" box, choose Selection. But if you frequently need to print the same area, you're better off defining and using 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 then choose Page Layout Page Setup Print Area Set Print Area. The portion of the worksheet that youve highlighted now has a thin dashed outline, indicating that this is the only region Excel will print. You can only have 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 Page Layout Page Setup Print Area Clear Print Area.

7.2.2. Quick Printing

If you know that the currently selected printer is the one you want to use, and you don't want to change any other print settings, you can skip the Print dialog box altogether using the popular (but slightly dangerous) Quick Print feature. Just choose Office button Print Quick Print to create an instant printout, with no questions asked.

The Quick Print feature's so commonly used that many Excel experts add it to the Quick Access toolbar so it's always on hand. If you want to do this, hover over the Office button Print Quick Print command, right-click it, and then choose Add To Quick Access Toolbar. (The Appendix has more about customizing the Quick Access toolbar.)

7.2.3. Previewing Your Printout

When you're preparing to print that 142-page company budget monstrosity, there's no reason to go in blind. Instead, prudent Excel fans use Page Layout view to check out what their printouts look like before they appear on paper. The tool is especially helpful if you've run rampant with formatting, or you want to tweak a variety of page layout settings (Section 7.2.5), and you want to see what the effects will be before clicking Print.

To see the Page Layout view for a worksheet, choose View Workbook Views Page Layout View. Or, for an even quicker alternative, use the tiny Page Layout View button in the Status bar, which appears immediately to the left of the zoom slider. Either way, you see a nicely formatted preview (Figure 7-11).

Figure 7-11. The Page Layout view shows the first (and part of the second) page of this worksheet's 76 printed pages. This worksheet has 19 columns, but since they're wider than the width of a single printed page, the first page includes only the leftmost seven columns, as shown here. You can scroll to the right to see the additional columns that'll turn up on other pages, or scroll down to see more rows.

How does Page Layout view differ from Normal view? For starters, Page Layout view:

  • Paginates your data. You see exactly what fits on each page, and how many pages your printout requires.

  • Reveals any headers and footers you've set as part of the page setup. These details don't appear in the Normal worksheet view.

  • Shows the margins that Excel will use for your pages.

  • Doesn't show anything that Excel won't print (like the letters at the top of each column). The only exception is the cell gridlines, which are shown to help you move around your worksheet.

  • Includes a bit of text in the Status bar that tells you where you are, page-wise, in a large spreadsheet. For example, you might see the text "Page: 5 of 26."

Note: Don't confuse Page Layout view with an ordinary print preview. A print preview provides a fixed "snapshot" of your printout. You can look, but you can't touch. Page Layout view is vastly better because it shows what your printout will look like and it lets you edit data, change margins, set headers and footers, create charts , draw picturesyou get the idea. In fact, you can do everything you do in Normal view mode in Page Layout view. The only difference is you can't squeeze quite as much data into the view at once.

If you aren't particularly concerned with your margin settings, you can hide your margins in Page Layout view so you can fit more information into the Excel window. Figure 7-12 shows you how.

Figure 7-12. Move your mouse between the pages and your mouse pointer changes into this strange two-arrow beast . You can then click to hide the margins in between pages (as shown here), and click again to show them (as shown in Figure 7-11). Either way, you see an exact replica of your printout. The only difference is whether you see the empty margin space.

Here are some of the tasks you may want to perform in Page Layout view:

  • If the print preview meets with your approval, choose Office button Print to send the document to the printer.

  • To tweak print settings and see the effect, choose the Page Layout tab in the ribbon and start experimenting. You'll learn more about these settings in Section 7.2.5.

  • To move from page to page, you can use the scroll bar at the side of the window, or you can use the keyboard (like Page Up, Page Down, and the arrow keys). When you reach the edge of your data, you see shaded pages with the text "Click to add data" superimposed. If you want to add information further down the worksheet, just click one of these pages and start typing.

  • To adjust the page margins, first make sure the ruler is visible by turning on the View Show/Hide Ruler checkbox. Then, drag one of the margin lines on the ruler, as shown in Figure 7-13. If you want to set page margins by typing in the exact margin width, use the Page Layout tab of the ribbon instead (Section

    Figure 7-13. The Page Layout view lets you set margins by dragging the margin edge with your mouse. Here, the left margin (circled) is about to be narrowed down to 0.58 inches. If you're also using a header or footer (below), make sure you don't drag the page margin above the header or below the footer. If you do, then your header or footer will overlap your worksheet's data.

  • When you're ready to return to the Normal worksheet view, choose View Workbook Views Normal (or just click the Status bars tiny Normal View button).

7.2.4. Creating Headers and Footers

A header is a bit of text that's printed at the top of every page in your printout. A footer is a bit of text that's printed at the bottom of every page. You can use one, both, or neither in a printout.

Ordinarily, every new workbook starts out without a header or footer. However, Page Layout view gives you an easy way to add either one (or both). Just scroll up to the top of any page to create a header (or the bottom to create a footer), and then look for the box with the text "Click to add header" or "Click to add footer". Click inside this box, and you can type the header or footer text you want.

Note: You won't see the header or footer boxes if you've drastically compressed your margins. That's because the header and footer don't fit. To get them back, resize the margins so that they're larger. When you're finished adding the header or footer, you can try adjusting the margins again to see just how small you can get them.

Of course, a good header or footer isn't just an ordinary piece of text. Instead, it contains information that changes dynamically, like the file name, current page, or the date you printed it. You can get these pieces of information using specialized header and footer codes , which are distinguished by their use of square brackets. For example, if you enter the code [Page] into a footer, Excel replaces it with the current page number. If you use the code [Date] , Excel substitutes the current date (when you fire off your printout). Of course, no one wants to memorize a long list of cryptic header and footer codes. To help you get these important details right, Excel adds a new tab to the ribbon named Header & Footer Tools Design (Figure 7-14) when you edit a header or footer.

Figure 7-14. The Header & Footer Tools Design tab is chock-full of useful ingredients you can add to a header or footer. Click a button in the Header & Footer Elements section to insert a special Excel code that represents a dynamic value, like the current page.

The quickest way to get a header or footer is to go to the Header & Footer Tools Design Header & Footer section (shown in Figure 7-14), and then choose one of the Header or Footer lists ready-made options. 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.xlsx or C:\MyDocuments\myfile.xlsx )

  • The person who created the document, and the date it was created

  • A combination of the above information

Oddly enough, 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 edit the automatic header or footer, or you can create your own from scratch. Start typing in the header or footer box, and use the buttons in the Header & Footer Elements section to paste in the code you need for a dynamic value. And if you want to get more creative, switch to the Home tab of the ribbon, and then use the formatting buttons to change the font, size , alignment, and color of your header or footer.

Finally, Excel gives you a few high- powered options in the Header & Footer Tools Design Options section. These include:

  • Different First Page . This option lets you create one header and footer for the first page, and use a different pair for all subsequent pages. Once you've checked this option, fill in the first page header and footer on the first page, and then head to the second page to create a new header and footer that Excel can use for all subsequent pages.

  • Different Odd & Even pages . This option lets you create two different headers (and footers)one for all even-numbered pages and one for all odd-numbered pages. (If you're printing a bunch of double-sided pages, you can use this option to make sure the page number appears in the correct corner.) Use the first page to fill in the odd-numbered header and footer, and then use the second page to fill in the even-numbered header and footer.

  • Scale with Document . If you select this option, then when you change the print scale to fit in more or less information on your printout (Section 7.3.2), Excel adjusts the headers and footers proportionately.

  • Align with Page Margins . If you select this option, Excel moves the header and footer so that they're centered in relation to the margins. If you don't select this option, Excel centers them in relation to the whole page. The only time you'll notice a difference is if your left and right margins are significantly different sizes.

All these settings affect both headers and footers.

7.2.5. Customizing Print Settings

Excel's standard print settings (Section 7.2) 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 you can easily read what you print. The Page Layout tab of the ribbon is your control center (Figure 7-15). 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.

Figure 7-15. The Page Layout tab's most important printrelated sections are Page Setup (which lets you change orientation and margin settings), Scale to Fit (which lets you cram more information into your printed pages), and Sheet Options (which lets you control whether gridlines and column headers appear on the printout). To get even more settings, you can click the dialog box launcher (circled), which pops up a full-fledged Page Setup dialog box. Margins

The Page Layout Page Setup Margins list (Figure 7-16) lets you adjust the size of your printed pages margins (the space between your worksheet data and the edge of the page). All you need to do is pick one of the preset options. The margin numbers indicate the distance between the item indicated (for example, the top of the page, or the footer on the bottom) and the edge of the paper.

Figure 7-16. You can choose a helpful margin preset (Normal, Wide, or Narrow), or choose Custom Margins to fine-tune your margins precisely, as shown in Figure 7-17.

Figure 7-17. 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's 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 the top margin's 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, their margin settings don't matter.

Note: The units Excel uses for margins depend on the regional settings on your computer (which you can adjust through the Control Panel's Regional and Language Options icon). 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, when 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 from that of other Microsoft Office applications (like Word). To see this in action, try setting your margins to 0, and then look 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 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, then 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 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. Paper size and orientation

Orientation is the all-time most useful print setting. This setting 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 your worksheet, it makes good sense to switch to landscape orientation. That way, Excel prints your columns across a page's long edge, which accommodates more columns (but fewer rows per page).

If you're fed up with trying to fit all your data on an ordinary sheet no matter which way you turn it, you may be tempted to try using a longer sheet of paper. You can then tell Excel what paper you've decided to use by choosing it from the Paper Size menu. (Of course, the paper needs to fit into your printer.) Letter is the standard 8.5 x 11-inch sheet size, while Legal is another common choiceit's just as wide but comes in a bit longer at 8.5 x 14 inches.

Note: When using different types of paper, remember to place the paper in your printer before you start the print job. Sheet settings

Margins and orientation are the most commonly adjusted print settings. However, Excel has a small family of additional settings hidden on the Page Setup dialog box's Sheet tab. To see these, go to the Page Layout Page Setup section of the ribbon, and click the dialog box launcher (the tiny square-with-an-arrow icon in the bottom-right corner). The Page Setup dialog box appears, as in Figure 7-18.

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, it's easier to use the Print Area tool (described in the box in Section 7.2.2). Some people find the Print dialog box's Selection setting (Section 7.2.1) 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 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 7-18. The Page, Margins, and Header/Footer tabs provide options that are easier to configure than using the Page Layout ribbon tab. However, the Sheet tab includes a few options that you can't find anywhere else. 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 23.2.1.

  • 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, two dashes (--), or the error code #N/A (meaning not available). You'll learn much more about formulas in Chapter 8.

  • Page order sets the way Excel handles a large worksheet that's too wide and too long for the printed page's boundaries. When 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. When 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.

Excel 2007[c] The Missing Manual
Excel 2007[c] The Missing Manual
ISBN: 596527594
Year: 2007
Pages: 173 © 2008-2017.
If you may any questions please contact us: