Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Multiple Print Areas on a Single Printed Page.

Multiple Print Areas on a Single Printed Page

by Allen Wyatt
(last updated April 25, 2016)

7

Ingrid set up her worksheet just the way she wanted, and then defined two non-contiguous areas on the worksheet as her print area. She first selected A1:C5, then held down the Ctrl key as she selected E3:G5. This selection was then defined as the print area. When Ingrid printed the worksheet, each of the selected ranges (A1:C5 and E3:G5) printed, but they printed on separate sheets of paper. Ingrid was hoping to get them on a single sheet of paper.

This happens because Excel automatically prints separate ranges on separate sheets of paper; there is no way to configure Excel to do this printing differently. There are a couple of things you can try as workarounds, however.

First, you could print multiple pages per sheet of paper. Follow these steps:

  1. Set your two ranges as your print area, as you have already done.
  2. Press Ctrl+P. Excel displays the Print dialog box (Excel 2007) or the printing options (later versions of Excel).
  3. Make sure the destination printer is properly selected at the top of the dialog box.
  4. Click the Properties button (Excel 2007) or the Printer Properties link (later versions of Excel). Excel displays the Properties dialog box for the selected printer.
  5. Browse through the available tabs and controls. You are searching for one that allows you to set multiple pages per sheet of paper. Set this control so that 2 pages are printed per sheet of paper.
  6. Click OK to close the Properties dialog box.
  7. Click OK (Excel 2007) or Print (later versions of Excel) to print the worksheet.

The result, for most printers, is that Excel combines two printed pages on a single sheet of paper. It does this by reducing the size of each of the two pages and printing them in landscape mode on the page. If you cannot find a control that allows you to specify multiple pages per sheet (step 5), it may be that your printer driver doesn't offer this capability. In that case you won't be able to use this workaround and will have to try the next one.

The next solution involves simply creating a "print" worksheet. This sheet can contain references to the original data ranges, combining them on a single worksheet. You can then print the consolidation worksheet, as it will contain only the information you want sent to the printer.

A third option involves using the Camera tool. This tool allows you to capture dynamic "pictures" of different areas of your worksheet. The Camera tool is not available, by default, on any of Excel's ribbons. Instead, you need to add it to the Quick Access Toolbar:

  1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 and Excel 2013 display the File tab of the ribbon and then click Options.)
  2. At the left side of the dialog box click Customize (Excel 2007) or Quick Access Toolbar (later versions of Excel). (See Figure 1.)
  3. Figure 1. The Quick Access Toolbar option sin the Excel Options dialog box.

  4. Using the Choose Commands From drop-down list, choose All Commands.
  5. Scroll through the Commands list until you find Camera. (Oddly enough, it has a small camera icon to the left of the command.) Select the command by clicking on it.
  6. Click the Add button. The Camera command moves to the right side of the dialog box.
  7. Click OK to close the Excel Options dialog box.

You are now ready to use the Camera tool. Follow these steps:

  1. Select the cells or range of which you want a picture taken. For instance, select A1:C5, the first part of the area you want to print.
  2. Click on the Camera tool. The mouse pointer changes to a large plus sign.
  3. Change to a different worksheet, preferably a blank one.
  4. Click where you want the top left-hand corner of the picture to appear. The picture is inserted as a graphic on the worksheet.
  5. Repeat steps 1 through 4, but select the other range you want printed (E3:G5).

Now you can manipulate the two pictures the same as you would any other graphic—stretch, resize, crop, or move them; whatever. The picture is not really a picture, however, even though it behaves as one. Instead, it is dynamic, meaning that if the contents of the original ranges are changed, then what is shown in the picture changes, as well. You can also print the worksheet containing the camera pictures, and they will appear on a single page.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8189) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Multiple Print Areas on a Single Printed Page.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Footnotes within Footnotes

Need to add footnotes to your footnotes? It's actually allowed by some style guides, but Word doesn't make it so easy.

Discover More

Understanding Templates

Templates are used to store a pattern for how a document should look. As such, they can be a very powerful tool for creating ...

Discover More

Adding a File Path and Filename

If you need to stuff the current workbook's filename and path into a cell or a header or footer, you'll appreciate the ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Printing Multiple Selections

Need to print several portions of a worksheet all on a single piece of paper? Here's an easy way you can get what you need ...

Discover More

Clearing the Print Area

Excel allows you to specify which portions of a worksheet should be printed when you send output to your printer. If you want ...

Discover More

Locking the Print Area

Excel allows you to specify an area of your worksheet that should be printed. Here's how to "lock" that area so it cannot be ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 - 0?

2016-04-25 10:32:39

John

Phil, it sounds like you want to print varying formats on separate sheets of paper. I think your best bet is to create separate "print" worksheets (as suggested above) for each of your print ranges that require print formatting different from the main worksheet.

If you want to print multiple worksheets or print areas on a single sheet of paper, consider passing the same sheet of paper through your printer multiple times.


2015-10-30 17:46:37

Phil

This is helpful, but is it possible to format each of the selected page ranges differently, i.e. difference margins, different sized paper?

Thanks.


2013-09-26 05:11:26

Tony

I didn't know about the Camera Tool either - just tried it - though it seems to work only with a range of contiguous cells. If your range spans any hidden rows or columns it won't work.


2013-09-24 11:13:40

Jennifer Thomas

Cool tip on the camera tool - I didn't know about that one! Thanks.


2013-09-23 05:29:08

Duncan

Following on from Brent's idea, you could then save the result as a Custom View (see View tab) so that you can repeat it easily.


2013-09-23 03:50:25

Wendy

That was my first thought, Brent. Thank you


2013-09-22 23:12:47

Brent Nielsen

You can also hide the rows in between which fools Excel into thinking that the print area is continuous. This way when you set the print area it treats it all as one single print area. Takes a second to hide the rows and then once you complete the print you can unhide them again.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.