Posts Tagged: Excel


3
Mar 10

Excel Data Bars for Quick Visualization and Analysis

One of the really great features in Excel 2007 is “Data Bars”.

Data bars allow you to quickly visualize and analyze your Excel table without creating a graph.   The bars make it easy to see distinguish higher vs. lower numbers in the table without resorting to using a combination of colors to clutter up your table.   Data bars are very clean and easy on the eyes.  If used properly, you can often use a data bar in lieu of a chart.

Here is an example looking at Bounce Rate by Country.    I used red in this case to help indicate that the longer the bar, the worse the Bounce Rate.

Excel Data Bars Bounce Rate example

I sorted it by Visits, so that I can focus on those countries which are bringing me the most traffic.  Just scanning the chart, I can quickly see which countries tend to look at more than one page, vs. those that look at my landing page and leave.

A couple of addtional trends that quickly emerge amongst the top ten countries (by visitor):

1. Non English speaking countries have a higher bounce rate overall

2. Overseas English speaking countries (except India) tend to have a lower bounce rate than my home country (USA).

Now, I can take this info and think about actions to take — for example, offer Portuguese and  Spanish language versions of my site (since they are the highest number of visitors after English speakers), and consider Greece, Italian, and maybe Turkish.

In summary, I find Excel Data Bars very useful for quick actionable insight and analysis.


25
Jan 10

Excel Plugin for Google Analytics

I have been using Google Analtyics and it is great.  One of the challenges with it is easily importing data into Excel for further analysis and reporting.  One way to do this is with a nifty plugin from the folks at http://excellentanalytics.com/

Excellent Analytics functionality:

  • Build queries with all dimensions and metrics available in Google Analytics
  • Apply filters to create advanced queries
  • All queries are stored in the spreadsheet

Benefits of reporting and analyzing Google Analytics data in Microsoft Excel:

  • Get one less tool to keep track of
  • Use a familiar interface
  • Combine data from multiple data sources
  • Use Excel formulas, charts, and pivot tables
  • Define and calculate customized KPIs
  • Build dashboards just the way you like them
  • Share workbooks with other Excel users

Imported data is stored in the spreadsheet so you can sort, manipulate, and distribute data to anyone using Microsoft Excel.