Excel: print right

Be the First to Comment on this article...

Jump to comments

Share this

If you're like me, you have a litany of Excel printing horror stories. Printing a large worksheet can consume a lot of paper, and when things go wrong, they often do so in a spectacular way.

Often the best thing to do when the printer starts coughing up pages of unwanted garbage is to hit the off switch, cancel the print job and start hunting for the culprit. To help you out, I've put together my best tips and troubleshooting techniques for printing right in Excel - every time.

Understand what prints When you click the Print button to print an Excel worksheet, Excel prints everything that's on that worksheet. Sometimes there are cells filled that you don't know about and the result is wasted pages. Your first and best tool is the Print Preview option in Excel - it shows you how many pages will print and if you're expecting one page and it's preparing for 50 you know something is very wrong.

One common problem in Excel is having a filled cell well out of view. You might have mistakenly moved to a faraway cell and typed something - even something as innocuous as a space is all it takes and that worksheet cell contains data. So, when you click the Print button, Excel will print everything up to and including the cell containing that space. To you it looks like reams of empty paper - to Excel it all makes perfect sense.

When this happens to you there are two choices - find and delete the problem cell or use a workaround. The workaround involves selecting the area to print before printing it. To do this, select the area to print and choose File > Print - don't use the Print icon (and in Excel 2007 choose Office button > Print > Print Preview). When the Print dialog appears, click the Selection option and only the selected area will print.

Inconvenient page breaks

Sometimes, when Excel divides up a large worksheet over a series of pages it doesn't put the page break in the most sensible place. You can preview page breaks before you print and make adjustments to them by selecting the View > Page Break Preview (View tab > Page Break Preview in Excel 2007). When you do this, you'll see dashed lines on the screen showing the page breaks.

To change a page break you can add your own but this must be done inside a current page so that you're effectively making the page smaller.

You can't make a page longer or wider using this technique. To add a manual page break, click and select the column or row which should trigger the page break and choose Insert > Page Break (in Excel 2007 choose Page Layout tab > Page Breaks > Insert Page Break). You can also drag on the dashed lines to move them around the screen and reorganize your page layout.

Missing column headings

When a worksheet prints over multiple pages - all pages from page 2 onwards will be missing row or column headings or both. The data on the pages will be hard to understand unless you tape the pages together.

A better solution is to print column and row headings on every page and, to do this, choose File > Page Setup > Sheet tab and click in the Rows To Repeat At Top box. Type the row letters in the format $1:$1 to print the first row or $1:$2 to print the first two rows on each page.

In the Columns To Repeat At Left box, type $A:$A to print the first column as row headings or $A:$B to print the first two columns. The same commands are available in Excel 2007 by selecting Page Layout tab > Print Titles.

Shrink to fit

When a worksheet is only a little too big to print on a single sheet of paper, you can shrink it to fit.

Choose File > Page Setup > Page tab and click to select the Fit to 1 page(s) wide by 1 tall option (in Excel 2007 click Page Layout tab > Page Setup menu expander). This tool also can help structure multipage printing, type the number of pages wide or tall that you want to limit printing to and leave the other measurement blank. Excel then calculates the second measurement for you and prints the worksheet scaled to the right size.

Print multiple sheets on a page

If you've tried to print part of one sheet and part of another on a single sheet of paper you know this is a futile exercise - Excel can't do it because every sheet prints on a new page even if it contains only a few cells of data.

There is a workaround that involves using the Camera tool to take a snapshot of the worksheet areas you want to print and place these on a single worksheet for printing.

To add the Camera tool to a toolbar, right click a toolbar and choose Customize > Commands tab. From the Categories list click Tools and locate and drag the Camera onto a toolbar. In Excel 2007 choose Office button > Excel Options > Customize and from the dropdown list choose Commands not in the Ribbon, locate the camera and click Add to add it to the Quick Access Toolbar.

Now select the area to take a picture of and click the Camera icon. Move to a new worksheet and click where the snapshot image should appear. Immediately you click the spreadsheet's snapshot will be inserted. Take a snapshot of the other worksheet and add it to your new sheet before printing. Snapshots are live views so if the data in the original sheet changes then the snapshot will change too.

Print a chart To print just a chart and not all the worksheet detail around it, click the chart to select it. Now choose File > Page Setup > Chart tab to locate all the options you have for sizing and printing your chart. When you click the Print button, only the chart will print