I’m starting a series on dashboards because I think creating sexy dashboards is a critical skill every marketer needs to know. It’s going to be a long series — but by the time we’re finished, you’ll be able to create dashboards that excel in both form and function. (Editor’s note: This popular series continues on Marketing Land with Creating Sexy Charts In Excel!)

Why Use Combination Charts?

The first skill we’re going to focus on to that end is how to create a combination chart. When I took MarketMotive’s analytics certification course, in my dissertation, Google Analytics evangelist Avinash Kaushik asked me why I didn’t show visits and bounce rate together in the same chart. (Disclosure: I am not affiliated with MarketMotive in any way.)

It’s embarrassing to admit this now, but I had no idea how to do that — or that you even could combine totally different metrics like that. But now, I use them all the time. The reason is that they give you the ability to demonstrate data trends visually. (So thanks, Avinash!)

Some classic metrics I use frequently in combination charts are:

  • Year-over-year data
  • Visits vs. bounce rate
  • Revenue vs. conversion rate
  • Campaign cost vs. conversions

Download The Excel Doc

If you’d like to follow along, feel free to download the Excel doc I pulled all my screenshots from.

How To Create A Combination Chart

Step 1: Have a dataset with at least the two values you want to chart. (I also always format my data as a table.)

dataset for a combination chart in Excel

Step 2: Click any cell inside your dataset and go to Insert > Charts > Insert Column Charts > Clustered Column (in 2013 on the PC) or Charts > Column > Clustered Column (in 2011 on Mac). With my dataset, I’m just going to select the Visits and Revenue columns since I have an extra column for conversion rate.

basic combination chart in Excel

Click for larger image

Step 3: Clean up your chart with the techniques I describe in this post on sexying up your Excel charts. I cleaned mine up using several of the techniques I described in that post:

  • Removed gridlines
  • Thinned out the vertical axis
  • Added an intuitive title
  • Removed the tick marks in the horizontal axis
  • Changed the default column colors to match my branding by selecting one of the columns in the series and choosing my new color using the Fill Color icon (under Home > Font for both versions)

All of these options are accessible to you by simply pressing Ctrl-1/Command-1 (PC/Mac).

combination chart in Excel

Click for larger image

Step 4: From here, you can change the chart type for one of your data series. For example, we could change the revenue to an area chart by selecting any one of the columns, then choosing Chart Tools > Design tab > Type > Change Chart Type. Once you’re in the newly revamped Change Chart Type dialog, just set Revenue to Area from the drop-down menu. (In 2011 for Mac, choose Charts tab > Change Series Chart Type > Area.)

column area combo chart

Click for larger image

Adding A Secondary Axis

Let’s say we want to also add conversion rate to the chart. I changed the Revenue series back to a column chart to decrease chart junk. One super cool trick to adding a new data series to a chart you’ve already created is to just select the column, copy it, select the entire chart (you’ll see the whole chart outlined), and then paste it in.

It won’t look amazing because we’re pasting in a value that’s less than one. But then, all you have to do if you’re following along with the download is: with the chart selected, choose the new series by going to Chart Tools > Format tab > Current Selection > Series “Conversion Rate” (2011: Charts tab > Format tab > Current Selection > Series “Conversion Rate).

To change the chart type to a line chart, I’ll break out the processes for 2013 and 2011 separately since the steps for the Mac are so different.

2013 (PC): With the Conversion Rate series selected, choose Design tab > Type > Change Chart Type. Then, in the Change Chart Type dialog, set Conversion Rate to Line from the drop-down. And put it in a secondary axis by selecting that option. Then click OK.

Change Chart Type dialog in Excel 2013

2011 (Mac): With the Conversion Rate series selected, choose Charts tab > Change Series Chart Type > Line > 2-D Line > Line. Then select the series again like you did before (it doesn’t stay selected for maximum frustration value) and press Command-1 to pull  up your formatting options. In the Format Data Series dialog, choose Axis > Secondary axis. Then click OK.

I changed the series color to green, thinned out the secondary axis, and ditched the decimal places. If you don’t need them to interpret the data, kick them to the curb.  (I almost always get rid of decimal places in my axes!)

column line combination chart in Excel

Click for larger image

A Few More Examples From The Wild

Here’s another example of a similar chart that combines a column and line chart to show year-over-year organic traffic for a client. I didn’t feel like the YoY wins stood out, so I created a calculated column that figured out the % increase/decrease in the table and then added % delta to the chart.

year-over-year combination chart in Excel

Click for larger image

Here’s another one I did for a client to show them the number of assisted conversions they were getting by marketing channel. Most marketers are only reporting the conversions in red. We should be reporting on the assists, too! I addressed this topic at length in my SearchLove presentation, Take Credit Where Credit’s Due.

assisted conversions in Google Analytics displayed in a combination chart

Click for larger image

And, here’s one more I did for a client to be able to view their PPC campaign costs vis-a-vis their results. In this case, the  first step in their conversion funnel is a call. The final step is an actual job. This chart has more data than most I create, but I wanted to capture both phases of conversions in one chart. You can see that two of their campaigns in Bing drove eight times more jobs than their AdWords campaign they’re sinking most of their resources into, for less than 1/10 the cost. That’s actionable data right there!

combination chart for PPC

Click for larger image

More Tips On Making Data Sexy

I just presented on the topic of giving your data an extreme makeover at SMX Advanced in Seattle. You can check out my presentation, which contains lots of tips on cleaning up chart data.

This popular series continues on Marketing Land with Creating Sexy Charts In Excel!

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

Related Topics: Channel: Analytics | Search & 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.

Connect with the author via: Email | Twitter | Google+ | LinkedIn



SearchCap:

Get all the top search stories emailed daily!  

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. Comments may also be removed if they are posted from anonymous accounts. You can read more about our comments policy here.
  • Gerry White

    So – the only problem is that the better at presenting sexy graphs you get – the more your boss wants you to do …! – suddenly it turns into your job! :-)

    This is a great post and I love split axis graphs (one tip – sometimes invert one axis) I love to show a line and bar graph showing change, as it can really highlight negative numbers (I also frequently manually colour in the negative months / weeks) as in 2007 you can’t easily do that automatically!

  • Sharon M. Mullins

    just as Heather replied I didnt even know that someone able to earn $8125 in 1 month on the internet. did you see this web link w­w­w.K­E­P­2.c­o­m

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

    LOL,Gerry! You’re absolutely right. When I worked in agencies I was also asked to make coworkers’ data sexy. :) Curious about your tip to invert one axis. I know I do that when charting ranking data since lower numbers are better. Is that what you mean?

    Tip: You don’t have to manually format negative columns in Excel in 2007. Here’s a hack: http://www.hichert.com/de/community/foren?func=view&catid=6&id=150.

  • Gerry White

    Awesome -thanks for the tip (sorry for the slow reply, I thought I had to be honest!)

    I have turned into our internal go to Excel guy – my resolution is to never profess any profficiency in Excel ever again!

    Yes,often the axis direction doesn’t matter, in that the correlation signals are more important… ! As long as it is clear enough (my one bad habit is creating graphs for me, and then including them in presentations without a decent enough explanation)

 

Get Our News, Everywhere!

Daily Email:

Follow Search Engine Land on Twitter @sengineland Like Search Engine Land on Facebook Follow Search Engine Land on Google+ Get the Search Engine Land Feed Connect with Search Engine Land on LinkedIn 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 Search News Recap!

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

 


 

Search Engine Land Periodic Table of SEO Success Factors

Get Your Copy
Read The Full SEO Guide