Learn How To Create Impressive Web Analytic Dashboards - Excel Camera Tool

Analytics are the heart of any online program. They are the key vitals signs in measuring success. In a blog post I wrote in 2007 I said by having an analytics strategy in place it does two main things:

1. Establishes a baseline of expected behavior – Creating a baseline of analytics helps establish what normal activity is.  Once normal activity is established any deviations from what is normal success or failure can be determined.

2. Builds foundation to make sound business decisions – Another thing that a baseline does is it takes away what people think or feel.  It establishes a scientific approach to marketing to make better decisions when it comes to your marketing budget.

In addition to having an analytics strategy in place you need to also have a sound method to properly display and communicate the analytic results. Below I am going to share with you the art of creating a single page Web Analytics dashboard using Microsoft Excel and a feature within Excel called the Camera Tool.

The Camera tool is a hidden feature within Microsoft Excel. The tool gives you the ability to select a range of cells and make a live picture of the selected range that you can place anywhere in your Excel workbook. What the live picture means is that any change that occurs to the selected range of cells will be reflected within the picture. This includes formatting changes as well as changing a cell value. The real beauty of this tool is when creating a Web analytics dashboard your design is not constrained by the rows and columns of an Excel Worksheet. Before I give you a simple example of the Camera tool let me explain how to find it.

Adding The Camera Tool To Microsoft Excel 2003
The Camera Tool button does not show up on any default toolbars, so you'll need
to add it yourself. Here is how to add it to your toolbar.

1. Select the view menu
2. Within the view menu select Toolbars > Customize and a dialog box will appear
3. Within the dialog box on the left-side highlight tools
4. Within the right side of the dialog box scroll down, select the Camera icon and drag the Camera icon to a toolbar (See Figure 1 below).

Simple Camera Tool Example
Once the Camera tool is within your toolbar I have created a simple example to show you the power of this tool. Below is a link to download a Microsoft Excel 2003 file. Within the spreadsheet there is a single worksheet divided into the following sections. Raw data layer

• Raw data layer
• Format layer
• Design layer

At the bottom there are instructions for each layer to demonstrate the abilities of the Camera tool.

Click to download Camera tool example spreadsheet

Web Site Analytics Dashboard
Finally, I have created a single page dashboard that can be used as a monthly report for a Web site. Again, the file was created using Microsoft Excel 2003. Any feedback about the report or this blog post is greatly appreciated. In the future I hope to share with you other dashboard designs and useful tools.

Click to download monthly Web site analytics dashboard

Click to download PDF version of dashboard

Note: The above examples are offered as is with no warranty. InteractiveAngle.com accepts no responsibility for any loss arising from your use of them.

 

Comments

[Steve Strickland]

This dashboard is an awesome tool!! Gave me a great place to start! Thank you.

[Melissa]

This is a great tool. Thanks so much! I encourage you to create and blog about more!