<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Excel Tips & Tricks: The Camera Tool

Published on
50,182 Points
32,582 Views
36 Endorsements
Last Modified:
Awarded
Editor's Choice
Introduction

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:
Camera Tool -- Overview
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.


Static Pictures

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.


The Problems

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 and screens 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.
Sub CameraTool()
    
    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
    
End Sub

Open in new window



Applications

Once you know the tool, you will find that it can be used to solve many typical problems.

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

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

Special Layouts -- 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?


Advanced Camera

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 close by

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

    =CHOOSE(A1,B10,B11,B12)

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
 * Close

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:

    =SOURCE

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


Conclusion

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.


Markus Fischer
(°v°)
36
Comment
Author:harfang
18 Comments
LVL 93

Expert Comment

by:Patrick Matthews
Markus,

That was an awesome trick!  I have been using Excel for over 15 years and a three-time MVP, and I had no idea that that was even there.

Cheers,

Patrick
0
LVL 61

Expert Comment

by:Kevin Cross
Markus, thank you for this trick; I share Patrick's sentiments. :)
Voted yes above.
0
LVL 85

Expert Comment

by:Rory Archibald
Congratulations and thank you, Markus. I think this is an excellent (if you'll excuse the feeble pun) article! :)
Rory
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

LVL 58

Author Comment

by:harfang
Thank you both, mwvisa1 and rorya, for your feedback, insights, and support during the editorial cycles. Thanks also to WaterStreet for suggesting a better introduction, which led me to add the illustration.

I would like to credit Rory for mentioning the "Shift-modified" menus and the name "Picture Link" used there and in Excel 2007, and also for having me reconsider and rewrite the paragraphs about VBA.

Your contributions vastly increased the quality and usefulness of the article.

Of course, the remaining errors and omissions are my own.

Regards
(°v°)
0
 

Administrative Comment

by:Netminder
Just a note: I found (per the instructions) the Camera AND the Solitaire tools in Excel 2000 as well.

Great article, Markus!

Netminder
Senior Admin
0
LVL 58

Author Comment

by:harfang
> ... AND the Solitaire tools ...

LOL (if you forgive my French). This did get me into trouble once before. I was sent to an Insurance company to give an Excel class, and the course scope included customizing the toolbar. So I did show the "solitaire" button, probably while showing the camera tool, at the end of a busy day.

It turned out that the (lazy incompetent) IT department had "disabled" all games by *deleting the shortcuts*. Yes. Not the programs, just the shortcuts. I had thus inadvertently "hacked" into their "fool-proof protection" against "wasting time during work hours playing games". It was a viral infection: within a week every employee had a "solitaire" button. I got the blame.

I'm glad you liked it, but please don't blame me!
(°v°)
0

Expert Comment

by:Articles101
Added my yes vote above.  It's well written and good business value that needs to be communicated.
0
LVL 16

Expert Comment

by:Jerry Paladino
Marcus,

I have been using the Camera tool for years to copy Pivot Tables and Charts into PowerPoint presentations.  The linked pictures are so much easier to work with in PPT files that copying a Pivot Table and dropping it into the presentation.   Because the pictures are dynamic, monthly presentation updates become much easier.

Since the Camera has never been a widely used tool I have not found much written about it or spokens with anyone who has delved into the various ways it can be used.  Your article is a great find.  I never realized it had so many other possibilities and your exercise for conditionally displaying different pictures will be helpful for some things I am currently working on.  Thank you so much for sharing this information.

Great Article!
Jerry
0
LVL 58

Author Comment

by:harfang
ProdOps,

Thank you for your feedback. The article seems to attract a wider audience that I would have though, but I'm glad to hear that *some* users did know of the feature before reading about it here!

Cheers!
(°v°)
0

Expert Comment

by:IbDyhr
Marcus,

Absolutely cute tool.

I notice that ProdOps above mentions that he/she is using it in PowerPoint presentation, which is exactly what I thought it would be would be brilliant for. However, it seems like the image becomes static once it's dropped into PP. How is it possible maintain the "live link"?

Cheers,
IbDyhr
0
LVL 58

Author Comment

by:harfang
IbDyhr,

When you use (Edit | Paste Special...) in PowerPoint, and the clipboard contains an Excel range, the option "Paste Link / Excel Object" will do exactly that. You will have a "picture link" in your presentation, which you can update every time you open it; and only a copy of the meta-file is stored, not the entire workbook!

One cool advantage of the camera tool used in PowerPoint is that you can remap the colours (from the 'picture toolbar'), without modifying the original. There are certainly more tricks, but I'm no PowerPoint expert.

Cheers!
(°v°)
0
LVL 29

Expert Comment

by:Bernard S.
Thx for this great article Marcus, and the style you use to communicate.
0
LVL 16

Expert Comment

by:Jerry Paladino
IbDyhr,

Sorry - my previous post was not clear.  The camera pictures are not linked inside the PPT file.  I keep a master Excel file with pivot tables and graphs with the pictures off to one side that are dynamic and update as the pivot table/charts update.  A quick copy paste to the PPT file is easy.  Since the picture size is small I will sometimes paste the new month's picture over the old picture in the PPT.  Makes it easy to move it aside and refer to the previous months data quickly if you need to address a question.

Jerry
0
LVL 58

Author Comment

by:harfang
IbDyhr, ProdOps,

I mistakenly made my comment private (for editors only). Sorry about that.

Thank you also for answering, ProdOps, I thought you weren't talking about links directly from PowerPoint, which you confirmed, but that solution was worth mentioning as well.

fibo,

I enjoyed your compliment. On a technical site like EE, most effort is put into content and accuracy rather than style. So I'm glad someone noticed!

(°v°)
0
LVL 50

Expert Comment

by:Dave
I stumbled over this relatively recently at a finanical modelling website - your article is a much better reference. Well done.

Cheers

Dave
0
LVL 7

Expert Comment

by:hippohood
oooh.. it's so coool! One of fewcfunny tricks tricks that are useful too.
0

Expert Comment

by:Kandin
Thanks for the information, showed a few people in the office and they absolutely love it!!!
0
LVL 10

Expert Comment

by:broro183
hi Markus,

I'm not sure if Allen Wyatt moderates his comments or not, but I liked the detail of your explanation (& your style ;-)), so I have just included a link to your article (this one) in one of his "ExcelTips": http://excel.tips.net/T002845_Printing_Multiple_Selections.html 

I found another problem with the camera tool in excel 2003 when using it a few years ago. I found that it maintained images of "dead ranges" without automatically providing any "#Ref!" error messages that the reference was invalid. For example, if I created an image of a range (that resided in a different file), saved and closed both files, then deleted, renamed or moved the file containing the image's source, I would receive no error. Pressing [ctrl + shift + alt + F9] (or other recalc' options) doesn't force an error & neither does clicking in the formula bar and pressing [enter] it doesn't recognise an error. A quick retest in 2007 seems to show the error still exists.
(From memory, I ended up using vba to test the validity of each shape's "refers to" or ".formula" properties & putting out a msgbox if any were invalid.)

Do you know of a solution to the problem of invalid ranges?

Rob
0

Featured Post

HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Join & Write a Comment

This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month