How do I record a Likert scale using Excel?

I've spent the last two posts harping on about the advantages of doing surveys and experiments using Excel and VBA. It might be time to actually get into it.

Your typical social science survey usually employ more than one of the following response modes:

• Likert scale
  
• Continuous scale measure
• Free response
• Multiple choice response (where you can only choose one of several options)
• Multiple options (where you can choose more than one of several options)

I'm not going into what you should use for your study. I'm just going to show you how each one can be easily presented and captured using Excel in the following entries.

This blog post will tell you:

1. How to create a Likert scale and Multiple Choice Response item.
2. How to record a response.

Likert Scale/ Multiple Choice

Likert Scale is probably the most popular and widely used instrument to investigate attitudes and beliefs. Whether it be 3-, 7- or 99-point scale, Excel can handle the presentation and collection of this scale easily. (Although if you have a 99-point scale, you might as well use a continuous scale measure, or risk a stern question from your Reviewer.)

I have put Multiple Choice Questions (MCQ) in the same category because, whilst having distinctly different purposes and theoretical underpinning from Likert Scales (choices in a Likert are usually of an ordinal nature; MCQs can be nominal), their functionalities on Excel are virtually identical.

What you need is something called "Radio Buttons". Open your spreadsheet; go to the "Developer" tab, go to "Control" group, choose "Insert", and choose the circle on the far right, top row.

(As I mentioned previously, I primarily use Excel 2007. If you've always used Excel 2003, this image won't make sense to you. I will provide Excel 2003 references when possible.)

(You can get the Radio Button in Excel 2003 by right-clicking on the toolbar, choose "Controls", and find the button with the same shape.)

Once you've chosen the option, your mouse cursor should turn into a cross; click anywhere on the spreadsheet, and you should get this:

You can change the words "Option Button 1" to whatever you want.

Now, having just one option isn't enough. Say we want a 7-point scale. All you have to do is copy and paste the first one that you created six times:

1. Hold down Control, and click on the button.
2. Release Control, then right click on the shady border.
3. Choose "Copy"
4. Click anywhere else on the sheet; right-click; and choose "Paste"
5. Repeat 1-5 until you have enough buttons.

You might find that your buttons are scattered everywhere on the sheet. Drag them and line them up in your preferred manner (by holding Control --> Click the button; drag the button by its shady border.) Here, the gridlines on your sheet are your friend. When you drag the button, you can see a rectangular box lined by dotted lines following your mouse (called a Placeholder). Line this box up with the gridlines for a more consistent look.

Once you have them lined up, you have to tell the program that they belong to the same question - that is, subjects cannot select more than one option out of that group.

Go to "Developer" tab, under "Controls" group, click "Insert", choose "Group Box".

Notice that your mouse cursor has turned into a cross. Drag to draw a rectangle around the 7 Radio Buttons that you've just drawn.

Now, the program knows that only one of these 7 points can be chosen.

You can also notice that, in the above diagram, there isn't quite enough space for the middle option "Neither agree nor disagree" (you can't see the word "disagree"). You can change the size of the Radio Button by selecting the button (hold Control, Click it), and drag it by its corner (the small white dots) until it gives you a good size.

Finally, one more thing. How do I record the responses?

1. Select any one of the radio buttons (yes, you guessed, hold Control, Click on button).
2. Click in the Formula Bar, and press = (equal sign)
   
3. Click on the cell that you want the data recorded (this is known as the "Target Reference"); in this case, I chose J3.
   
4. Hit Enter.

You should see the number 3 in Cell J3. When you click on other radio buttons in this group, you will see the number change - precisely, 1 if you choose "Strongly agree", 7 for "strongly disagree", and so on.

Just a few final words of advice:

1. In this example, I made the Target Reference in the same sheet as the buttons for illustrative purposes. In an experimental situation, you'd much rather have the Target Reference in a different sheet, to hide it from subjects.
2. Don't be too concerned by the $-sign that goes with the cell reference ($J$3). It means exactly the same as J3; and you can change it to $J$4, $K$3, manually, if you want.
3. This whole post talks about just creating one item. You need a different Target Reference for each item - best lined up in a logical way so that your data can be easily retrieved.

In my next post I will talk about creating other scales - but the basics of creating and recording references have basically been covered here.

Stay a step ahead,
Alex.

Why bother?

Why bother with Excel?

I mean, what's the point of doing all this programming crap? Why don't I just print a bunch of surveys, let my subjects fill them out, then I'll enter and code them?

Well, you can. But a bit of work now saves you lots of time later. And once you become familiar with the basics, not even the initial work will daunt you.

I have three main reasons why I prefer running surveys on Excel VBA:

(1) Control

The most beautiful thing about using VBA is the control you can have over your study.

My doctoral study involved two parts, first a task which contains the experimental manipulation, then the dependent response. As a general rule, I cannot let subjects see the dependent response until they have gone through the manipulation. I usually have 10 subjects per session. Now, no two people would ever complete the manipulation task at the same time, much less 10.

Had I used paper-and-pencil method, I would have to check on everyone's progress, hand out the dependent response when someone's finished, record their ID number (to make sure the two tasks match each other), put the papers into a sensible pile, etc etc. If 2-3 subjects finish the manipulation at the same time, I'd be stuffed. If one more ask me a curly question or two, the whole session would be in chaos.

What made it worse was that part of my manipulation task consisted of a number of survey questions - 180 of them to be precise. They all need to be counterbalanced. I wasn't going to print 180 different versions just for the sake of counterbalancing!

And of course, the keystone of experimental research - random assignment. I ran a 2x4 design, and for each condition, I had to hand out the manipulation and dependent measures in a different order. Maybe my memory is fish-like, but it's a pain remembering who is supposed to be in which condition! Wish I could have just pressed a button on the desk and all the right manipulation would just magically appear! ... wait, you can.

Excel VBA lets you control how your information is presented. Don't let subjects see certain material prematurely? Sure. Randomise a bunch of material? Try a few thousands of them. And response-dependent presentation of stimulus? A breeze.

Experimental studies demand control. That's what Excel VBA can do for you.

(2) Adaptability

Okay, I printed my aforementioned 180 versions of the same survey for my anticipated 400 subjects. My supervisor rings me up, "Alex, don't use the word 'satisfaction', say 'happy' in your survey". Oops. Early morning dash to the Faculty, change the survey (all 180 versions of them), frentic printing, feeling guilty for the trees as you pulp the 400 surveys...

With a well-designed VBA program, you can change everything with just a few clicks. You can still enjoy your 7am coffee at the coffee shop rather than frentically printing.

You might wish to run follow-up studies. Rather than ruining a few more soccer fields worth of trees, you can make easy minor adjustments. Or major ones.

(3) Speed

Surveys and experiments are time-consuming processes. Anything that helps you save time during and after studies are a beautiful thing.

I've lost countless hours just inputting and coding survey responses into Excel. 7-point Likert scales I can deal with; what about continuous scales where subjects just put a dash on a line? The ruler and I became unlikely companions for some long evenings, converting millimetres into numbers, and into Excel.

Why not just record everything on Excel in the first place?!

Excel VBA not only helps you achieve quick data coding, but also enhances data collection speed. You can give the same program to as many subjects as you want, simultaneously. Why be stuck with sessions with only 4-5 respondents when you can hold 30-40 of them at once? Could save you days, even weeks, in data collection time. Spend that time do some useful work that could get you an extra publication, get noticed by the boss, play some golf - anything beats data-collection and data-entry.

My next blurb will be more useful and less lyrical in compliments, hopefully.

Stay a step ahead,
Alex.

The Mandatory Basics

The maiden post of almost every blog seems to require a mandatory blurb with a similar introductory tone. If you are after a break from convention, you are about to be disappointed.

I am a marketing/psychology researcher who happens to know a bit about Microsoft Excel, the Glorified Calculator; and its companion event-driven programming language, Visual Basic for Applications (VBA).

(I will not even attempt to explain what "Event-driven programming" is. The Wikipedia link should be sufficient explanation for now; and in the greater scheme of things, these technical definitions are not too important. I'm sure your Reviewer won't ask what a Call Procedure is - if they do, give them my number, I want to get sloshed with them.)

During the last six or so years (3.5 of which masquerading as a marketing doctoral candidate), Excel VBA has saved me countless hours in data collection and coding. In fact, given that my studies always involved manipulation, randomisation and counter-balancing, I couldn't have done it without some form of automatic assistance. Guess I could have hired 20 research assistants to help counter-balance my stimulus material, but then I'd be more broke than I already am now.

Enough spruiking; here are a few preambles that might help you decide whether this blog is for you, or whether another Youtube video will be more interesting:

• I'm far from an expert in the art of VBA programming. Therefore this blog will not be a definitive guide to your programming problems - it's not intended to be. This blog will specifically focus on how VBA can help you do sound experimental/survey research. Therefore, every idea here will be driven by research principles; technical sophistication comes second.
  
• Since this is not intended to be a comprehensive VBA guide, I may introduce concepts that have not been covered elsewhere in the blog. For instance, I won't give a detailed explanation of what objects, procedures and methods are. Punch "Excel VBA basics" into Google, or get a VBA for Dummies book, they will give you much better explanations - but they won't be as research-focused as you'd like them to be.
  
• Technical standards. Every time I refer to codes, I will write in fixed-length font:

Sub ThisIsATestCode ()
Sheets("sheet1").Visible = True
Msgbox "hello!"
End Sub

Examples used here will refer to Excel 2007. There are some VBA commands in Excel 2007 that are not available for 2003; I will avoid using them; or if absolutely necessary, give you codes for both versions. People say Excel 2007 has a painful interface with its Ribbon and crap...I find its VBA changes even more annoying.

Still with me? Good. Hopefully we can all move forward and do some good for the world. Failing that, a JCR will do.

Stay a step ahead,
Alex.