Visualizing Survey Text Responses

[Jeffrey Lebowski]

Hi Chandoo.org,

First off, I want to shout out my appreciation for this site:

Recently I was in an interview for a Business Analyst position (praying for an offer!!) and I razzle-dazzled the interviewers with three nuggets of Excel wisdom that I learned directly from Chandoo.org!

1) INDEX(MATCH()) is so much more versatile than VLOOKUP().
2) Pie Charts are evil; Line Charts and Column/Bar Charts can do the job best on most occasions.
3) Word Clouds can be used to provide a visualization into text data.

So a big thanks for being such a great resource to draw on for all things excel and analysis.

Now, my question lies within the third point listed above. One of the interviewers asked me how I would analyze and provide metrics for survey questions that would elicit free-text responses. She liked my Word Cloud answer, but I am wondering if there are any other ways to provide metrics on free-form answers?

Thanks again!

 

[Hui]

The problem with free form is that 3 people can use different words to all mean the same thing but will rank as 1 value each, yet 3 people who use the same word rank as 3

Limiting values to a table of values allows for better analysis of results

I would recommend using a Pareto (sorted) Column or Bar chart

I can direct you to: http://chandoo.org/wp/2011/07/22/we-want-your-ideas-results/
Where I manually edited the Suggestions from the 101 user defined suggestions
Without that there would have been 101 suggestions of value 1 each, which would not have shown any correlation of direction

 

[Jeffrey Lebowski]

Thanks Hui.

Any chance you can provide the workbook used for the link you gave above?

 

[Hui]

A quick search using the Search Box at the Top right of this and every page will find many references to Pareto Charts
like: http://chandoo.org/wp/2009/09/02/pareto-charts/