Make Changes in Excel Cells Appear in More than One Place

BY STANLEY ZAROWIN

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.

SPONSORED REPORT

Get your clients ready for tax season

Upon its enactment in March, the American Rescue Plan Act (ARPA) introduced many new tax changes, some of which retroactively affected 2020 returns. Making the right moves now can help you mitigate any surprises heading into 2022.

100th ANNIVERSARY

Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.