Why Printing Needn't Be A Grey Area In Excel

Tue 24th May 2011

With the ability to handle data across 65,536 rows and 256 columns, and with the possibility of over 16 million cell references in a single worksheet, it's little wonder that Microsoft Excel 2003 is regarded as one of the most powerful spreadsheet programs available. For some, however, understating the print function is still a bit of a grey area. Here we find out how to print to perfection.

One function in Microsoft Excel 2003 that is frequently referred to as a grey area is, in fact, blue. However, by using the Page Break Preview, you are able to view these blue lines which can help unravel the mysteries of why your worksheet isn't printing correctly, or has unexplained breaks in certain parts of a sheet.

The blue lines indicate where the pages will break in your document. Solid blue lines are manually set page breaks, and the dotted lines are default page breaks set by print settings; for example, print range, paper size, and margins. You can move these breaks by dragging to the area you want your worksheet to print to, and Excel will automatically resize your workbook to fit into the printed area. It's as simple as that.

So, once you are happy with how your data looks on screen, then to print the exact amount of pages you want, use the Page Break Preview view to adjust page breaks. Remember, in this view, manually inserted page breaks appear as solid lines, and dashed lines indicate where Microsoft Excel will break pages automatically.

To move a page break, drag the page break to a new location (remember, moving an automatic page break changes it to a manual page break). Or if you want to insert a vertical or horizontal page break, select a row or column below or to the right of where you want to insert the page break, right-click, and then click Insert Page Break. If you want to remove a page completely, then drag the page break outside of the page break preview area. And if you need to remove all manual page breaks, right-click any cell on the worksheet, and then click Reset All Page Breaks.

If you would like to see how the margins will affect your printed worksheet, click Print Preview before printing your document. Then, to adjust the margins in print preview, click Margins, and drag the black margin handles on either side and at the top of the page. By setting the margins, you can specify how worksheets appear on a printed page. You can also quickly centre worksheets horizontally or vertically on a printed page. And if you need to repeat a row or even a column description on each page, then Excel can take care of that, too.

By including these rows and/or columns, it saves having to refer back to a previous page in order to see what the data in a certain column relates to.

To repeat rows at the top of each page, click on the miniature spreadsheet button next to Rows to repeat at top. A floating toolbar, Page Setup - Rows to repeat at top will appear. Now, select the row(s) that you want repeated and their numbers will display in the dialog box. Click the button at the end of the toolbar once you have made your selection. The row(s) will then appear in the Page Setup dialog box. Likewise, to repeat columns, click on the miniature spreadsheet button next to Columns to repeat at the left. The instructions for selecting columns are similar, and you can choose to repeat just rows, just columns, or both.

If you need only to print a specific portion of a worksheet, you can define it as a print area. Then, every time you print the worksheet, Excel prints only the data specified in the print area. To set a print area, select the data you want to include in your printout, point to Print Area on the File menu, and then click Set Print Area. However, you can only set one print area at a time. To clear a print area, point to Print Area on the File menu, and then click Clear Print Area.

If there is information on your worksheet, and you do not need to see or if you want to print only certain columns, you can hide this data. To do this, select a cell within the column(s) to be hidden, then from the Format menu, select Column then Hide, and the column is hidden. You can also hide rows and Excel even offers the facility to hide the contents of individual cells if you do not need to view their contents or you simply do not want to print certain cells.

It's even easy to add page numbers to the top or bottom of your printed spreadsheet. You can do this by going to View and selecting header and Footer. The Page Setup multi-tabbed dialog box will appear, with the Header/Footer tab selected. Click the pull-down option underneath Header if you want the page number to appear at the top of your spreadsheet, or Footer if you would like it to appear at the bottom. Now select Page 1 or Page 1 of ? in the pull-down to determine how you want the page number displayed on the printed copy. You can even insert a graphic into a header or footer.

There's the flexibility to position your worksheet on the printed page, including centring. Click Page Setup on the File menu. Now, on the Margins tab, under Center on page, select the Horizontally or Vertically check box. And to provide extra space around the worksheet, or to allow room for a large header or footer, you can move the margins to the widths you prefer.

For perfect printing results every time you use Excel, one of the most important things to remember is to look at a preview of your spreadsheet by selecting the Print Preview command before you print; this helps you to make final adjustments. This also allows you to see items that aren't visible in the on-screen worksheet, such as margins, headers and footers, and print titles. The buttons at the top of the Print Preview window give you quick access to the layout and printing options for your worksheet. For example, you can turn gridlines on or off, add page numbers, or change the order in which pages are printed. After you make a change, make sure to preview the worksheet again.

Now, hopefully, next time you need to print an Excel worksheet everything will black and white - unless, of course, you want to print in colour, or even intentionally print in shades of grey.

Author is a freelance copywriter. For more information on excel courses, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1696-printing-need-not-be-grey-area-excel.html