Monitor parts of your Excel workbook without leaving your current view

How do I know what is going on in my kitchen when I am upstairs in my bedroom?

If that’s a question that is a concern to you, then you might have considered some sort of surveillance device. A web cam, perhaps.

Such a device exists in Excel – designed to answer the question: “how do I know what is going on in a part of my spreadsheet that I cannot see?”

And it is the Camera function. Once set up, it allows you to monitor other parts of your workbook without leaving your current view.

Finding the camera function

The main problem with the camera function is that it does not appear on a standard toolbar. You have to add the function in. And that involves changing your Excel settings. Here’s a step-by-step guide:

– Open up Excel and go into options: Alt F, T;
– Tap down to Quick Access Toolbar;
– Make sure that you select “All Commands” in the “Choose commands from:” box;
– Scroll down to “Camera”;
– Click “Add”; and then “OK”.

You should see the camera symbol appearing in your Quick Access Toolbar.

Alternatively, follow a similar set of steps to customize your existing ribbon so that you can access the camera function through the standard Excel menu. Choose “Customize Ribbon” when you tap down through options, select a main tab and then a new group. Add the camera function to the selected new group.

Now the camera symbol will appear in your main Excel ribbon.

Using the camera function

In F1F9, we tend to use the camera function when testing models. That’s regardless of whether it is a model that we have built ourselves with a client or a model that has been supplied to us for us to review and – if need be – repair. We use the camera to limit how much we move about the model when testing a particular aspect of it.

Here is how you use the camera:

– Select a group of cells that you wish to monitor;
– Click on the camera button in your Quick Access Toolbar or Ribbon. You will note that the cursor changes to a small cross;
– Click on the place where you would like to paste the image.

Now any changes that are made to the original (as a result of testing the model) will feed through to the image of the original. Since it is an image, you can play around with its size as with any other image.

You can also double-click on the image to jump back to the original source.

Other ways of monitoring what is going on

The camera function is not the only way of keeping an eye on what else is going on in your model. Other approaches include:

– Linking to source cells e.g. “= E42”; and
– Setting up a Watch Window. That’s Alt M, W. You will see that while you can select a group of cells, they are listed individually.

While linking to cells is bread-and-butter work for us in F1F9, it is a permanent solution. The advantage of the camera is that it sets up a quick view that can be deleted without consequence.

Checking on the checks

You will see below a worksheet from a Project Finance built during a recent 2-day classroom-based course. While optimising the model through running a solver macro, delegates were keen to see which checks and alerts were triggered, and which were not. The camera function proved an effective tool in keeping an eye on things while the optimisation process was underway.

On finalising the model, delegates selected the image and deleted it.