Our Recent Posts

Archive

Tags

No tags yet.

What is the Camera Tool in Excel? - How to Use it and Why

This is an Excel guide which will explain what is the Camera Tool in Excel and how to use it.

The Excel camera tool is a great hidden feature in Excel. It takes a snapshot of an area in your spreadsheet as a drawing object and updates whenever your worksheet updates. You can resize it and move it around your worksheet. Let’s say for example you take a snapshot of an Excel table in your worksheet using the Excel Camera tool. Now whenever the table updates with different values, the snapshot drawing object changes too to mirror the data set!

Where is the Excel Camera Tool?

You won’t find the Excel camera tool in the ribbon or the Quick Access Toolbar. Instead you have to add it to the Quick Access Toolbar. Here is how.

Adding the Excel Camera Tool to the Quick Access Toolbar

Click on the down arrow on the Quick Access Toolbar and in the drop down menu click on More Commands.

You can also right click on any of the tabs and then select Customize Quick Access Toolbar.

In the Choose commands from drop down menu select All Commands.

Scroll down until you find the Camera option on the left hand pane. Click on it and then press the Add button to move the icon across to the right hand pane. Click on the OK button.

The camera icon will be added to the Quick Access Toolbar.

How to use the Excel Camera Tool

Using the Excel camera tool is very easy. There are basically 3 steps:

1) Select the cell or range where you want to create the picture

2) Click on the Excel Camera tool icon on the Quick Access Toolbar

3) Select anywhere on your worksheet to display the picture

Let’s look at an example:

I have a list of numbers in the cell range A2:A9

Highlight the cells where you want to take a picture, in this example it is A2:A9, and then click on the Camera tool icon in the Quick Access Toolbar. The borders will turn into dashes around the data set.

Click anywhere in the worksheet and the picture of the data set will appear. You can resize the picture using the resize handles or move it by clicking on the picture and dragging it to another location. It is worth noting that Excel adds a border to the image. You can remove it using the drawing toolbar.

Now this is the best bit!

I changed cell A2 from 89 to 25. Notice the picture has now also changed.

I now changed the heading colour to grey and put gridlines in the table. The picture has now done the same!

Whenever you make a change in the data set, the picture changes automatically in real time and mirrors the data set.

It is worth noting that the data set and the picture doesn’t need to be on the same worksheet. You can have the picture on another worksheet and it will work.

You can change the format of the picture just like formatting a normal picture using the Picture Tools and Format tab.

Advantages of using the Excel Camera Tool

There are many advantages of using the Excel Camera tool in your worksheet.

Create Dashboards

If you have a large workbook which contains lots of information in pivot tables, charts and data tables you can create a summary sheet and take snapshots of the most interesting or the most important pieces of information and group these together on one page. If the user wants to drill down on the data then they can double click on the picture so they can view the original data.

Create Linked Images that Update Based on User Selection

If you have a drop down list of items and you want an image to update based on what the user has selected then you can use the Camera tool to create a linked image.

Make your Spreadsheet More Presentable

You can apply special layouts to the pictures just like with normal pictures. Say for example, you have taken a snapshot of a chart using the Camera tool, you can apply some very useful tricks. Maybe you want to rotate it or format it using the different built in Excel formats in the Picture Tools tab. Maybe you want to apply different brightness or contrast or put a shadow or glow to the picture. There are many ways you can apply special formatting to your picture to make it look better and more presentable. You can’t apply these special formatting to a normal Excel chart.

Help When Working with Large Data Sets

If you are working with a large workbook with many different worksheets and each worksheet is linked to another with formulas then using the Camera tool can help you. For example, if the user changes values in Sheet2 and it affects the values in Sheet4 then you can take a snapshot of the data in Sheet4 and see how it changes when you change the values in Sheet2.

Problems with using the Excel Camera Tool?

The main problem with using the Excel Camera tool is that it is slow. There is usually one second delays in screen refresh. This is because Excel needs to recalculate the intermediate metafile or metafiles after each and every modification.

If you have VBA macros in your workbook then these delays will be more problematic if the macro changes the workbooks contents. In these instances you can turn of screen updating in the VBA code but with pictures this has no effect.

Another potential issue is that if a user has no knowledge of shapes and pictures then they will not know how to use the picture once the Camera tool has created it. They will not know how to format it, apply different contrasts and brightness, add different colours or apply effects. If you intend to use the Camera tool then it would be beneficial to get a good understand of shapes and pictures.

For the more advanced Excel user the Camera tool isn’t exposed to the macro recorder. If for example you want to automatically apply a different format to the picture and want to see the code of how this is done then the macro recorder will not help.

For example I changed the format of the picture and recorded it. I checked the VBA code to see what was recorded and there was no code there. You can manipulate the pictures using VBA but you need a good understand of VBA and the Shapes Object Model in order to do it.

Conclusion

Now you know what is the Camera Tool in Excel. It is a little known tool in Excel but is great fun to use on your worksheets. It provides many solutions to tricky problems and is great when you want to create an Excel dashboard.

Now you know about this great feature I hope you will also start to use it.

If you have any questions on the Excel Camera tool or this tutorial or you want to give me any feedback then please leave a comment below. I would love to hear from you.

#ExcelCameraTool #WhatistheExcelCameraTool

  • Facebook Social Icon
  • Pinterest Social Icon
  • LinkedIn Social Icon
  • Google+ Social Icon
  • Twitter Social Icon

Excel Master Consultant

©2018 by Excel Master Consultant

This site was designed with the
.com
website builder. Create your website today.
Start Now