Camera Tool - The Dashboard Builder's Best Friend

When you’re building a Dashboard in Excel, you want it to be useful, and clear, but you also want it to look good.  Clearly the accuracy and usefulness of the report come first.  But you’re more likely to get someone to pay attention to a report if it looks good.  For that reason, sometimes a dashboard can’t simply be a table on a spreadsheet with formatting and graphs.  Those work fine, but to really take your dashboard to the next level, it helps to get familiar with Excel’s shapes and tools that can take your reports to the next level.

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.

Formatting: a few basics

(All my lessons are taught using Excel 2010. Excel 2007 should be very similar functionally, but anything earlier than that will be completely different.  If you’re using an older version of Excel, you may want to think about upgrading, you’re missing out on a lot of great things!)


A major part of making a spreadsheet that works well is formatting. It’s not critical to the technical functioning of your spreadsheet, but it helps with the most important part of spreadsheet creation: Being able to read it later.  


When you are working with a small spreadsheet, formatting is less important than with a larger spreadsheet. But as your data grows, it can be a little difficult to work your way around a table of data if it doesn’t have some logical formatting conventions to help you out. Some of this might seem obvious, but believe me, one of the biggest impacts you can make, especially when sharing a file with another person is to have a well organized, and well formatted spreadsheet.

For the next few lessons, I’ll use a basic sales list as a starting point.  I have a spreadsheet with six columns: Order Number, Order Date, Product, Quantity, Unit Price, Total Price.  Without any formatting, it looks something like this:

Number Formatting:
The most important piece of formatting you can do is number formatting. Large numbers can often be hard to read and it can be hard to know whether something is a dollar amount or quantity, or date, etc.  So we’re going to start out by formatting some of our numbers in the sample spreadsheet. Number formatting options are  seen on your home tab in the Number section. 

 
In my screenshot above, I’ve highlighted a few things.  The red box shows you where you can find the number formatting options.  The purple box outlines some of the quick formatting options you have.  These can quickly put your numbers into the format you want with a single click. The dollar sign will format your numbers as dollars, with 2 decimal places and commas every 3 digits. The % sign will format your number as a percentage with 2 decimal places. The comma will format your number with comma separators and two decimal places.  The two symbols on the right side will increase or decrease the number of shown decimal places. If you click the drop down menu highlighted in blue, it will give you some additional options for quick number formatting as seen below.

If none of these work for you, you might want to click the small icon in the green box in my above screenshot. You can get to the same place by clicking on “More Number Formats” in your formatting drop down.  This will bring up a number formatting window that will give you a lot more options for number formatting.  In the screenshot below, you can see the orange highlighted area on the left, where you can pick your basic category of numbers, and then you have additional options on the right specific to the format that you chose.

So let’s start formatting the numbers in our spreadsheet.  Let’s start from the right side and work our way backwards.  The last two columns are Unit Price and Total Price.  These are easy to choose a format.  Let’s use the currency format from the quick options.  Select all the number cells that you want to format, and click the dollar sign. This formats our numbers with a dollar symbol, two decimal places, and comma separators.

For the Quantity Column, let’s use the comma button. Select all that data, and click the comma. You’ll notice that it adds two decimal places to the end of the numbers:

Since all my quantities are whole numbers, let’s get rid of the decimals.  With the quantity numbers still selected, click the decrease decimals button twice.  That’s the button furthest to the right in the purple box. That will get rid of my decimal places in the quantity column.




Now we’ll move on to the Order Date and Time Column.  You’ll notice in my screenshot, these don’t look like dates at all.  If you enter a date into excel (i.e. 10/4/2011) it will automatically format as a date.  But in the background, the data is stored as a number representing the number of days since 1/1/1900.  10/4/2011 is actually stored as 40820.  If you want to add time to that equation, you enter it via decimals. So 10/4/11 5:38 AM is stored as 40820.235.  For formatting this column, if we only want to know the date, we can format this as a Date by selecting Short Date from the Number Format Drop Down box.  If we want to format it with date and time, we’ll need to go into the Number format window.  See my screenshot below for an example of how to format this with the date and time visible.

So Now we’ve formatted all of our Numbers, and our spreadsheet looks like this:

Formatting Borders and Tables:

Let’s make a few more tweaks to the formatting here to make this a little easier to read.  One thing that makes a big difference if you plan to print your table is to add borders to your cells. When you’re looking at the file on your screen, It’s easy to see the different cells, because excel shows lines between the cells, but when you print it, those lines go away, and it can sometimes be hard to see where one cell ends and another begins.  For this next bit of formatting, we’re going to use the Font Section of the Home tab, and we’re going to click the divot (small triangle) next to the borders button.  This will drop down a list of options for creating borders.  

For starters, we’re going to draw a thick border around the entire table. So we’ll select the entire Table, and click the divot, and select Thick Box Border from the drop down. This will create a nice dark border around the whole table. We also want to put dotted borders between cells, but let’s just do that between each row, not between the columns.  To do this, select your whole table again, and click the divot, and select More Borders this time.

Start by clicking the line that’s outlined in red in the screenshot above. Then click anywhere along where I’ve drawn the blue line on the screenshot.  This will create dashed borders between rows.  As a final step, let’s highlight the first row in the table and click the bold icon (That’s the Dark “B” in the Font section on the Home Tab.

If you’re dealing with a very big table, and you want to do more to define the different rows, Excel allows you to format a range as a table.  There are some negatives to doing this, but it might be useful.  I’ll get into the negatives later, but for starters, let’s talk about how to do this.  Select your entire table of data, and click the Format as Table button in the Styles Section of the Home Tab.  You can see it on the far right of my number formatting screenshot. When you click that button, it will give you options of the table styles available.  Let’s pick the green one in the middle.



You’ll get a box asking where the data for your table is, this will be your current selection by default, and since our data does have headers, let’s leave that checkbox checked, and click ok. You’ll notice that our formatting from before is still there, but now we’ve got colors indicating where the various rows are, and highlighting the header row.  You also see drop down buttons that allow you to filter your table based on the data in the given columns.

Now for the caveat.  These tables don’t work the same as other data in excel.  Using a table makes your data more readable, but it can make your formulas less readable. Suddenly, your cell references become table references. So it references the data based on where it is in your table, rather than the cell within the spreadsheet.  It can make your life difficult. My rule of thumb is, if I’m going to be using the data for other formulas or reports, I avoid the table.  If I’m planning on having the table itself be the report, then I will consider formatting it as a table to make it easier to read.  Use this feature wisely, and you’ll avoid a lot of frustration.

Well, that’s the end of my lesson for today.  There are a lot more things you can do with formatting than what I’ve shown, so get in there and start playing around, learn all the great ways you can format your data for maximum readability.