Of all the Excel features that users don’t use very often, the Camera tool probably is the most powerful. This tool, also known as a Picture Link, returns a real-time image of any range in Excel.
This tool is so useful that I devoted a full chapter to it in my ebook, “Dashboard Reporting With Excel.”
The most frequent reason I use the Camera tool is that it allows me to position a table in my report while ignoring the row and column settings in the report worksheet.
For example, the figure above shows a simple table in Sheet1. And this figure shows a live version of the table in Sheet2. Notice that the table ignores the row and column boundaries in Sheet2.
One of the benefits I described in my ebook was that you can re-size the Camera image, which allows you to precisely fit the image within a space. Here, for example, I stretched the table to fit within the space available for this blog.
Excel's Camera tool object, stretched to an extreme.
In other words, the Camera tool allows you to stretch your figure more than you really should.
In Excel Dashboard Sample: Weekly & Monthly Top-Ten Activity Reports I introduced two Excel dashboard reports that make extensive use of the Camera tool. The author of those reports, Chris Helfrecht, uses the Camera tool extensively in that workbook.
The original table in a spreadsheet.
Although Chris didn’t stretch his Camera objects to the extreme that I did above, he did stretch them more than I would recommend.
To illustrate, here is one of Chris’s tables in one sheet of his report workbook. The font in the body of this table is 8-pt Calibri.
A corner of the table above, returned by a stretched Camera object. Both the chart and the original table use the same font.
Here’s a corner of this table as it appears in one of the reports, along with a corner of an adjacent chart. Both the chart and the table use the same font, 8-point Calibri. But the table’s font appears much larger because Chris stretched the table to fit the space available within his report.
To see why this approach isn’t a good idea, let’s get back to basics.
When readers study a report, their eyes are drawn to differences. For example, their eye is drawn to headlines because headlines typically are larger and bolder than body text. And their eyes are drawn to italicized words, because the italics version of the font is different from the standard version.
Therefore, by stretching the Camera object to fit the available space within a report, we create a difference in the display, but we do so for no relevant difference in the data. We therefore distract readers, and slightly confuse or irritate them. And we create reports that look unprofessional.
To avoid this problem, I seldom use the Camera tool’s ability to stretch the object. Instead, when I need to expand or contract a Camera figure within a dashboard report, I adjust the underlying spreadsheet. I change column widths or row heights. Or I add or delete data. Or I add or remove commentary. And so on.
And when I do these things the Camera object automatically expands or contracts in response to my changes in the underlying spreadsheet. This allows the Camera object to maintain an accurate reflection of my original design.
You might try to do the same.
Comments on this entry are closed.