News
Commentary
6/30/2006
10:01 AM
Commentary
Commentary
Commentary
Connect Directly
RSS
E-Mail
50%
50%

Office Letter: Printing Double In Excel

Printing two copies of a range on a single page sounds useful, and it is. It also sounds easy, but it isn't.

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 tips@officeletter.com.

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.

Comment  | 
Print  | 
More Insights
The Business of Going Digital
The Business of Going Digital
Digital business isn't about changing code; it's about changing what legacy sales, distribution, customer service, and product groups do in the new digital age. It's about bringing big data analytics, mobile, social, marketing automation, cloud computing, and the app economy together to launch new products and services. We're seeing new titles in this digital revolution, new responsibilities, new business models, and major shifts in technology spending.
Register for InformationWeek Newsletters
White Papers
Current Issue
InformationWeek Tech Digest - July 22, 2014
Sophisticated attacks demand real-time risk management and continuous monitoring. Here's how federal agencies are meeting that challenge.
Flash Poll
Video
Slideshows
Twitter Feed
InformationWeek Radio
Archived InformationWeek Radio
A UBM Tech Radio episode on the changing economics of Flash storage used in data tiering -- sponsored by Dell.
Live Streaming Video
Everything You've Been Told About Mobility Is Wrong
Attend this video symposium with Sean Wisdom, Global Director of Mobility Solutions, and learn about how you can harness powerful new products to mobilize your business potential.