Excel: Print Right

| February 22, 2012 | 1 Comment


If you’re like me, you have a litany of Excel printing horror stories to tell. It seems that when things go wrong when printing an Excel worksheet they never go wrong by halves – it is usually a spectacular disaster.

Hit the big red switch

Well your printer probably doesn’t have a big red switch but it will have an OFF button. When the printer starts coughing up pages of unwanted garbage – hit the off switch, cancel the print job and then start hunting for the problem.

To help you, 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 may not realize are filled and the result is you will print pages you don’t mean to print.

Use Print Preview

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. In Excel 2010 things are easier than ever before and the print preview appears whenever you choose File > Print.

print preview

Selecting what to print

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 click the Print icon if you have one. 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.

choosing what to 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 and 2010). When you do this, you’ll see dashed lines on the screen showing the page breaks.

preview where page breaks will occur

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 and  2010, 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.

changing page breaks

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 Page Layout > Print Titles to open the Page Setup dialog at the Sheet tab. Click in the Rows To Repeat At Top box and 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.

printing column 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 > Print and choose an option from the Scaling list which probably currently shows as No Scaling.

Choose Fit Sheet on One Page to scale the sheet down so it all prints on one sheet of paper. Other options include Fit all Columns on One Page and Fit All Rows on One Page which scale the worksheet to fit either one page wide or one page lengthwise. Choose Custom Scaling Options if none of these settings is exactly what you need.

shrink to fit

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 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 the QAT (Quick Access Toolbar), choose Options > Quick Access Toolbar and from the dropdown list choose Commands Not in the Ribbon, locate the Camera and click Add to add it to the QAT.

camera tool

Now select the first area to print, and take a picture of it by clicking the Camera icon. Move to a new worksheet and click where the snapshot image should appear. Immediately you click on the spreadsheet a snapshot of what you captured will be inserted.

selecting camera area

Now go back and take a snapshot of the other worksheet and add it to your new sheet. You can do this as many times as you need to. Then print the worksheet and all the ‘snapshots’ will print on the one page. 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 > Print to locate all the options you have for sizing and printing your chart. When you click the Print button, only the chart will print.

print the chart

Tags: , , , , , , , , , , , , , , , , , ,

Category: Excel

About the Author ()

Helen Bradley is a lifestyle journalist specializing in Photoshop, Lightroom, photo editing, web design, Visual Basic and Office software. She writes how to articles, tips and projects and produces how to videos for computer magazines, newspapers and online services in the USA, Australia, Canada and the UK. She writes for PC World, SmallBusinessComputing.com, Practical Photoshop and Digital-Photography-School.com.

Comments (1)

Trackback URL | Comments RSS Feed

  1. Deb says:

    Great tips. I teach Excel and I stress to my classes how important it is to check how many pages will print.

Leave a Reply