AccountingWeb
News
ExcelZone Compendium: Printing tips (Excel 2007 update)
Practical issue: Gain better control over the spreadsheet elements you print outSolutions: Deciding what to print
Print areas
Print titles
Headers and footers
Fitting the content on to the page
Print options
Print Preview
Further reading
By default, Excel will print the entire contents of a worksheet, but there are a number of Page Setup and Print options to select and fit your print outs onto a specified number of pages. Print Preview is a helpful tool that can also reduce paper and toner wastage, writes Simon Hurst.
Practical issue: Gain better control over the spreadsheet elements you print out
Much of the look of any printed Excel output will depend on the formatting of your spreadsheet. However, Excel's Page Setup and Printing tools can be used to control the areas of each sheet that are to be printed, add headers and footers and also Print Titles that use the existing sheet title rows and columns. In addition, there is a very useful Scaling option that allows you to shrink your print area to fit a designated number of pages wide and tall. Print Preview helps reduce paper (and ink/toner) wastage by letting you see what your output will look like before sending it to the printer.
Solutions - Deciding what to print
If you have not set a specific Print Area for a worksheet, Excel will print the entire contents of the sheet. Use View, Page Break Preview (Excel 2007: View ribbon tab, Workbook Views section, Page Break Preview) to display the area of the sheet that will be printed together with an indication of what will print page by page. Here we can see a worksheet with no print area in page break preview, showing what will print on each page. The blue lines can be dragged to control what will be printed on each page. In this case we have dragged the page break upwards:
To set a print area, select one or more blocks of cells and then use File-Print Area-Set Print Area (Excel 2007: Page Layout ribbon tab, Page Setup section, Print Area dropdown, Set Print Area) . Here we have selected three different blocks of cells by holding down the control key while selecting the second and third block. We have then set the print area. An existing print area can be extended or reduced by dragging the page break line as described above.
Excel 2007 includes an additional option in its Print Area dropdown - Add Print Area. If you have already set a print area and want to add another, separate area to it, then select the additional area or areas and you should find a Print Area dropdown option to 'Add to Print Area'.
Versions of Excel prior to 2007 don't appear to include an Add to Print Area option (or does someone know better?). If you do want to add a separate area to an existing print area, then use Edit-Go to-Print_Area to select the current area or areas, then select the additional, separate area by dragging over it while holding down the control key. This will create a multiple selection including your original and new print areas. You can then use Set Print Area to set this combined set of areas as the print area:
To return from Page Break Preview to your normal view, choose View-Normal (Excel 2007: View ribbon tab, Workbook Views section, Normal) .
When the contents of a sheet will be printed over several pages, you may want to include the topmost row or rows and the leftmost column or columns as titles on each printed page. To do this, go to File-Page Setup and choose the Sheet tab (Excel 2007: Page Layout ribbon tab, Page Setup section, Print Titles) . In the Print titles area you can click in the "Rows to repeat at top:" and/or "Columns to repeat at left:" text boxes and then click on a row or column, or drag across several rows or columns, to use them as titles on each printing page.
Here we have set row 1 and columns A and B as the print titles for printing our sheet:
Note that the Print titles section of the Sheet screen is only available when it is accessed directly from File-Page Setup (Excel 2007: Page Layout ribbon tab, Page Setup section, Print Titles) - not when you get there from the Print Preview, Setup button.
Like Print Titles, Headers and Footers are repeated on each page of the printed output, and are set for each sheet. From the File, Page Setup screen shown above, choose the Header/Footer tab. (Excel 2007 either use the Page Layout ribbon tab, Page Setup section, Print Titles option and then switch to Header/Footer or, from the same section, click on the Page Layout dialog button at the bottom right) .
You can choose from some built-in Headers and Footers by choosing from the Header and Footer dropdowns or you can click on the Custom Header and Custom Footer buttons to type in your own text in one of the three sections: Left, Center or Right. You can also use the buttons provided to enter page numbers, date and time information, file path and name details, the sheet name and, for Excel XP and later, a picture which you can also format using the "Format Picture" button. You can insert no more than one picture in each section. Often, the easiest way to create your custom header or footer will be to choose a similar built-in version and then use the custom button to edit it.
Fitting the content on to the page
The Page section of the Page Setup screen includes the Scaling settings. You can either set the print Adjust to: option from 10% to 400% of the normal size, or you can use the Fit to: dialogue to condense the contents to fit on the designated number of pages wide by tall. Here we have chosen to fit the contents to 2 pages wide by 10 tall which has resulted in a reduction to (a rather difficult to read!) 23% of normal size.
Having sorted out what you want to print on each sheet, and at what size, when you choose File-Print (Excel 2007: Office Button, Print) you can then choose whether to print the active sheet or sheets, the entire workbook, or just any currently selected area or areas. If you select more than one sheet and choose "Active sheets" or "Entire workbook" then any page numbering included in the headers or footers will be continuous across all the printed pages.
It is almost always worth checking your potential printed output by previewing it before sending it to the printer. The Print screen described above includes a Preview button, or you can go directly to Print Preview via File-Print Preview (Excel 2007: Office Button, Print option, Print Preview) . The Preview screen includes buttons to Print, access the Page Setup options, display the margins so they can be viewed and repositioned as well as Zoom, navigation and close buttons.
Use Excel's built in templates to set up standard printing options
Include watermarks in an Excel print out
Include the contents of a worksheet cell in the header and footer area and also use "&&" to include a single ampersand
ExcelZone Compendium Archive - further articles and queries on printing
Subscribe to the ExcelZone newswire
To keep up with spreadsheet issues, click the graphic to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.
Number of comments: 12
AccountingWEB.co.uk 13-Jun-2008
Categories: IT Features, ExcelZone Features
Times read: 6661
you can print by custom views?
Easy to do in Excel 2000 etc. You can print multiple ranges by highlighting (or selecting from the "names" dropdown box) your first range.
To add additional range(s) highlight (or"names") as above but press down the Ctrl key as you do it.
You will see multiple ranges highlighted on your sheet. To print these either set them as Print Area or select "print" and then "print selection"
Yes, it is a strange thing isn't it! Some reasons why it might be preferable to just linking to the cells - any formatting of the original area would be automatically reflected in the camera picture; less error prone (as you mention) in that you can't accidentally change any of the linking cells (since there aren't any!); copes dynamically with inserting rows and columns; fewer calculations in the spreadsheet as a whole. There are probably others, and some problems as well - I wouldn't say it's something I user regularly, but it can be useful in certain circumstances.
Regards
Simon
Thanks for the link, Simon. I had no idea this existed. But what a bizarre beast it is!
I have played around with it a bit now, and it does work. But I can see no reason to use it when you could just as easily refer to non-contiguous source ranges in contiguous areas of a page (called 'Print' or something) using normal formulas; and then select the lot as the print area. It is much tidier and simpler to see what is going on.
I suppose it does avoid the danger of such formulas getting corrupted. Is there any other reason to use it?
Brian
"Versions of Excel prior to 2007 don't appear to include an Add to Print Area option (or does someone know better?)."
I have just tried this with Excel 2003 and it works there. Thank you - just what I needed.
Hi Brian
Thanks - glad you find the articles useful. I think the camera tool might help in what you want to achieve. There's quite a good explanation of how it works here:
http://www.j-walk.com/ss/excel/odd/odd04.htm
Kind regards
Simon
Ah-HA!
Yes this works, but it puts the second range into a different page, which is not quite what I was expecting, though I can see that this could be useful sometimes. I can use the page break preview to join them together, but then I get the intervening rows as well, and I might as well have just selected the whole lot to start with.
Thanks for clarifying, though, Simon. I do find your columns extremely useful.
Brian
Thanks Brian - I understand now. My instructions could have been clearer - after selecting Print_area in the Goto Dialog, click on the OK button. This should select the current Print_area in your sheet and close the dialog allowing you to select the additional area. Sorry for the confusion. Let us know if it works!
Kind regards
Simon
Hi Simon,
I suppose my message was a bit cryptic. Sorry.
Your instructions read 'use Edit-Go to-Print_Area to select the current area or areas, then select the additional, separate area by dragging over it while holding down the control key', but once the Go To dialogue box is open I cannot select any cells in the worksheet without closing the dialogue box first (which is what I meant by being gone).
Brian
Hi
Highlight area to set before selecting set print area from file menu.
Despite this being a good function be aware that it does not re-set so before printing anything goto View menu and select 'Page Break review. Th
If you only want to set print area once. Try
1 Highlight Area
2 File Print
3 In bottom left hand side of print dialogue box choose 'Selection'
Hi Brian
Not sure I quite understand - could you just explain a bit more about 'everything outside the dialog box being ignored' and, when you say it was all gone, what exactly was gone?
Thanks
Simon
When I tried to add to the print area in my Excel 2000 via the Edit / Go To, I found that everything outside the dialogue box was ignored while the box was open, but when it was gone, it was ... well ... gone.
Have I missed something?
