Using the Camera tool on your Excel dashboard

The Camera tool is a little known Excel function which can be helpful in several situations. Probably the most obvious is on your dashboard report. You’ve been asked to make some changes, add something new into the dashboard, but it’s a nightmare because you have to spend hours adjusting all the column widths and merging cells to make everything line up neatly. Well this basic example will show you how the Camera tool can save you a lot of hassle.

 

NB: Don’t overuse this if you can avoid it, one or two images on a dashboard is fine, but it is quite labour intensive and can have performance implications if you have too many.

 

Overview

The Camera tool captures all the information in a chosen range of cells and creates a live picture of it in a different location. So any changes to the formulas, formats, values, charts, etc within the range are also reflected in the location you save the image to.

 

In this example we’ll just stick to basic data and we’re going to use a separate sheet to begin with to get our column widths sized correctly and then just lookup that source data on the main dashboard sheet.

 

Firstly you’ll need to add the Camera tool to your ribbon if you haven’t done so previously. I find it helpful to keep this in your Quick Access Toolbar in the top left of your screen. Go to Options, Quick Access Toolbar, from the ‘Choose commands from’ drop-down select Commands Not in the Ribbon and you’ll find the Camera tool in the list.

 

CameraTool1

 

Add it and the small camera icon will now appear in your Quick Access Toolbar.

 

CameraTool2

 

Select the range you wish to capture (in this case the range is on the hidden Calculations sheet) and then click on the Camera icon.

 

CameraTool3

 

Go to your destination sheet and click to paste it. You’ll see this sheet has the column widths already aligned to suit it’s current display, and as this is just an image the new figures don’t need to align with them.

 

CameraTool4

 

By default the picture has a border, to get rid of it right-click, Format Picture, from the Fill & Line menu select No Line.

 

This is just a very basic example of how it works, but this tip will save you lots of time when developing dashboards once you get familiar with using it.

Leave a Reply

Your email address will not be published. Required fields are marked *