Software

Five tips for printing Excel data more efficiently

Excel's printing features aren't complicated, but users often take a hit-or-miss approach and wind up with a mess. These simple tips demystify the print options and explain when to use which ones.

Many Excel users are skilled at manipulating data in their worksheets — but when the time comes to print certain portions of the data, the results aren't what they want. Fortunately, once you master a few of Excel's print options, you can avoid frustration and save a lot of time. Here are several easy ways to enable the appropriate settings to print just what you need, when you need it.

1: Set a custom print area

Excel prints the active sheet unless you tell it differently. There are two quick methods for controlling what Excel prints:

  • Set the Print Area to limit what Excel prints by default.
  • Print selected cells for a one-time print job.

A combination of these two methods provides the flexibility you need to quickly print exactly what you need.

First, use the Print Area option for a print job that seldom changes. After you set the Print Area, subsequent print jobs will default to that specified area instead of the entire sheet. To set the Print Area, do the following:

Excel 2003

  1. Select the range you want to print.
  2. Choose Print Area from the File menu.
  3. Choose Set Print Area.

Excel 2007 and 2010

  1. Select the range you want to print.
  2. Click the Page Layout tab.
  3. In the Page Setup group, click Print Area | Set Print Area.

When you need to print something other than the Print Area, print a selected range as follows:

Excel 2003

  1. Select the range you want to print.
  2. Choose Print from the File menu.
  3. In the Print What section, choose Selection.
  4. Click OK.

Excel 2007 and 2010

  1. Select the range you want to print.
  2. Click the Office button or the File tab.
  3. Choose Print in the left pane.
  4. Choose Print Selection from the first Settings drop-down (Print Active Sheet is the default).
  5. Click Print.

For the normal print job, just print, and Excel will default to the Print Area. When you need to quickly print another area, print a selected range.

2: Use page breaks to your advantage

Initially, printing can be a bit tenuous; just what will you get? You print, reset, try again... that could go on for a while. Fortunately, there's help in the form of page breaks. To remove some of the guesswork, choose Page Break Preview from the View menu in Excel 2003. In Excel 2007 and 2010, choose Page Break Preview from the Workbook Views group on the Layout tab. Excel will prompt you with a quick reminder that you can adjust page breaks by clicking and dragging them. Click OK to clear the prompt and move on to Page Break Preview. This view is noticeably different from the other views but shares a lot of information, visually:

  • The visible white area is the Print Area. You can adjust this area by moving the solid blue boundary lines.
  • The dashed lines identify page breaks, which you can move.
  • The dark gray watermark denotes the page number.

No more guesswork: Simply put the page breaks exactly where you want them. Sometimes, rather than moving an existing page break, you might want to insert a manual break, as follows:

  1. Select the row or column where you want to insert the break.
  2. Choose Page Break from the Insert menu. In Excel 2007 and 2010, click the Page Layout tab. Then, in the Page Setup group, click Breaks | Insert Page Break.

The break will appear above the selected row or to the left of the selected column.

3: Fit To Page

This feature is nice when you want all the data on a single sheet. You might not be able to read it easily, but that might not be your main concern. To print the active sheet on a single page, do the following:

Excel 2003

  1. Choose Page Setup from the File menu.
  2. On the Page tab, click Fit To in the Scaling section.
  3. Make sure both Wide and Tall settings are 1.
  4. Click OK.

Excel 2007 and 2010

  1. Click the Page Layout tab.
  2. Click the Scale To Fit group's launcher.
  3. On the Page tab, click Fit To in the Scaling section.
  4. Make sure both Wide and Tall settings are 1.
  5. Click OK.

This option won't satisfy your everyday print chores, but when you need everything on one sheet, you can't beat it.

4: Use custom views in Excel 2007 and 2010

Custom views are a new feature that let you save specific display and print settings for a sheet. It's so flexible that a sheet can have multiple custom views and a Print Area — so think of the possibilities! Once you've set up a custom view, just apply it and press [Ctrl] P to print it. How easy is that? You can create a custom view as follows:

  1. Set the appropriate display and print settings you want to save to a custom view.
  2. On the View tab, click the Workbook Views group and the click Custom Views | Add.
  3. Enter a name for the custom view.
  4. Specify which settings to include.
  5. Click OK.

Before you print the custom view, you must apply it by clicking the View tab and then clicking Workbook Views | Custom Views. Choose the view you want to apply and click Show. To print the custom view, press [Ctrl] P.

Creating a custom view takes a bit of work, but once it's saved, printing it is just a few clicks away.

5: Copy Page Setup options to other sheets

Setting Page Setup options isn't a huge job, but it can be time consuming if you have to set them for every sheet in your workbook. Fortunately, you don't have to — and you won't believe how simple the alternative is. If all the sheets in your workbook have the same settings, set the options for one sheet and then copy those settings to others, as follows:

Excel 2003

  1. Select the sheet whose Page Setup settings you want to copy.
  2. Right-click any sheet tab and choose Select All Sheets.
  3. From the File menu, choose Page Setup.
  4. Click OK.
  5. Right-click a sheet tab and choose Ungroup Sheets.

Excel 2007 and 2010

  1. Select the sheet whose Page Setup settings you want to copy.
  2. Right-click any sheet tab and choose Select All Sheets.
  3. Click the Page Layout tab.
  4. Click the Page Setup group launcher.
  5. Click OK.
  6. Right-click any sheet tab and choose Ungroup Sheets.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks