Last month, Stephanie Evergreen wrote an awesome guest post called “Six Steps to Great Charts” with lots of practical tips for using the Excel chart feature to visualize your social media measurement data. The six steps:

For step 1, she suggested using Juice Analytics chart chooser tool. I wanted to learn more about what particular chart format is better suited to visualize a particular comparison or insight from social media data. And, can the process of selecting the right chart format help you better understand your data? To help answer those questions, I consulted a classic, “Say It With Charts” written by Gene Zelazny, Director of Visual Communications at McKinsey which is a sort of Strunk and White for graphs and charts and used it to guide creating charts in Excel. Here’s what I learned:

When it comes to charts to display quantitative data, there are only a few basic chart forms to select from. Here’s the different formats and some pointers on when and how to use them for reporting on your social media metrics and data.

Pie chart

Pie Chart: Use a pie chart when you are making a point about the size or percentage of each component compared to a whole. The example of above is the percentage of gender for total audience. Your eye is used to measuring in a clock-wise motion, so you should position the most important segment against the 12 o’clock line. To make the most of pie charts, do not have more than 6 slices. Select the five most important components and make the rest into other. Pie charts are the least practical of the chart forms and most often misused.

As soon as you need to need to compare the components of more than one total, avoid using a pie chart and switch to a bar chart or column graph.

Bar chart

Bar Chart: When you compare different items, use a bar chart. The vertical is used to label and measure different items. In the example above, the bars are measuring the unique number of monthly web visitors from source. You have some choices in how you arrange the bars or items. You could arrange them in alphabetical order, or low to high or high to low (best to worst) as I did above. Think about what order best stresses the point you want to make and make sure you sort your data in excel the right way.

Use can also use color to reinforce the what the data is telling you or the title of the chart. In this example, I made the item with the best traffic referral a different color from the rest, using Facebook blue. I had to use format options in Excel and change the colors of the bars manual. Zelazny also suggests that the space between the bars be smaller than the size of the bars. The default in Excel is to have thin bars with a lot f space. I had to use the editing series option and reduce the “gap width.” Here’s how to do it.

You can add a scale at the top or numbers at the end of the bars, but not both because it adds clutter. Use the scale if all you want is a quick study of relationships, but use the numbers if they are important to your message. Sometimes, you might want to use the scale and the one number that needs emphasis. There are variations on the bar chart that you use in Excel, but these are used for complex data sets. Keep it simple!

The pie chart and bar chart are used to compare different components with another at one point in time, but you have data that is showing changes over time, you can use a column chart or line chart. Zelazny says that picking between the two forms depends on how many data points you are plotting – fewer use the column chart, more (many years) use the line chart. Also, column chart is best for representing data that “reset” every month and line charts best for cumulative data.

I’ve created two different examples below with some notes about how to maximize each.

Column chart

Column Chart: The chart illustrates click thrus on Twitter to links by month and each month starts over again. The suggestions for making the most of column charts are similar to bar charts.

Line chart

Line Chart: The line chart is one of the most often used of the five charts. It is easiest to understand your data – whether the trend is increasing, decreasing, fluctuating, or remaining the same. It is best used when there is cumulative data, like growth in subscribers, followers, or fans.

Make sure your trend line is bolder or thicker than the horizontal grids, again requires changing the style of the lines in the chart format menu. Grids are there for reference, not dominate visual attention.

The line chart has a variation – the grouped line chart which compares the performance over time of two or more items. The challenge is figuring out how many trend lines you can show before your chart looks like a bowl of pasta. One technique to de-clutter is pair your trend lines, although this requires using more charts.

Column charts and line charts can also be used to comparison of frequency distribution these are called histograms (column) and histographs (line). This shows how many items fall into a series of progressive numerical ranges (distribution). Column charts are used for fewer ranges and line charts more ranges.

One thing I discovered: If approached representing data on a chart as a design process focusing on my key point and using the customization options (color, size, grids, order, title), my charts were vastly improved over the default Excel options. In other words, I used Excel to as a sense-making tool, not just a chart creation tool.

Want to go deeper in charts and graphs in Excel? Here’s a terrific resource from one of my favorite data nerds, Ann Emery. (Hat tip to Susan Chavez)

What are your tips for making sense out of your data?

How do you visualize your social media data in excel?

16 Responses

[…] Last month, Stephanie Evergreen wrote an awesome guest post called "Six Steps to Great Charts" with lots of practical tips for using the Excel chart feature to visualize your social media measurement data. […]

Maybe we can help too, in line with free data analysis, http://datahero.com/ is a great tool. You can import spreadsheets and we’ll suggest graphs for you to help make sense of your data. Send me your email address and I can send you an invite!

Hi Beth – no, not a geek, but I used to be a social researcher so I have experience in analysing and presenting data using charts.

Now I’m an organisation change consultant, so I’m interested in other ways of meaning-making as well, mainly conversational. But making meaning out of quantitative data still has its place in my work, and sometimes a chart can just be beautiful – like the work that Hans Rosling has done.

Unbelievable in this day and age, but for over a decade I made a living by creating final presentations for (name drop Fortune 500 here). I cannot recall a time I didn’t bring the Excel charts of clients into PowerPoint and rip them apart for some real visual impact, but nothing cutesy.
Ask yourself “what am I really trying to say here?” Often you need a simpler representation. Leave the hard data and endless points to a printed handout or an optional frame/slide, maybe even a spreadsheet. The number crunchers in the audience much prefer data in hand to analyze up close and will refer to this to ask the pointed questions.

They need a whitening shampoo to keep them from becoming too dingy.
So if you see a few of hair breaking every time you take a shower or comb your hair don.

Ingredients varies according to product line and ranges from green tea, goji berry, guru kola
and many others. Usually contain herbs that are natural and have a long history of actually benefiting your hair.

Good overview, i feel like i learned soemthing. And i consider the data given about social media a neat addition, usually there is just gibberish or generic data in the charts. I think very unexperienced excel users might need a little introduction to charts in general though, before they can go on to the part where you explain hw to choose the right one. http://www.excel-aid.com/excel-bar-chart-function-creating-a-default-column-chart.html does this quite well i think. Hope that helps someone to get started with excel.