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

6 Dec

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 :
viz1

(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.

viz2

(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:

viz3

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.:

viz4

(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:

viz5

Select Humidity instead and you get:

viz6

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.

Advertisements

5 Responses to “The Camera Tool, Part 1: Picturing Free-Floating Data”

  1. Doug Glancy January 6, 2013 at 5:10 pm #

    I just wanted to let you know that I’m enjoying your blog. I’ve added you to my RSS feed. This was the first post I read, following you here from Debra’s weekly picks at http://archive.aweber.com/ctxtips01/L3AL6/h/Excel_Tips_Dynamic_chart_titles.htm.

    I’ve used the camera tool quite a bit in dashboards, but nothing as fun as this.

    • Abbott Katz January 6, 2013 at 9:03 pm #

      Thanks for your note – glad you like the blog. The camera is very underutilzed, in part because it’s so little known (even though it’s been available for a long time). It’s noteworthy that the beta version of Excel 2013 places the camera tool on the Quick Access Toolbar by default, a late-coming appreciation of its potential value.

  2. love April 17, 2013 at 10:04 pm #

    I was curious if you ever thought of changing the structure of your
    site? Its very well written; I love what youve got to say.
    But maybe you could a little more in the
    way of content so people could connect with it better.
    Youve got an awful lot of text for only having 1 or two images.
    Maybe you could space it out better?

    • Abbott Katz April 17, 2013 at 10:41 pm #

      Thanks for your reply. True, the blog’s aesthetics probably could use some upgrading – something that’s not my strong suit. Need to think about it more intensively now.

      • Doug Glancy April 20, 2013 at 3:36 pm #

        Abbott, I think Love’s remark is spam, albeit a better class of comment spam. If you google the entire comment you’ll see many pages of results.

        Plus, Love’s website looks a little suspicious.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: