Intuit Accountants News Central

Excel’s Camera Tool: Don’t Leave Home Without It!

Article Post Date
April 5th 2012 by Brian Tankersley

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.

The Quick Access Toolbar (QAT) in Excel 2010. Note the icon in the lower right corner of the screen, which allows users to edit the icons shown on the QAT.

To add the camera tool to your Quick Access Toolbar (QAT), perform the following commands:

The Customize Quick Access Toolbar Menu

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:

  1. In Excel, select a range of cells whose linked picture you want to appear elsewhere.
  2. 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 (+).
  3. 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.)
  4. Click on the location where you want to insert the linked cells, and a Camera Object will appear.
  5. Move or resize the Camera Object as needed by clicking and dragging its the sides and corners just as you would resize a picture.
  6. 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:

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.

Share and Enjoy:
  • Facebook
  • Twitter
  • LinkedIn
  • Digg
  • del.icio.us
  • StumbleUpon
  • Technorati
  • Google Bookmarks
  • RSS
  • Print
  • PDF
  • email
  • About Brian Tankersley

    Brian Tankersley Brian Tankersley (@bftcpa, CPATechBlog.com) is the Technical Editor for the CPA Practice Advisor magazine (cpapracadvisor.com). He teaches courses nationally through K2 Enterprises, and is a senior faculty member with Becker CPA Review. Tankersley is a Certified Public Accountant (Tennessee), a Certified Information Technology Professional, and has been a Certified QuickBooks ProAdvisor and a Certified QuickBooks Enterprise Solutions ProAdvisor since 2007. See all of Brian's articles…

    You can also visit Brian's Website

When commenting, please be polite and use the site for your own personal, non-commercial use. For the full legalese of dos and don'ts, see our Online Community Terms of Use. Thanks!

No Comments »

No comments yet.

RSS feed for comments on this post.

Leave a comment

You must be logged in to post a comment.

 
© 2014 Intuit Inc. All rights reserved. Intuit, the Intuit logo, QuickBooks, ProSeries and Lacerte, among others, are registered trademarks and/or registered service marks of Intuit Inc. or one of its subsidiaries. Other parties' marks are the property of their respective owners. Terms, conditions, features, pricing, support and service are subject to change at anytime without notice.

The views expressed on this blog are those of the bloggers, and not necessarily those of Intuit. Third-party blogger may have received compensation for their time and services. Click here (to read full disclosure on third-party bloggers.  This blog does not provide legal, financial, accounting or tax advice. The content on this blog is "as is" and carries no warranties. Intuit does not warrant or guarantee the accuracy, reliability, and completeness of the content on this blog. After 20 days, comments are closed on posts. Comments are subject to moderation. Comments that include profanity or abusive language will not be posted. Click here to read full Terms of Service.