How to Easily Add Visuals To Accounting Reports in Excel
Microsoft Excel is the “Swiss Army Knife” of the accounting profession. You can create tables, graphs, formulas, format data, and even score the office pool for the NCAA tournament. If you didn’t already know how you might use Excel to solve a particular problem, just check out the thousands of templates for Excel and the other Microsoft Office applications on its website.
In an earlier article, I discussed using Excel as a tool for dashboard reporting, and many of you who have created dashboards with Excel looked at the Stevenson & Company dashboard screenshot, and wondered how it was accomplished. In particular, you will notice that the individual tables in the dashboard seem to not use the same columns as the worksheet. The tables seem to always fit perfectly on the dashboard report, and there is no wasted space. There are many pivot tables driven off of different data sets reflected on the same dashboard, and everything seems to work just fine on a single sheet.
The reason the dashboard looks fine is that we didn’t put the actual tables, charts, and graphs in the sheet containing the dashboard. We have dynamically linked pictures of each of the objects displayed in the dashboard sheet so we can resize the object to take up the amount of available space for the widget. Many of you have used the Paste Special, Picture command in Excel to create an image of a table or graph which can be pasted into another application (Word, Excel, PowerPoint, etc.). These pictures generally have to be updated manually each time the underlying data changes (e.g. delete the old picture, copy the object or image from Excel, and then use Paste Special, Picture to create a replacement object, and finally make it fit the desired space. ) The update process is tedious and the image requires a significant amount of effort each time you make a change to the underlying data.
The dashboard displayed in this article, however, will automatically update itself. This is because we use a special, undocumented Excel tool called the Camera Tool to create each object.
I’m not exactly sure when Camera Tool was introduced into Excel, but I first used it in Excel 97, almost 15 years ago. While there’s no documentation in the Excel Help file for the Camera tool, it has been included with every subsequent release of Excel, including Excel 2000, Excel 2002/XP, Excel 2003, Excel 2007, and Excel 2010. In each version of the product, you have to add the Camera tool to a custom toolbar, or in the case of Excel 2007/2010, to the Quick Access Toolbar.
To add the camera tool to your Quick Access Toolbar (QAT), perform the following commands:
- Click on the icon at the end of the QAT (shown in Figure 2) which looks like a DVD player’s eject button.
- A menu labeled “Customize Quick Access Toolbar” will appear (shown in Figure 3). Click on the option labeled “More Commands.
- The Customize Quick Access Toolbar will appear on the screen (shown in Figure 4). In the main window above the listing of commands in the left column is a drop-down box labeled “Choose commands from.” Click on this drop down menu, and select “All Commands.
- In the command listing beneath the drop down menu, select the icon for the Camera Tool by clicking on it.
- Click on the “Add” button to the right of the table, click on OK to finish, and the Camera tool will appear in your Quick Access Toolbar.
Once added to your toolbar, the use of the Camera Tool is fairly straightforward, although other than a few blog posts, you won’t find much documentation of how the Camera tool works online.
You may recall using the Paste Special, as Picture command to create a static image of a range of cells in Excel which can be resized like any graphic (e.g. pictures, drawings, etc.). The Paste Special, as Picture tool will create a static image of a range of cells – just like you would create with a digital camera.
The Camera tool is like Excel’s own version of a closed circuit TV camera. It will show an image of the selected cells, and will also update itself when the sheet is recalculated. (Unfortunately, the Camera tool seems to only work within Microsoft Excel, as I’ve not found a reliable way to use it with PowerPoint or Word.) The Camera tool is used to create a “screen view” or “self-updating picture” as follows:
- In Excel, select a range of cells whose linked picture you want to appear elsewhere.
- Click on the icon for the Camera Tool. A dotted line will appear around the selected cells, indicating that they have been selected for use by the Camera Tool. The cursor will also turn into a small, thin black plus sign (+).
- Navigate to the sheet where you want to insert the object. Although you can use the mouse to select a different sheet or a different workbook, be careful to not click on any cells except the one where you want the camera object to be pasted. (If you do click on somewhere accidentally, you will need to go back and perform steps 1 and 2 above again.)
- Click on the location where you want to insert the linked cells, and a Camera Object will appear.
- Move or resize the Camera Object as needed by clicking and dragging its the sides and corners just as you would resize a picture.
- Click on an empty cell in the workbook to “de-select” the camera object.
There are a number of blog posts where you can learn more about the camera tool:
- What is the Camera Tool and how to use it?
- The Camera Tool
- Still Another Excel 2010 Camera-Tool Bug, and How to Fix It
- Camera Tool Function in Excel 2010
- Enhance Excel 2010 Dashboards with Camera Tool and Picture Effects
Some of these will show you additional tips and tricks, and others will teach you more about the limitations of the camera tool) We will discuss how and why we chose to set up each of the dashboard objects (a table or chart) on a different tab, or sheet, in the workbook file in a future blog post.
In summary, Excel’s Camera Tool allows users to place a resizable, moveable, dynamically linked picture of a range of cells in another location in Excel. While there isn’t much documentation to support the use of this hidden feature, it is an excellent addition to any accountants toolbox. More information on the Camera tool is available from author and Excel MVP Charlie Kyd. His Excel Dashboards e-book ($29 USD) has an entire chapter on how to use the Camera Tool.