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.
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:
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:
There are a number of blog posts where you can learn more about the camera tool:
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.
No comments yet.
You must be logged in to post a comment.