The Camera Tool, Part 1: Picturing Free-Floating Data

Begin with a question belonging to the chicken-crossing-the-road genus: where are spreadsheet data typically placed? Answer: in cells.

You’re not amused. But it’s the follow-on that’s going to matter, so humor me for a bit longer.

OK – data are sealed in cells, and what could be called spreadsheet superstructures – e.g., charts, and the latter-day conjurations we call data vizzes – are almost always synergized from, and as it were balanced atop, those data. But you knew that too.

Now here’s where your patience gets rewarded.  The data – those above-mentioned, cell-sourced elementals – can be in fact be loosed from their boxy holds (and I‘m continually struck by how often beginning Excel students calls cells boxes) through a relic but little-heralded feature known as the camera tool. The camera, which has made itself available at least as far back as the 1997 release of Excel, snaps a screen shot of any range of cells – a live shot. And that means if the data in the cells you’ve camera-captured change, the screen shot changes too.

Camera screen outputs are often pasted to Excel dashboards –assemblages of data-bearing objects such as pivot tables and charts that resolove into a big financial picture, and the like :

(Source: http://blog.accountants.intuit.com/from-the-experts/excels-camera-tool-dont-leave-home-without-it/)

Those several objects above are probably camera shots of data compiled on other worksheets, and resized on the dashboard so as to accommodate one another in the design space. And again, any modification in any of the data sources will modify any camera shot correspondingly. And what this post seeks to espouse is a slightly variant application – a grafting of these camera tool cell captures to a kind of data viz – one which ushers the source data into the viz.

But before I explain what I mean, let’s retrieve the camera tool and nail it to the Quick Access Toolbar (again, I’m narrating the Excel 2010 command sequence). Click the File tab (2007 users need to click the Office button), and proceed with Options > Quick Access Toolbar (on the commands columned on the left of the window you see). Select All Commands from the Choose Command s drop-down menu and click on Camera (you can buzz there quickly by typing the letter C) and then Add.

(You can also double-click the Camera icon, thus catapulting it onto the QAT forthwith.)

And once put in place, the tool is easy to use. Select the range you want to snap, click the Camera icon (you’ll note the slender black cross replacing the default plump white indicator), and just click at the point on the worksheet where you want the photo to land. Once having alighted, you can of course drag the shot anywhere else and resize it, just as you would any graphic object. (Two cautions, of sorts:  if you’re snapping text-populated cells whose contents overflow into an adjoining cell you’ll want to autofit the cell before “shooting” it, because the snapshot will only capture the data straitened by the cell’s boundaries. In addition, a multitude of shots seems to decelerate sheet processing speed. You won’t want to splatter your sheet with dozens of captures. On the other hand, your machine is probably faster than mind)

Now let me wend my way back to the larger point. Once you place the camera tool in your virtual hands and take aim at a collection of data, you can reposition the resulting cell shots anywhere on the sheet, even as the source data – the photo’s subjects, so to speak – remain in place, in completely  usable, spreadsheet data form.

By way of a very simple introductory example, consider this assortment of test scores:

I’ll spare you the formulaic nuts and bolts here in the interests of descriptive expediency, but if you join, or concatenate, the student names and their respective scores and then camera-capture the results, you can map the students’ seating configuration as per the test day by dragging each student shot (realized from each cell in the column whose first entry is Mary 78, etc.) here and there, e.g.:

(Note as well the conditional format, imparting blue to any score equalling or exceeding 90. Again, I’m looking past those technicalities here.)

And if you’re suspecting a dodgy comradeship between Quentin and Ed you can remix the class seating for the next test and depict the updated assignments on the worksheet by dragging the above cell shots into their new positions. Continuing to keeping things simple here, you can enter the Test 2 grades in the Score column and calibrate these to your new seating alignments, casting a concerted eye at the scores for newly-distanced Quentin and Ed.

So let’s track back to that larger point. The camera shot deputizes data points – the data that typically inform data vizzes by submitting to some superordinating , front-ending graphical take on the information – into active players in the viz itself.  The data as originally posted steal their way into the viz, at the same time holding fast to their original positions, yet available for all the standard spreadsheet scrutinies – pivot tabling, filtering, lookups, etc.

And because the camera shots are in effect graphic bits, they can be superimposed atop other graphics, encouraging a different kind of mapping that needn’t call upon any additional software.

For example: for demo purposes I tracked down and pasted an outline map of the UK, and proceeded to grab some of today’s temperature and humidity data for selected cities (source: www.weatheronline.uk) and commit them to the usual row-and-column treatment. Then the user decides between the Temperature or Humidity selection in the mini-dropdown menu in cell E18, and the map then associates the pertinent reading with its city:

Select Humidity instead and you get:

You can access this demo at

UK weather demo

(Y0u may also want to take a look at the formulas in the D and E columns.)

One hopes the larger point, or points, have been made. The camera tool and its potential uses described here won’t serve to mothball the data viz tools in the contemporary journo’s box. The pictures won’t be as pretty, for one thing, but the tool

1. Lets you drag the data points precisely where you want them, and
2. With some formulaic savvy, can foster considerable user interactivity with the viz, e.g., the two-columns of Temperature vs. Humidity embodied in my demo. There’s no reason why you can’t select from 20 columns worth of data instead, any and each selection finding its way into the viz.

If this still isn’t wholly clear, I hope to add to it all in the next post. Don’t go away.