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.

4 comments
ecoprinting
ecoprinting

Great information here but I would also suggest the use of remanufactured toner cartridges since these printer supplies can offer a lot of savings.

stapleb
stapleb

There are some good tips here, although I did know most of them. The fit to option is great although I did have one user change the 1 wide by 16 tall to one of each, not a good look. Another important aspect is Print Titles which many users either forget or don't know about. One of the best I saw was someone who selected about 20 noncontiguous empty cells and set them as print areas - caused great confusion to the email recipient of the file. Worth noting that the Name Box will show whether or not someone has set a Print Area.

michael_boardman
michael_boardman

If you want to make it so that your print-out is ALWAYS one page wide but as many pages LONG as it takes on any given printing session, just delete any number in the "by ... tall" box: ie, it should be completely blank. (Probably works the opposite way for one page tall by any pages wide, but can't think if I would ever use it!) This is how it works up to Excel 2003, not sure about later versions.

ManiakMatt
ManiakMatt

@michael_boardman  A little late considering the date you posted but here goes anyway.


I use Custom Views on one spreadsheet with all our technical data listed, now I can view and print different lists of data depending on the filters set in the custom view.


The problem I was having was getting some blank pages where data was filtered out.  I fixed it by setting the print view to be 1 page wide and 20 pages tall (the number of pages tall was chosen to be more than the data contained; may need adjustment in the future but I set it pretty tall).


Great article Susan

Editor's Picks