Camera Tool

Last week I demonstrated how to create a box chart. Didn't see the post? click here

Someone wrote to me asking why they would ever want to use a box chart when they could use a simple column chart to represent the same data. Well it’s a good question so let’s look a bit closer at the two techniques.

Here’s a simple dashboard comparing three different types of leaf blowers. It also shows the box charts compared to column charts.

Notice how I matched the leaf blower color in my box charts as well as my column charts. This helps provide continuity to the dashboard. Both the box chart and column charts show the same information, but the way it the data is represented is different.

And that’s the point. Excel allows you to represent data in different ways. Most people will settle for the built in charts Excel provides and that’s fine. However there are alternatives and the box chart is a good example of this. It’s a creative use of conditional formatting that provides a user the flexibility to display their data differently.

The person who emailed me also raised a valid point regarding the columns and how I needed to size them to make the box chart. He pointed out that it would be difficult to properly display data under these boxes due to the column width. I agree. It would not be ideal to place data under the boxes. However there is an easy solution.

Notice the columns D – M, O – X and Z – AK in the below example. These are the columns that the reader was referencing. They are very small in width.

To get around this problem, you can use the camera tool to place the image of the chart onto your dashboard. The actual chart itself can be on a different tab. And of course since you are using the camera tool, the image is not static (it will change as your data changes).

In the attached example I have my box charts on the data tab and the Dashboard “links to the box charts” by the use of the camera tool.

Now if you are not familiar with the camera tool, you should become acquainted.
The camera tool is a hidden feature in Excel that allows you to copy a rectangular area in your workbook and then creates a mirror image of the area as a drawing object. You can then move and resize the object. If the contents of original rectangular area changes then the mirror image also changes.

To use the camera tool, you must add the tool to a tool bar in excel menu area.

From the menu Click the drop down arrow on the tool bar and select More Commands

1. From the menu Click the drop down arrow on the tool bar and select More Commands
2. In the Customize tab and select All Commands.
3. Scroll down in the commands area until you see a little camera icon.
4. Click the Add Button then the OK button to add this to your tool bar.

To use the camera tool on our box chart first highlight the chart. When I select my cells they are outlined with a black boarder.  Next click on the camera icon that we added to our tool bar.

Now click anywhere in your worksheet and Excel will places a snapshot of the range you selected.

 Now you can move and resize this as needed.

Notice how my column widths are normal size on the dashboard tab.

So using the camera tool allows you to get around the problem of column widths.

The camera tool is a great feature in Excel but there are some issues that you need to be aware of. As shown here if you resize an image too small you will loose resolution. Also if you change the aspect ration of the image, you will get distortion. 

Need to see an example? Download the worksheet by clicking on this link.