One such tool is the Camera tool. It’s a relatively unknown and underrated feature of Excel, and it goes back to some of the earlier versions of Excel (97-ish). This tool allows you to take a live picture of a section of spreadsheet, and place that picture somewhere else in the workbook. When the cells in the picture update, so does the picture. This can be terribly useful when you want to place a table around and between graphs on a dashboard without having to resize and arrange the cells on the tab you are working with. The Camera tool picture can be resized and moved much more easily than the table itself.
For starters, you’ll need to set up a button for this. It doesn’t show up in any of the tabs by default, so you’ll need to add it to your quick access toolbar or if you’re using 2010, you can add it to one of the standard ribbons.
The quick access toolbar is the little toolbar with the Undo, redo and save buttons by default. It should appear in the title bar of the Excel window, or maybe it appears just below the ribbon. It looks like this:
See the screenshot below for help with configuring the camera tool. Click the little down arrow on the far right to customize the quick access toolbar, and click the “More Commands” option (Orange box). You’ll see a window something like the screenshot. On the left side, there’s a drop down box (Green), select “All Commands” from that box, and scroll down until you see “Camera” (Purple) select it and click the add button (Blue). Now you’re ready to use the camera tool.
Now that you have the Camera tool set up, let’s use it. For our example, I’m creating a sales dashboard. This has 4 regions and the salesperson responsible for each, as well as 4 quarters worth of data. We can create graphs from this data, but in this case, we also want to include a table with the information, so that the person viewing the dashboard can see the detail behind the graphs. For starters, let’s select our table. I’m going to select a buffer of one extra row/column on each side of the table as well - it gives the end product a more finished look. Once you have your table selected, click the camera tool button that we just added to the quick access toolbar.
After clicking the camera tool button, your selection will have the dashed border that indicates that the data has been copied, and your cursor will turn into the thin cross-hairs. If you now click anywhere, it will create your camera tool image. See the screenshot below, you can see there’s one picture and one range of cells, the picture shows an exact snapshot of what’s in the cells. Changing data in the cells will change what shows up in the picture.
If you click on your image to select it, and look in the formula bar (The area where you normally type a formula or data in a cell), you’ll see that there is a reference to the range that was selected. You can change this range to have the camera display the contents of a different range as well. More on that later.
For now, let’s create another snapshot for our dashboard. After selecting our range, and clicking the camera button, let’s switch to the tab where the dashboard is, and click anywhere. You can resize the image to fit your dashboard, and it will proportionally resize the contents, while still displaying the information from the cells.
A Few Things to keep in mind:
1) This is an image of the cells you copied. Anything you change in the cells will change in the image. That also means that in order to change the image, you need to change the cells, so put the target range somewhere out of the way, but accessible. This has the added benefit of protecting your data. If you’re sending the dashboard in excel format, your users are less likely to change anything when they don’t see the source data directly.
2) When you resize the shape, if you change the fundamental shape of the object, it will distort the image. So if you make it more narrow, it could make your text look funny. To prevent this, always resize from a corner, and hold the shift key to preserve the aspect ratio of your image.
3) Pay attention to what shows in the formula bar. If the cell reference that shows up is a local reference (i.e. =$A$1:$D$4) then you may have trouble when copying the image to another tab. In stead of showing the data from the original tab, it will show the data from the tab where you copied it. To prevent this, make sure that the sheet name is included in any references (=Sheet1!$A$1:$D$4). To do this automatically, just click on another sheet when you create your image, so it starts with the sheet name in the reference.
4) You can also change the referenced range by changing the formula that shows up in the formula bar when you select your image. Just type your new range in the formula bar, and the shape will adjust the image. Note that this will not change the size or shape of the object, just the image inside it, so you may need to adjust the aspect ratio or size of the object for it to look right.
5) The image shows the cells you reference, but it also shows any shapes, graphs or other objects that are in front of the range referenced. This can be useful. You can create a camera tool image that displays a chart, just simply place your chart in an area, select an area of cells that covers the full size of the chart, and click your camera button. You now have a camera image of the chart.
Now that you’ve learned this favorite tool of dashboard builders, why not try it out. Next time you’re creating a report, try using a camera tool image to place some data in the middle of your report. Your audience will be amazed at how cool you are.
No comments:
Post a Comment