Top 3 Excel Productivity Tips for Web Analytics Dashboards

interactivegraph.jpg
Facebooktwittergoogle_pluspinterestlinkedinmail

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.

data table

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

table3

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.

table-1

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.

performance chart

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

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.

checkboxes

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.

interactive graph

interactive graph1

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

conditional formatting

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.

if statement for insights

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

raw data table

Create another empty table with five rows for the dashboard.

empty table

Insert a scroll bar form control between the Day and the visits columns. Click on the Developer tab > Insert > Scroll Bar (form control).

scroll form

scroll table

Right click the scroll bar, click format control and add the following values-

scroll bar value

Assign the cell link value to any empty cell on the excel sheet. We will call this empty cell “Scroll cell”.

To add values to the empty table we will use OFFSET function.

offset function-

The OFFSET function will allow us to display the value from the raw data table while aligning it to the scroll position.

Start with the data column and apply the OFFSET function with the following values –

B27 = is the first cell in the Day column (start with the day column itself)

O35 = is the cell with the control form “Cell link:” value (see above)

0 = no column is selected

Leave the option to select the height and width blank.

Similarly, apply the OFFSET function to all the empty cells.

condensed table

(Note – Click here to download the excel file with all the tips mentioned above)

Hope you enjoyed the post. Please share your thoughts, insights and comments below. Your feedbacks are helping me become a better blogger. Thanks!

Here is a list of the related top blog posts on this blog –

Top 10 Outrageous Web Analytics Myths..Debunked
Ultimate Web Analytics Training Guide: From Click to Close
Facebook vs Google & Google Analytics – Final Round??
Top 3 Worst Web Analytics Metrics & Reports

Leave a Reply

Be the First to Comment!

Notify of
avatar

wpDiscuz