Our sister site, AccountingWEB.co.uk, provides us with an interesting technique for setting the print area on an Excel worksheet to include several individual, distinct areas. This is relatively easy to accomplish by holding down the Control key while selecting additional areas. However, if the print area is made up of separate areas, then Excel will print each on a separate page.
Of course, you could create a separate sheet with links to all the different areas, arranged as you wish them to print, but this might be tricky to maintain. Changing the formatting of the source areas, or inserting rows and columns would all require you to make corresponding changes in your printing sheet.
One possible solution is to use Excel's little known camera feature. The camera takes a picture of a selected area, and you can then paste that picture wherever you want it. It updates automatically, and because it is a picture rather than a set of links to the original cells, any formatting of the source is automatically reflected in the picture. The picture will also adjust to include any rows or columns inserted within the source area.
There are two ways to create a linked picture. You can either use the camera tool, or use the Paste Picture Link option. To use the camera you first need to add the button to an existing toolbar. To do this, choose View, Toolbars, Customize. Click on the Commands tab and then the Tools category. The camera tool is close to the bottom of the list of Commands – just drag it to whichever of your existing toolbars you want it to appear on. In Excel 2007, right click on the Quick Access Toolbar, choose Customize Quick Access Toolbar, choose commands from: Commands Not in Ribbon and Camera should be in alphabetical order. Click on it and then the Add button and OKto add the camera to the toolbar.
Now select the first area that you want to include on your printing sheet and click the camera tool – this takes a picture of the selected area. Now go to your printing sheet and click at the top left-hand corner of where you want the picture to appear. The picture is inserted. You would then use the same technique to add the other separate areas to your printing sheet, and then set a print area on that sheet that includes the individual areas.
The alternative approach uses Paste Picture Link. Select your first area and copy it, go to the printing sheet and click the cell at the top left-hand corner of where you want the picture to appear. Now hold down the Shift key and click Edit on the menu; you should see an extra option - Paste Picture Link. Click on this option to paste the picture link to the printing sheet. In Excel 2007 the Paste button dropdown on the Clipboard section of the Home ribbon tab includes an As Picture option, which includes the Paste Picture Link sub-option. Copy and Paste Picture Link any further areas as with the use of the camera tool.
The camera is a rather obscure but interesting Excel tool with perhaps relatively few uses. Do let us know how you are already using the camera, or any other situations where you think it might prove useful.