As I was digging into some web traffic from Brazil, I happened to notice that the most popular screen resolution was 1024 x 768. OK, that makes sense, but it got me thinking…with all the huge resolution desktop screens, wide screen laptops and odd-shaped mobile devices, just what is the most common size/shape of display screens these days. Using Nextanalytics with our Google Analytics connector, I downloaded the screen resolution numbers for our web site visitors to have a look, then I played with analytics and made it visual in Excel.

What I did:

+ I grabbed 6 months of traffic data (visits, screenresolution) into Excel for our corporate web site (www.nextanalytics.com) using Nextanalytics’ Google Analytics connector

+ Then I split the text in the “screenresolution” field (looks like “1024×768”) into a width and a height field (Advanced commands – Change Text – Split Text Columns)

+ Using Nextanalytics’ pivot command (Arrange – Swap Text Column with Columns), I swapped the width across the columns, with height down the rows

+ And then sorted both numerically (Sort – Sort Rows Numerically, Sort Column Labels Numerically) . That gave me a worksheet with the number of visits per screen resolution combination (width x height)

What I really wanted was an indication of how many people had a particular width at a particular height, or better. That sounds like a cumulative operation, so:

+ I performed a cumulative sum down and across the page (Calculations – Cumulative Down, Cumulative Across)

+ Then changed it to a percentage of the largest number (Compare – As Percent of Worksheet Maximum)

+ for visualization, I used Excel 2007’s conditional formatting and voila!

Some key thresholds:

98.85% of visits had a screen with a resolution of 800 x 600 or larger

94.5% – 1024 x 768

66% – 1280 x 800

48% – 1280 x 900

39% – 1280 x 1024

33% – 1440 x 900

24% – 1600 x 1050

14% – 1600 x 1200

5% – 1920 x 1080

3% – 1920 x 1200

Nextanalytics for Excel provides direct access to your Google Analytics data (as well as many other data sources) and easily performs many analytic transformations, delivering the results into an Excel workbook. All the power and capabilities of Excel without the “spreadsheet hell” of copying and maintaining formulas.