There’s a scenario that occurs across the country every day. You create a complex worksheet, and when you print it disaster strikes, and your office printer ejects half-printed pages at an alarming rate.
To save your sanity – and some trees – take a few minutes before you press the Print button to ensure that the worksheet is ready for printing, that it will be easy to collate, and that your audience will be able to read the resulting output easily.
 The Page tab allows you to configure a large worksheet so that it prints more efficiently. (Click for larger image). |
Print Preview
Before you print, preview the print job. Tens of thousands of sheets of paper could be saved by this simple technique. The print preview dialog shows the number of pages that will print in the bottom left of the screen. If this is extraordinarily high then troubleshoot the problem before pressing the print button. This could indicate a simple problem such as the worksheet printing two sheets wide instead of one or something more complex — a cell with something in it somewhere well out of the way of the data on the sheet, for example.
When you print a worksheet, Excel prints everything on it. If you have a rogue cell buried deep in the sheet even if it only has a space in it, Excel will print everything up to and including this cell, resulting in sheets of wasted paper. If you check the number of pages that will print, you will see you have a problem and will have time to fix it.
To locate and delete a cell that is causing excessive pages to print try deleting rows and columns between the bottom of your data and the end of the worksheet. Another option is to select the data area before selecting Print and then choose to Print the Selection from the Print dialog.
Page Setup Options
In the Print Preview dialog click the Page Setup icon to configure the way your worksheet prints. On the Page tab you can select Landscape or Portrait orientation — some worksheets can print on a single sheet of landscape paper where they may require two sheets in portrait orientation.
This tab also has a tool for shrinking or expanding the worksheet for printing. To do this, click Adjust to and then set the percentage to increase or decrease the worksheet size.
The Fit To option lets you preset how many pages wide and/or tall the worksheet will print. So, for example, if your worksheet is just a bit too wide to print one sheet wide, you can reduce by selecting Fit To and then type 1 in the Pages Wide box. Delete the figure in the Tall box and Excel will reduce the width of the printed area to one page wide but use as many pages as it needs to print the full length of the worksheet.
You can also specify the number of pages tall leaving the number of pages wide blank if you want to limit the vertical size of the printed copy but flexible as to its width. These options are often better choices than trying to specify exactly how many pages wide and tall you want the printed document to be.
 The Show Margins option in the print preview dialog helps you see where the margins are going to occur and gives you the option of adjusting them. (Click for larger image). |
Set Your Margins
Use the Margins tab to set the margins on the page and to center the printout horizontally and vertically between these margins. In addition to setting the margins by typing values into the boxes you can also set them in the Print Preview area. To do this, click the Show Margins button to see the margin markers on the preview.
Use the margin markers to adjust the margins to a smaller size and use the column markers to adjust the column widths without needing to return to the worksheet to do this. You may find that the margins tool in the Print Preview gives better visual feedback as to how the margins will affect the final printed copy than other options.
The Header/ Footer tab in the Page Setup dialog lets you configure headers and footers for the worksheet. You can specify, for example, a different odd and even page header or a different header or footer for the first page of the document.
From the Header dropdown list you can select one of a range of headers that are built in to Excel or click the Custom Header area and create your own. Before you create your own header select a built-in header that is close to the type of header you want to create then click Custom Header and customize the options in the dialog rather than building the entire header from scratch.
Use the Sheet tab in the Page Setup dialog to control the printing order for a large document — you can print Down, then Over or Over, then Down. Choosing the right option may make it easier to collate a large worksheet later on.
You can also select to print in black and white, print row and column headings (the letters and numbers) or turn gridlines on or off. Even if gridlines show on your worksheet they will not print unless you enable the checkbox on this tab.
 Use the options in the Sheet tab to configure row and column titles to repeat on every page. (Click for larger image). |
Special Printing Options
Use the Print Area option to configure a specific area of the worksheet to print — by default Excel prints everything on the current worksheet, but you can select a subset of the data if desired using this feature. The Rows to repeat at top and Columns to repeat at left options are a must-have for a worksheet that prints over a series of pages. These options ensure that the rows and/or columns that you select are printed on every sheet of the printed document.
This lets you pick up any sheet of paper from the printed worksheet and see the column and row headings appropriate to this data. Without this information, a single sheet from a printout may well be incomprehensible.
However, in Print Preview mode you cannot configure either the print area or columns and row titles. You must exit Print Preview and choose Page Layout > Print Titles and then select the area of the worksheet to print and/or the Rows and Columns to repeat. You can click the Print Preview button in the dialog to view the worksheet once you have made your choice but you must configure the print area and titles first.
 Selecting the chart before you click Print ensures that only the chart will print. (Click for larger image). |
Printing Charts
If you have a worksheet with a chart embedded in it, you can print just the chart by selecting the chart before clicking the Office button and choosing Print. If you do not select the chart before printing, the entire worksheet including the area covered by the chart will be printed.
Viewing Your Worksheet
Excel provides a number of views of your worksheet that help you see how it will print. To see these, click the View tab on the ribbon — by default you will be see the Normal view. The Page Layout view lets you see the document as it is intended to print — you can see more or less of the sheet using the zoom tool in the bottom right corner of the screen.
Use Page Break view to see where the page breaks will appear in your document — these are blue dashed lines on the page. Adjust the position of the page break by dragging it up or down or to the left or the right.
Drag a page break upward or to the left to create a page break at an earlier position than Excel was intending to do so. If you drag it down the page or to the right, Excel leaves the existing page break in place and adds another one at the point where you drag the marker. This is probably not what you want as it creates more pages than before.
 Use the views dialog to configure and save common worksheet printing configurations. (Click for larger image). |
The Custom Views option on the View tab lets you create and use custom views of your worksheet. A View can store the area of the worksheet to print and the specialized print settings from the Page Setup dialog. You can select all these options by simply selecting a view that stores these options.
For example, create a print area by selecting the area to print and choose Page Layout tab > Print Area (in the Page Setup group), and click Set Print Area. Click the View tab > Custom Views > Add to create a new view. To use the view, click the Custom Views option, select the view and click Show to apply it to the worksheet.
Using views you can configure different printing setups for your worksheets so you can, for example, create one view that prints the worksheet in landscape orientation and in color and another that prints portrait in black and white. Views can save you from having to repeatedly configure and check the printing options for a worksheet.
Multiple Sheets Per Page
If you’re like tens of thousands of other people who use Excel you frequently lament not being able to print multiple sheets on a single sheet of paper. While Excel has no option for doing this, fortunately there is a workaround. To use it you need the Camera tool, so in Excel 2007 click the Office button > Excel Options > Customize.
 Add the camera icon to your quick access toolbar and you can assemble data from multiple sheets to print on a single sheet of paper. (Click for larger image). |
From the Choose Commands From list select Commands Not In The Ribbon, locate and click the Camera icon and click Add to add the camera to the Quick Access Toolbar. (At the same time, if you miss the Print Preview button, locate and click Print Preview in the list and click Add to add it, too.) Click OK to close the dialog.
Select the first area to print and click the Camera icon to take a snapshot of the selected area. Move to a new sheet to assemble the page for printing and click to paste in the snapshot of the selected range. Repeat for other parts of the workbook that you want to print on a single sheet of paper. When you are done, print this sheet that has the assembled snapshots of your data.
The data in these snapshots is live, so if you make a change to the original data, the data in the snapshot version alters accordingly.
With a little pre-planning and some knowledge of the print options that can be configured to your advantage in Excel, you can turn your next Excel print job from an exercise in frustration to an effortlessly simple and successful procedure.
Adapted from winplanet.com.
| Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today! |
