How do I record a continuous scale on Excel?

In my last post, I have covered the basic mechanisms of creating survey scales and how to record them, starting with the ubiquitous Likert Scale. Today I will cover how to do Continuous Scales.

An example:

“How happy are you with this product?"

Can be captured by either a Likert or a Continuous scale. It is not my role to pontificate which of the two is preferable; suffice to say that a continuous scale can be broken down into categories if desired; a Likert scale cannot be built back into a continuous scale.

Excel offers the Scroll Bar control to capture this kind of data. It can be found, once again, in Developer --> Controls --> Insert:



Once selected, your mouse cursor turns into a cross. Simply drag it across your worksheet to produce a familiar-looking scroll bar. Re-size it by dragging its corners (Control-Click; release control; drag).

To record its value, select it, click in the formula bar, press "=", and click on your desired Target Reference (ie. where you want the recorded value to go - see my last post), and hit Enter.

You should notice that nothing happens - until you start dragging the scroll bar button (the thin little stripe between the two arrows - it doesn't have to be this thin, depends on how you size the scroll bar itself.) In the example below, I have set the Target Reference to H2 (or $H$2); and the scroll bar is moved to indicate 20.



Excel sets the following defaults:

• The maximum value (if I drag the scroll bar button all the way to the right) is 100;
• The minimum value (all the way to the left) is 0;
• The incremental change (each click of the arrow) is 1;
• The Page Change (if I click in the scroll bar, but not the scroll bar button) is 10.

What if I want a Scroll Bar that counts from 0 to 300 rather than 0 to 100? Easily done.

• Right click on Scroll Bar, bring up menu;
• Click "Format Control";
• Change the Minimum and Maximum values as you see fit.
• (You can also change the other values, if you're game to experiment.)

Do a few formatting changes, and you have a decent-looking continuous scale:



This scale shows that I'm 71% confident that my paper will be published. You can't see the recorded value, because it is in a different sheet called "Data" (recorded in Cell A2) - the formula bar tells you that.

Just like the Likert Scale, rarely would a survey consist of one question and one only. For multiple questions, you need multiple scales; just copy and paste the original scroll bar. As with Likert Scales, you will need to define the Target Reference for each scale.

Just a few Best Practice comments:

• Be mindful of the default value of the scale when subjects first see it. If you leave the scale at either extreme (0 or 100), it may create anchoring effects, particularly if your scale has two opposite anchoring points (eg. "Strong agree" vs. "Strongly disagree")

This brings anchoring effects galore!

• You can eliminate anchoring effects by setting the scale to its middle value (50 if the scale is 0 to 100), by changing its Target Reference to 50. Yes, just as moving the scale would move the Target Reference value, changing the Target Reference value would move the scale too.

That's better.

Stay a step ahead,
Alex.