Office Letter: Printing Double In Excel

Office Letter reader S. Jeyaraaman wrote with this printing tip for Excel:

Recently, I created a worksheet with a few columns. On selecting the print area, I found that it is occupying only half of the landscape A4 sheet. To minimize the number of photocopies I would have to make, I thought of copying the same print area in the adjacent cells, but that didn't work. I tried using the format option, etc., but at best I could paste the range "as it is" in the next sheet. Suddenly, it flashed in mind and I did the following. I selected the columns (instead of the print area), copied and pasted special on the adjacent column. Eureka! The whole matter got pasted "as it is" and when the new print area is selected, in the print preview I could see two copies of the same worksheet on one sheet of paper.

Hence, instead of making 12 photocopies, I could reduce it to 6 copies.

I tried the same thing with wider and longer worksheets for the A3 paper size and it worked, too.

At first, this reader tip seemed clumsy — there should be a better way to print two copies of the same range on a single physical page than copying what you want to print on a worksheet page. If you change the original cells, you'll have to make a change to the copied version (or recopy the range) — unless, of course, instead of making a copy you use a series of "=" formulas so that each "copied" cell is simply an "=" formula back to the corresponding "original" cell. Ugh. That's even more work.

Sadly, after trying a variety of combinations and printing endless sheets of paper (which we'll now recycle), we could find no combination of page setup and print properties (including the copies and collate settings) that would allow us to print two copies of a range on a single page. Brute force seemed to have triumphed.

That made us go searching the Internet. We found a tip at MrExcel.com's message board that provides a slightly different (and clever) approach. The message-board entry explains how you can "copy" the original cells as a snapshot, paste them into the original document worksheet, then print both the original and the snapshot on a single page.

The striking advantage of this technique is that when you change a cell in the original area, the "copied" cell also changes. The original message and directions are at http://www.mrexcel.com/archive/Printing/21648.html.

We've modified the original instructions to make things a bit clearer; the original directions had us scratching our heads for a few minutes.

1. Using the View/Toolbars command, select the Customize menu option. Choose the Commands tab, and then pick Tools from the category list. In the right-hand column, find the Camera icon and drag it to your toolbar.

2. Select the data range you want copied. Click on the Camera icon; that range is copied to the clipboard.

   

3. Move your cursor to the location where you want the copy to begin and drag your mouse to the bottom right corner of the area where the copy will be placed. (This is just like inserting a chart -- you click where you want the upper-left corner to be, then drag down and to the right to specify the chart's size.)

4. Excel displays an image (a picture) of the original cells. If you change the original cell, the value in the copy changes. Likewise, change the formatting (make a cell bold) or change a column width and the copy changes in unison. You can't change any of the cells inside the picture (and, frankly, we don't want to in this example), only cells in the original area.
   
   
5. Select as the print area the original area plus the cells that contain the picture. The picture seems to sit "on top" of the worksheet, and at first it doesn't look like it's selected, but if you click the cells the picture is positioned over, you'll select the picture, too.

   In other words, when you select the cells that you want printed, the background of the selected cells changes to blue. When you select the cells that lie underneath the picture, the picture's background doesn't change to blue, but the cells underneath it do (even though they're obscured), meaning you'll have included the picture (see illustration).

When you print the page, you'll get two copies of the cell range per page!

Extending The Idea
As the message at MrExcel.com points out, these instructions can also be used to copy a set of summary cells onto another worksheet page. It's a slick idea when you need to print two (or more) cell ranges (whatever and wherever they are) on a single page.

We know there are other ways to select multiple print areas and print them with one File/Print command, but using the Ctrl + Select method doesn't print the selected areas on the same page. Furthermore, it doesn't allow you to select the same area (in other words, you can't use the technique to duplicate the cell range, which was the original aim).

If you know of different combination of options we may have overlooked that will accomplish the task, please let us know. Write to [email protected].

The Office Letter is a weekly e-mail and online newsletter offering tips, tricks, and techniques for Microsoft Office. It offers shortcuts, explores features, and boosts productivity with hands-on how-to information for Word, Excel, Outlook, PowerPoint, and more.