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.
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.