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.