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-
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-
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.
Create another empty table with five rows for the dashboard.
Insert a scroll bar form control between the Day and the visits columns. Click on the Developer tab > Insert > Scroll Bar (form control).
Right click the scroll bar, click format control and add the following values-
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.
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.
(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