Last month, I promised a series on creating dashboards in Excel. To kick off the series, I started with a tutorial on how to create combination charts in Excel. Every marketer should have this critical skill mastered.

You can expect this series to focus on cool visualizations in Excel.

Next up, I’m going to show you how to create a super-cool scrolling chart. These are especially helpful to show traffic trends over a long period of time, like years. They really add panache to your dashboard and save on space. It’s also a great way to show your boss or client(s) the progress they’ve made over a longer period of time.

scrolling chart in Excel from Google Analytics data

And, let’s be honest. You will also look like an Excel aficionado — and no one has to know you learned it from me!

Download Excel Doc

If you want to follow along, you can download the Excel spreadsheet I work from. It includes the data from the Google Analytics API, the Chart tab I use in the video tutorial, and the final, sexied-up chart.

As I mention in the video, if you’re using an export from the Google Analytics (GA) interface, it works just as well. I just use the API to create dashboards for clients, which you should do, too. You set it up once and then just have to update it. I show you how using a free Excel plugin (PC-swim only).

Collect Data From Google Analytics

You will first need data to chart out. I will typically use a scrolling chart for overall traffic over time and organic traffic over time — but if I have a dashboard client who’s doing paid search, I’ll use a scrolling chart to show a panoply of PPC metrics as well.

I’m going to use GA data for my example. You will need to know how to create a custom report with a Month of Year dimension. If you haven’t created one of those before, I have a quick video tutorial on how to do that. Or you can use this custom report for overall traffic or this one for organic. You need to be logged in to Google Analytics in the browser you open those links in; otherwise, you’ll get a 404 error. If you’re not reading this post in the same browser you’re logged in to GA with, just copy the link and paste it into that browser.

Caveat: The date format for the Month of Year dimension is improperly formatted as yyyymm (e.g., 201307). Excel has no freaking idea what to do with that, so you’ll have to fix the formatting (as I demonstrate in this post) before you can chart it out. I know… data drama. Pfft.

Charting Prep

Because I work almost exclusively with the GA API, my export will look a little different from exports pulled via the UI. But it doesn’t matter. As long as you have column headings, you can rock this chart. Here’s what my raw data looked like:

creating scrolling charts in Excel from Google Analytics data

If I’m charting historical data, I generally like to show 12 months at a time. Makes sense, right? To do this, we’ll need to create a subset of our data set. The perfect function to do that is the INDEX function.

The format for the INDEX function is as follows:

INDEX( array, row_number, [column_number] )

(The brackets indicate an argument that’s optional.)

Think of the INDEX function as playing the game Battleship or Bingo — the array is the board, and the row and column numbers help identify which value from a data set you want. To create the table, I won’t need the optional column number because I use separate INDEX functions for each column. It’s kind of like playing Bingo with only the B column. You don’t need to yell B each time if that’s the only column you’re calling numbers from. (Okay, fine. The analogy breaks down a bit, but you get the idea.)

Enable Developer Tab

To add a scroll bar, you’ll need to enable the Developer tab in Excel. Here’s how to do it in 2013 and 2010 and 2011 (for Mac).

Video Tutorial

From here, go ahead and view the video below to see how to create the scrolling chart. I discovered as I was creating the chart that Excel 2013 still has some bugs in it, which made it an exercise in frustration. So I redid the video using 2010.

YouTube Preview Image

Axis Formatting Alert

If you scroll through your chart and see the maximum value (or the number at the tippy top of the vertical axis) changes from one view to another, for the sake of clarity, you’ll want to update the axis so that it sets the highest value for the entire chart as the max.

For example, in the chart I used, the maximum value was 800,000 to start with and then bumped up to 1,200,000 because in Aug 2012, revenue came close to $1,000,000.You can see the difference in the screenshots below:

axis formatting in Excel

If you don’t standardize the axis, the increases in traffic, revenue, or whatever you’re charting won’t be as apparent because the scale will keep changing. You don’t want to be accused of being a data tease. (I think.)

Anyway, to change this, just select the axis and press Ctrl-1 (Mac: Command-1) to pull up formatting options. And format as you see in the screenshots below, replacing 1,200,000 with your highest value:

axis formatting in Excel for PC and Mac

Click for larger image

Formatting Tips

I don’t go through all the steps to format the chart in this video to keep it to a reasonable length. However, here are some posts that I’ve written and videos I’ve created to help you:

10 Simple Tips To Make Your Excel Charts Sexier

Give Your Data An Extreme Makeover

Change Dashes To Zeros In Excel

Formatting Dates In Excel

I also include a list of the formatting changes I made in the Excel workbook available for download. (I know — I spoil you guys!)

Opinions expressed in the article are those of the guest author and not necessarily Marketing Land.

Related Topics: Analytics | Analytics & Marketing Column | Channel: Analytics | How To Guides | How To Guides: Marketing Analytics

Sponsored


About The Author: is an SEO and analytics consultant. Her areas of expertise are analytics, technical SEO, and everything to do with data — collection, analysis, and beautification. She’s on a mission to rid the world of ugly data, one spreadsheet at a time. If you just can’t get enough data visualization tips, you can check out her blog, Annielytics.com.



Sign Up To Get This Newsletter Via Email:  


Share

Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. You can read more about our comments policy here.
  • Justin Goodman

    Can the next tutorial be on using named ranges to count the range and index only based on values within the dataset so that you dont need to know your min/max and you can add data later knowing that the chart will still work!

  • http://www.annielytics.com/ Annie Cushing

    I’m not following you, but whatever it is you’re asking, it sounds like you already know how to do it.

  • Sam Mazaheri

    this is gold. scrollable gold.

  • http://www.annielytics.com/ Annie Cushing

    Love this! :)

  • kasyallen

    You may be a geek when you call excel charts sexy, but I’m proud to be one of those geeks!

    Annie, you’ve really outdone yourself this time; I love how clean this is, and the GA API, I seriously don’t know how many hours I have wasted on reports before this came into my life – love at first sight!

  • kasyallen

    Quick question, I want to take Google & Bing Shopping out of the organic traffic, do you know if there is a way to setup filters so that these are excluded?

  • http://www.annielytics.com/ Annie Cushing

    Heh. Thanks! That means it’s only downhill from here. :)

  • http://www.annielytics.com/ Annie Cushing

    This is a great question. I found this answer re: Google, which seems to be the most accurate:

    https://www.en.adwords-community.com/t5/Measure-results/Google-shopping-results-in-google-analytics/td-p/109822/page/2

    Look for eCI’s reply to AdWiser (‎22-04-2013 06:59 AM) and follow the links the Google rep provided.

    As for Bing, I think you would need to use campaign parameters, e.g., utm_source=bing and utm_medium=shopping to distinguish them from regular ads, but I’m not 100% about that.

  • http://www.annielytics.com/ Annie Cushing

    Great! Glad it hit the mark. Even a broken watch gets the time right twice a day. :)

  • http://www.annielytics.com/ Annie Cushing

    Then create one?

  • William

    Fabulous tutorial, and nothing wrong in being a perfectionist. One question I have as a very new user to dashboards. I have a set of data that is in weeks and months but as you know there a some months with 5 weeks and some with 4, so is there anyway you can do the scroll bar with this is mind.
    I want to create the scroll bar via weeks but the middle by the month hence my problem.
    Also love your very direct answers to other comments, my kind of girl

 

Get Our News, Everywhere!

Daily Email:

Follow Marketing Land on Twitter @marketingland Like Marketing Land on Facebook Follow Marketing Land on Google+ Subscribe to Our Feed! Join our LinkedIn Group Check out our Tumblr! See us on Pinterest

 
 

Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States

Europe

Australia & China

Learn more about: SMX | MarTech


Free Daily Marketing News!

Marketing Day is a once-per-day newsletter update - sign up below and get the news delivered to you!