Make Changes in Excel Cells Appear in More than One Place

Q. I have a spreadsheet with a range of cells that contains frequently changing data and several formulas, and I want all of these—the changing data, the formulas and even all the corresponding formatting—to appear simultaneously in another worksheet each time a change occurs in the original one. I know that’s a tall order, and a spreadsheet expert tells me I need to create a fairly complex macro to accomplish all that. Is there no other way to do it?

A. Your expert is partly right—a macro will do the job, and it would be a fairly complex command. But I have a much easier solution that doesn’t require the use of macros.

There is a seldom used tool in Excel called Camera . As its name implies, Camera takes a snapshot of all or any part of a spreadsheet. The resulting graphic can be copied to other parts of the file and other worksheets; it even can be copied to other applications—for example, Word and Access. However—and this is where it gets very interesting—if you copy it to somewhere in your spreadsheet, that graphic is not static, it’s “dynamic”—meaning any and all changes in the original will immediately be reflected in the copy, and the copy will be a graphic—not an Excel formula.

If you’re having trouble imagining this, think of Camera as if it were a live television camera trained on a scene (in this case, a range of cells), and when it’s turned on, it’s as if the camera keeps transmitting that live picture of the range of cells to the place where the Camera graphic was copied. Thus, any changes in the original scene are simultaneously reflected in the copied graphic.

Admittedly, all this sounds awfully complicated, but in fact, it’s a lot easier to do than to explain. To begin, you need to access the Camera function. It’s probably not in your default toolbar drop-down menu; you’ll have to customize your toolbar. To do that, click on Tools , Customize and on the Command tab, bringing up the screen above.

Then under Categories , click on Tools , and under Commands , depress the down arrow until you come to Camera , which is adjacent to an icon of a small camera (

Now that you have the tool in place, highlight the worksheet, cell or range of cells you want to capture and then click on Camera . The mouse pointer will change to a plus sign. Go to the place where you want the dynamic image to appear and click on where you want the top left-hand corner of the graphic to be. To illustrate I placed the copy in the same worksheet next to the original. The original is in the A column (A1, A2, A3) and I used Camera to copy them to the C and D columns.

Notice the eight little circles around the numbers; they indicate it’s a graphic. If you grab one of the circles, you can enlarge or move the image. And, of course, if you make any changes in the original cells—to the data, the formula or the formatting—they will be reflected immediately in the adjacent graphic.