An Easy Way to Tabulate Open Ended Survey Questions


Making Sense of  Open Ended Survey Questions

Tabulate Open Ended Survey Questions

This post is going to go over a very easy, manual way to tabulate open ended survey questions.  I wouldn’t suggest you do this for 10,000 surveys,  but the system works very well for a few hundred survey responses and is easy to do.

WHY DO WE DO SURVEYS?

Marketers are always working to find out more about their customers and potential customers.  Asking them questions is one of the most powerful tools we have.

When you are developing your survey, it is quite possible that you will shy away from open ended questions.  It can seem like a lot of work to even begin to make sense out for several hundred open ended responses.  Thinking about trying to sort them into something meaningful can seem impossible.

EXAMPLE DATA FOR THIS POST

While you can end up with survey data from lots of sources, I’m going to work with some data I collected creating a Facebook Group.  Group Administrators are allowed to ask people who ask to join the group up to three different questions.  These questions can be very useful in determining if a person “fits” in the group and should be allowed to join, however Facebook doesn’t save the responses and IMHO that is almost criminal.

Recently a new tool became available called Group Funnels.  This is a Chrome Extension and it lets you save the Group Join Answers into a Google Sheet (Google’s version of a spreadsheet).  While the driving force behind the development of the tool was to collect email addresses, the other answers SHOULD NOT be thrown away.  Many group managers realize the value of the data and are copy/pasting or retyping answers, but Group Funnels is a better way.   (BTW  My Affiliate link to buy Group Funnels is http://PrintOnDemandAtoZ.com/groupfunnels.  You can go directly to their .com site if you want, but I thought I’d mention that I do like the tool.)

I also use this technique to analyze responses from surveys created with Google.com/forms.  I’ll be writing a post of that soon.

Now let’s look in more depth into why and how to use the data you are collecting.

YOU NEED OPEN ENDED SURVEY QUESTIONS!

I’ll tell you right now that avoiding open ended questions is a mistake.  The fact is that open ended survey questions are VERY powerful.  They give you  information that you just can’t get from multiple choice questions or yes/no questions..

  1. Answers in the respondents own words – they say things differently than we do and it can be a real boost to use their words instead of ours when you want talk to them.
  2. The answers can (and WILL) reveal concerns or appreciations that you never thought of.
  3. As you condense the raw data down into usable information the distribution of issues that you do know about may be very different than you think it is.
  4. Respondents are less likely to “give you want they think you want” when they have to use their own words.

THE KEY TO THIS SYSTEM TO TABULATE OPEN ENDED SURVEY QUESTIONS

People say the same thing using different words.  Your job is to create a category that includes all the ways that the respondents say the same thing.  For example:  In my survey I see that people want  “everything”, “to get  started”, “learn it all”, “know which way to go”, “see what POD is all about”, and more.    I decided that all of these could fall into a category that I named “Everything”.   To me this means that they don’t have specific questions and from the other response and talking to them online I know that most of them are total beginners.  Categorizing the gist of each response is the key to turning hundreds of different responses into a few buckets that makes sense of all the answers.

A SIMPLE WAY TO  TABULATE OPEN ENDED SURVEY QUESTIONS

While I’m sure there are expensive services and expensive software that will help you make sense out of your survey, I’m going to show a simple way to turn this:

RAW DATA

Tabulate Open Ended Survey Data
Tabulate Open Ended Survey Questions -original Google Doc

into this:

USEFUL INFORMATION

Tabulate Open Ended Survey Questions - What we'd like to have

WHAT DO YOU NEED BEFORE YOU START?   

  1. A yellow pad
  2. A pen or pencil
  3. A printer

That’s it.  As I said, this is really simple:

Tabulate Open Ended Survey Questions – STEP 1  – PREPARE THE SHEET

We need a printed copy of the survey responses.   It can be done with an electronic copy, but for me this is one of the few times that a printed copy works better.

Tabulate Open Ended Survey Questions - Google Sheet with lots of white space

The survey question section of my spreadsheet looks like this:

If I print this, I’ll get page after page and most of it will be the question repeated over and over.

There are two ways to handle this.

Tabulate Open Ended Survey Questions - narrow a column

Both of the methods require you to put your cursor on the little line shown by the arrow and click and drag to make Q2 and later the Q1 columns narrow.

Method 1 – Leave the Question like it is and tell Google to CLIP the overflow.  As you can see in the previous image when Google tries to show us all the words we end up with a ton a white space that we don’t need.

Tabulate Open Ended Survey Questions
Tabulate Open Ended Survey Questions – clip a column

#1 – Click on the Letter at the top of the column to highlight the whole column

#2 – Click on FORMAT

#3 – Click on Text Wrapping

#4- Click on CLIP

#5 – This is what the column looks like.

Method 2 – All the previous steps plus one.

I like to go one step further and type a short word into the top row of question column.  Then I copy and paste it in the next row.  When I have five or so, I copy all five and paste them five at a time (then 10 etc.)

The main reason I do this is to indicate how much of the data I have analyzed.  When Group Funnels adds new rows, the new rows will have the full question.   If I want to add more responses to my analysis, it is easy to tell which ones I haven’t done.

Tabulate Open Ended Survey Questions - Shows clipped columns

PART OF THE FINAL SPREADSHEET

The arrows are pointing to the narrow columns I created.

Analyze Open Ended Survey Questions – STEP 2  – PRINT THE SHEET

The next step is to print just the section of the spreadsheet that we want.

You place your mouse cursor in the cell at the upper left corner of the section you want and drag it to the lower right corner (which may be a couple of hundred rows down and out of sight.)

Tabulate Open Ended Survey Questions - highlight area to print

HIGHLIGHTED ROWS AND COLUMNS

This section (and lots of rows you can’t see) is the part we want to print.

You can click Ctrl-P or the Printer Icon to tell Google you want to print.

Tabulate Open Ended Survey Questions - print icon
Tabulate Open Ended Survey Questions - print options

THE DEFAULT PRINTS TOO MUCH

#1 – I know that it hard to see, but the default print area gives us all the data in the spreadsheet and that is way too much.

#2 – At the top right there is a small drop down that say CURRENT SHEET.  Click this and change to the SELECTED CELL option.

Tabulate Open Ended Survey Questions - final results

WORKING PRINTOUT

This is one page of the printout we have been working to get.  It takes much longer to describe than it does to do the work.

Analyze Open Ended Survey Questions – STEP 3  – COLLATE THE DATA

Now that we have our printout, we need to grab our yellow pad and pen and go to work analyzing our open ended survey questions.

Start by reading down the first set of responses and see if you see some common theme popping out.  If you do write them down on you yellow pad.  Use an abbreviated set of words that you will understand later when you read them.   Here is an example I started with.

STARTING WITH THE YELLOW PAD

Tabulate Open Ended Survey Questions - Yellow sheet with categories

Now you read the first answer off the printout and decide which category it fits into.  If it doesn’t fit, add a new category.  Do three very simple things:

  1. Write the category number you used onto the printout beside the response.
  2. Add a slash mark beside the category on your yellow pad.  I am using the four slashes followed by a cross slash.
  3. Flag responses that surprised you or that had powerful wording that you can use later.  (You can put stars on the page or paste stickers on the page, it doesn’t matter as long as you can find those SPECIAL responses later.)

Here is what the Printout looks like after I’ve gone down the page.

Tabulate Open Ended Survey Questions marked up print out

Note that I have decided (in this case) that a person might respond with an answer that fits into two or three different categories.  I will write all the numbers on the print out.

This is what the yellow pad looks like after a while.

Tabulate Open Ended Survey Questions tabulated yellow sheet
Tabulate Open Ended Survey Questions Final result we wanted

Once you have tabulated all of the responses, you can sort them and if you want you can type them up.  Remember we started with this image:

That really all there is to it.  It is simple, relatively quick and you end up with useful information   I’ve always said that a box full of questionnaires is DATA.  A summary report on the data is INFORMATION.  Follow this system and you with have INFORMATION instead of page after page of DATA.

There is one last thing you might do.  We numbered the responses for three reasons.

  1. To tell which ones are done
  2. To let someone check over the process if they want or need to.
  3. To let us re-categorize.

Here is what I mean be re-categorize.  Sometimes you end up with one Category that has a huge percentage of the Responses and you realize that you could have broken the category into a couple of smaller categories.  Having a number beside each response allows you to go back through the printout and redo the category that is too broad.   For example, in my survey I used the word EVERYTHING to cover a lot of different ways that people said that they were just beginning and needed to learn it all.  If the EVERYTHING category had ended up with over 50% of the responses, I might have gone back and changed that to 1) Looking into Print on Demand and 2) Want to start and 3) Started and lost.

SUMMARY OF HOW TO Tabulate Open Ended Survey Questions 

  1. Create a printout of the open ended survey questions and responses you want to compile
  2. Number and write categories on a yellow pad
  3. Go though the printout, determine which category a response fall into and a) write the category number beside the response and b) add one to the count beside the category on the yellow pad and c) FLAG any surprising or very powerful responses.
  4. If a response doesn’t fit into any of the existing categories – create a new category.
  5. Re-categorize if necessary
  6. Pull out the FLAGGED responses for later use.
  7. Write up the final summary.  This will usually involve combining the categories that one had one or two responses.

Hope this help.

Until Next Time. Remember, You Are One Decision Away…! Oh Hale Yes
When you decide you need more information, check out the sidebar links —->>

Print Friendly, PDF & Email
About Temp Worker

Speak Your Mind

*

CommentLuv badge

This site uses Akismet to reduce spam. Learn how your comment data is processed.