Top 3 Excel Productivity Tips for Web Analytics Dashboards

Due to the great response for my last post “Top 5 Power User Excel Tips & Tricks for Web Analytics Productivity“, I dedicated to write another post about excel tricks. This time, I want to focus on excel tips for web analytics dashboards. Web analytics dashboards are key medium for communicating insights and trends. Building dashboard is a time consuming and tedious task. It is extremely important to use time saving excel tricks to provide actionable insights from data. Here are three powerful tricks that can be incorporable to make the dashboard usable and save time. (Note – Click here to download the excel file with all the tips mentioned below)

Lets jump into action and discuss each tricks one by one –

#1. Dynamic Charts

Excel dynamic charts add a level of interaction to plain looking excel dashboards. Users can interact and see the change happening right in front of their eyes.

To create dynamic charts, first create a data table and populate it with your data set.

Create another table with the same number of rows and columns and add a separate row for logical test value and set the value as “True”.

We will use an IF statement to populate this table using the data from previous table. Here is the formula-

IF(C$AB,CX,NA())

AB is the location of the logical test value cell and X is the location of the same cell in the previous table.

For example, to populate the cell R1A the formula will be-

IF(C$A,CJANVISITS,NA())

Once all the data is populated using the IF formula, the final table will look like the original table.

Create a stacked column chart graph with visits and orders being the primary axis and add the make the conversion data as the secondary axis.

The last step is to add a form control checkboxes/radio buttons. To add the checkbox/radio button on the excel sheet, select a cell then go to the Developer tab and click Insert icon on the Controls menu. From the Form Controls click on the Check Box (Form Control).

A check box will be placed at the selected cell on the excel sheet. Repeat the steps until you have three checkboxes. Add appropriate labels to each check box.

To assign the control value to each check box, right click the check box, click Format Control and select Checked radio button. In the Cell link: box select the “True” logical test cell location. Repeat the process for all three check boxes so you can assign each check box to the respective columns(visit to visit, orders to orders and so on).

If all the steps are completely as per the instructions then you should be able to toggle the graphs by using the check/uncheck button. This cool little interaction to your excel sheet can make you excel rockstar within your peer group.

#2 Conditional formatting for actionable charts

Conditional formatting is a tool in excel allows you to change the formatting of the cell based on preset criteria. Before you may get lost in words, let me share an example of a conditionally formatted table from one of my weekly dashboards.

Notice how the insight column graphically represents the change in daily traffic week/week. If the value of the “Yesterday” column is higher than the “Last Week” then the insight column will display a peak symbol and if the value of the “Yesterday” column is lower than the “Last Week” then the insight column will display a valley. This representation is done through conditional formatting, and it can be apllied to any metric.

This is the easiest of all the tips mentioned in this post, and it is done by using double IF statement.

#3 Condensed data tables (formula based scrolling and sorting)

When you have a data series of more than five rows it is advisable to only show the most meaningful data on the dashboard. However, there could be instances where your audience might be interested in looking at the entire data series. In that situation, you can create a condensed data table and incorporate a formula based scrolling feature. This will allow you to incorporate a large set of data in a small five row table.

Prepare an excel sheet with the raw data table.