Latest Stories

Make Changes in Excel Cells Appear in More than One Place

BY STANLEY ZAROWIN

Related

TOPICS

Uncategorized Article

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 ( ) and drag it to your toolbar.

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.

When professionals prepare written material for readers inside their organization or outside, they should make sure that no errors distract from the message they need to convey. Take this short quiz for practice in subject-verb agreement.