How to Tabulate Survey?

sammipd · Oct 21, 2011

I’ve been given paper copies of completed 20 question surveys to tabulate. Some the questions are simple Yes No answers, others have multiple checkboxes selected, others are text. What is the best way to set this up in Excel? I can analyze using countif and pivot tables, but can’t figure how to set up the data.

I’m sure Access would be better for this, but I haven’t time to relearn it.

I’m using Excel 2007. Thanks for your help!

Smitty · Oct 21, 2011

Welcome to the Board!

It'd probably be easiest to set up a survey on SurveyMonkey, fill in the 20 completed surveys and let the SurveyMonkey software tabulate it for you. It's free too.

sammipd · Oct 21, 2011

I actually thought of SurveyMonkey, but I can't do that with this survey.

I guess I wasn't clear. The survey consists of 20 questions. 20 didn't refer to the number of completed surveys.

Tanks!

Smitty · Oct 21, 2011

If they're completed and in hard copy form, then no matter what you do you have your work cut out for you.

I suppose you could set up a few different tabulation methods, one for Yes/No, one for the multiple choice, then manually compile the text entries. Either way it sounds like you'll need to go through and manually tabulate everything.

sammipd · Oct 21, 2011

If I convert the multiple response questions to a Yes/No format, it should work? Columns would be set as #1a, #1b, #1c... with Y or N entered. Other than resulting in miles of columns, what would be the downside?

ex: #1. Name 3 fav colors
a blue
b red
c green
d yellow
e purple

The text responses will have to be done manually. I'm just looking for some method to automate the rest.

Smitty · Oct 21, 2011

Here's a possiblity. If you set up the questions in a tally format, you can use some code to make your entries with the BeforeDoubleClick event.

Excel 2010<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">1. Favorite Colors</td><td style="text-align: center;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Count</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Blue</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Red</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Green</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Yellow</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Purple</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td></tr></tbody></table>Sheet1 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) ' Code goes in the Worksheet specific module Dim rng As Range ' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3") Set rng = Target.Parent.Range("B:B") ' Only look at single cell changes If Target.Count > 1 Then Exit Sub ' Only look at that range If Intersect(Target, rng) Is Nothing Then Exit Sub ' Action if Condition(s) are met (do your thing here...) If Target.Value <> "Count" Then Target.Value = Target.Value + 1 Cancel = True End If End Sub

When you double-click on a cell in the Tally range it will automatically increment by one.

HTH,

sammipd · Oct 21, 2011

Thank you for the help. I tried your code, but I'm VBA-challenged. I think I'll do it using miles of columns. It's slow but I understand it.

Learning VBA is on my bucket list. I'll check into local classes, or can you recommend a source for an online course?

Smitty · Oct 21, 2011

The code needs to go in the worksheet specific module. Just right-click the sheet tab and select "View code" and paste the code in that module. You'll need to delete it from any general modules you might have placed it in though.

As for tutorials, you can do a Google search for "Free Excel VBA tutorials", which will turn up some good ones. Martin Green has some good stuff here: www.fontstuff.com

How to Tabulate Survey?

sammipd

Board Regular

Excel Facts

Smitty

Legend

sammipd

Board Regular

Smitty

Legend

sammipd

Board Regular

Smitty

Legend

sammipd

Board Regular

Smitty

Legend

Similar threads

Forum statistics

Share this page

How to Tabulate Survey?

Board Regular

Excel Facts

Legend

Board Regular

Legend

Board Regular

Legend

Board Regular

Legend

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock