Back in the years when I was teaching Excel 4 (yes, version 4, when .xls meant "worksheet" and not "workbook"), I had a couple of recreational exercises to use when the class was getting tired, after the afternoon break. One of them was the "Camera Tool".
It's one of these things that the original development crew of Excel -- an excellent team by all standards -- added quite possibly only because it's fun. However, although it's still there, it seems that Microsoft never wanted to publicize it, probably because it would put too much strain on the help lines. Indeed, the tool comes with a special kind of potential problems, some of which are impossible to troubleshoot over the phone.
On the other hand, it proves to be invaluable for the creation of dashboards and complex reports, and allows some spectacular layout special effects.
In a nutshell, the Camera Tool, also known as a Picture Link, is very much like a webcam. Anywhere in your workbook, you specify the region you want to monitor, which can contain graphic elements; anywhere else, you show a dynamic image of that range, exactly the way it appears:
Everything is captured by the camera: values, formatting, colours, shapes, even the gridlines. What's more, the image can be altered like any other image: scaled, rotated, dimmed, stretched, etc.
Whether you are reading this article looking for a solution to a specific problem or just to enhance your knowledge of Excel, please take an hour or so to play with it. You will find no demo file here, only some guidelines to get you started in your own exploration and one advanced exercise.
A Well Hidden Tool
The best way to access the camera tool is to find it in the toolbar's or ribbon's customize menu. Up to Excel 2003, use:
* menu (Tools | Customize), or right-click a toolbar and choose "Customize"
* tab [Commands]
* category: Tools
* scroll down until you locate the Camera
* drag and drop to any existing toolbar
* close the customize dialog
As a side note, almost an Easter egg, you will find close to the camera tool two other icons labelled "Custom", one showing a deck of cards, the other a calculator. These are meant to be customized and assigned to a macro of your own. However, if you don't, they will attempt to launch respectively the Solitaire game and the Windows Calculator...
In Excel 2007, it's still hidden but you can find it following one of these paths:
* office button (Excel Options | Customize (left pane)),
or right-click a toolbar and choose "Customize Quick Access Toolbar..."
* choose commands from: All Commands or Commands Not in Ribbon
* scroll down until you locate the Camera
* click "Add >>" or double-click to add to toolbar
* close the customize quick access toolbar dialog
If the tool isn't installed, you can also use a little-known menu modifying trick. Up to Excel 2003, the menu shows slightly different items when the Shift key is pressed. To obtain the camera feature, you can thus follow these steps:
* Select the range you want to monitor;
* choose (Edit | Copy) or press Ctrl+C;
* select a destination cell;
* press and hold Shift while selecting (Edit | Paste Picture Link).
In Excel 2007, the Shift modifier is no longer used, and the options "Paste as Picture" and "Paste Picture Link" are directly available in the copy-paste process. So the Camera Tool, under a different name, is now more easily discoverable.
When you copy and paste a range or an object, you can also choose to "Copy as Picture" and "Paste Picture". Up to Excel 2003, this is available using the Shift key while navigating the menu. However, the result is quite different.
A static picture is disconnected from the copied area, and becomes an independent object. You can rotate it, stretch it, and otherwise format it as picture, and produce spectacular layouts not easily achieved otherwise. Furthermore, you can directly copy graphic elements such as charts, and paste them as picture. But if you need the picture to update automatically with the source range, the link needs to be preserved.
It's easy to distinguish between static pictures and "picture links", produced by the camera tool. Both are rectangles containing a vector graphics picture, but picture links also have a formula. Simply select the picture and look in the formula bar. If it contains a range reference, it's a picture link.
Incidentally, if you add such a formula to an existing static picture, say "=A1:B3", it becomes a "picture link". Conversely, deleting the formula makes the picture static.
How it Works
In order to truly understand the tool, it's important to know about the internal mechanisms. Let's start with something more familiar: open a scratch workbook with some data, select a region of cells, and copy. Then switch to another program like Word, and use "paste special". One of the options will be "Picture (Windows Metafile)". Once that picture has been pasted, you can try to edit it, using the context-menu. By playing in edit mode, you will discover that the region you copied has been converted to a series of elements, namely shapes and text boxes, which you can drag around, resize, overlap, etc. It's no longer a worksheet range, it's a vector image of the pasted range.
The camera tool uses the same intermediate format. The target area is converted to a metafile, which is in turn displayed in the viewing box. But let's try it.
* Select a region, possibly containing shapes or a graph
* Click on the Camera Tool
* Click anywhere else, or use the pointer to draw a rectangle
You have basically created a rectangular shape, which we will call the "screen", displaying not only the selected range, but also any shapes or charts overlaying it. Let's call this range the "frame".
Notice that you can manipulate the screen
just like any other rectangle. Try to resize it or to rotate it. When stretching the rectangle, the underlying metafile structure becomes apparent. When stretching text, the characters themselves are stretched to match. Likewise, all elements are rotated with the container.
Also notice that the image is dynamic. Any change made to the frame
region is immediately visible in the screen
. Again, this includes formatting, and any overlayed object. Thus, it's much like a webcam, and not just a snapshot image.
Finally, when the screen
is selected, you see a formula indicating the address of the frame
. When you double-click the screen
, the frame
region is automatically selected.
If you have played long enough, you will have already encountered the main problem with the camera tool: it's slow. One-second delays in screen refresh are frequent, whether your are manipulating the screen
or changing information in the frame
. This is because Excel needs to recalculate the intermediate metafile (or metafiles) after each and every modification.
These delays are especially problematic when VB macros are used to modify the workbook's contents. Normally, the macro can turn off "screen updating" to speed up processing, but this has no effect on "picture links". If you want to use the camera tool to create a dashboard or a report for a VB intensive workbook, you will need to create it in a different file. "Picture links" work across files, so you simply need to make sure the "dashboard file" is closed while you run the macro. When done, open the file and refresh the links.
Another problem occurs when the frame
is overlayed with the screen
. It's much like when you are filming a monitor showing what you are filming. The result can be spectacular, but it isn't fast. Something similar occurs when a "screen" is part of the frame
of another camera. Anyway, when you reach a dozen frames
visible at the same time, your worksheet becomes unusable.
As fun as it is (and it really is), the exercise is quite ridiculous. Why would you place a screen
so close to the frame
for any serious reason? In real-life situations, you should follow this simple rule of thumb:
Always place the "screen" in another worksheet than the "frame", possibly in a different file
This removes the main problem: the redraw cycle. When working on the frame
, Excel will leave the screen
alone, because it's not visible at the time. When working on the sheet with the screen
, its content doesn't need to be recalculated, since it comes from another static sheet.
A lesser problem is that many users are not familiar with shapes. A shape is anchored in the cell under its top left corner, and can move and resize with the cells underneath. Since a camera screen
shape can also automatically resize according to the frame
, this can become very confusing. The same can be said about image controls: brightness, contrast, transparency, etc. This leads to the second guideline:
If you intend to use the camera tool, it's very useful to acquire a good understanding of shapes and pictures.
Finally, the camera tool isn't exposed to the macro recorder. Manipulating "Picture Links" from code is of course possible, but it requires advanced knowledge of the Shapes Object Model. Going into details would be beyond the scope of this article, but here is a tiny primer. When run on an empty sheet, without any existing shapes, the following code will create a picture object aligned with the cell D2, linked to the range A1:B4.
Range("A1").CopyPicture ' place any picture on the clipboard
Range("D2").PasteSpecial ' paste it on the sheet
ActiveSheet.Shapes(1).DrawingObject.Formula = "=A1:B4" ' create the link
Once you know the tool, you will find that it can be used to solve many typical problems.
-- Consider a complex workbook used to synthesize information in many ways: pivot tables, charts, consolidations... You might want to create a cover sheet presenting the essentials in as little space as possible. Without the camera tool, you can either create the dashboard manually (every month? every week?) or recreate all the necessary elements, which becomes quite tedious if it involves pivot tables and charts, and very difficult to maintain. Using the camera tool, you simply regroup snapshots of every interesting range of the workbook on the front page. If more details are required, the reader can simply double-click on any screen
to view the original information in context.
-- This is a similar problem. How to print information from several worksheets on a single page? How to reconcile the requirements of the users of the workbook (readable screen font, easy navigation, intermediate calculation sheets) and those of the printed report (professional layout, small font, grouped information)? Again, by using the camera tool, you can easily create one sheet for your report, controlling alignment, rotation, individual resizing of charts, adding text boxes, etc.
-- The intermediate metafile format allows for many spectacular (and sometimes useful) tricks. Can I show text at a 30° angle? Can I have a table including formulas, but vertical? Can I stretch the characters of the total to be double width? Can I have a tiny icon of my chart, showing the actual chart? Can I make my chart look disabled, greyed or washed out? Can I create a linked cell, but linking the background colour as well as the content? Can I show an enlarged detail of my chart next to it?
Many spectacular results can be achieved with the tool as it is. For example, use the context menu on a screen
and try the various formatting options. Since it shows a picture, the picture toolbar is available as well, including gray-scale, black-and-white, and washed-out modes (all quite useful for reporting).
Again, since it's a picture, you can crop the visible area. This is useful if what you want to show does not perfectly align with the gridlines, or if you need to fine-tune the internal margins. The screen
has its own border and its own background (which can be transparent or semi-transparent).
The resizing of the screen
is tricky. If you haven't modified the size, it will automatically resize depending on the frame
. In other words, if you enlarge a column of the frame
, you will see the screen
resize to match. Once you have modified the size in any way, the behaviour changes: the screen
will remain at the same size, but the contents will be stretched accordingly. Sometimes, you need to simply delete the screen
and recreate it, but that takes only seconds.
When you select a screen
object, you have already noticed that the formula bar shows the reference of the frame
. If we can manipulate this expression, it becomes possible to go one step further, namely conditional screens
. This is explained in in the exercise below.
Exercise: The Semaphore
The idea is to show one of three shapes, depending on a value (which can be the result of any formula). The exercise is quite short.
* Open a new sheet
* Enlarge the cells B10 to B12
* Place different shapes in these three cells
* Change their background colour for effect
* Select cell B10
* Use the camera tool to create a screen
You notice that the formula of the screen
reads =$B$10. You can change it manually to show instead =B11 or B12, with the expected result. The idea is now to use a conditional statement instead, namely
Unfortunately, Excel will not accept a formula here, but there is a way around that. We will create a named formula.
* Choose (Insert | Name | Define...)
* Type "SOURCE" in the first box, without the quotes
* Type "=CHOOSE($A$1,$B$10,$B$11,
$B$12)" in the "refers to:" box
The $'s are not really necessary, but they make the formula more readable, as it won't adjust depending on the current active cell. Now select the screen
again, and change the formula to:
Finally, change the value in A1 to 1, 2, or 3, and observe how the screen
displays the expected shape, or rather the expected cell containing a shape. If you want only the shape, you can use a transparent background and no borders.
You have it!
In this exercise, we have created a graphical semaphore. But if you remove the shape from the cell B12, you can now conditionally show a shape, or nothing (if you use a transparent background). Replace the shapes by charts, and you can conditionally show one of several charts, depending on a formula. The same is true for any information from your workbook. All this without one line of VB.
In short: conditional formatting of (or selection of) shapes and conditional layout of reports...
The Camera Tool has always been a success in the class room, because it's fun. It has also provided me with elegant solutions to many tricky problems, including here on EE. And I ran across at least one book about "dashboard technology using Excel", which relied almost solely on this tool... and on the fact that it has been so little known.
I hope you will find this tool fun to discover and useful in your applications.