To Use This Code Yourself

  1. With Excel open, press Alt+F11 to open the VBA Editor.
  2. Copy/paste the code block above into the VBA Editor.
  3. Close the VBA Editor (return to Excel).
  4. In an empty cell, simply type =LONGSTRING() and put the cell range of your scale’s survey items inside.  For example, if your first scale was between B2 and G2, you’d use =LONGSTRING(B2:G2)
  5. Repeat this for each scale you’ve used.  For example, if you measured five personality dimensions, you’d have five longstrings calculated.
  6. Finally, in a new cell use the =MAX() function to determine the largest of that set.  For example, if you put your five LongStrings in H2 to L2, you’d use =MAX(H2:L2)
That’s it! Importantly, the cells needs to be in Excel in the order they were administered.  If you used randomly ordered items, this adds an additional layer of complexity, because you’ll need to recreate the original order for each participant first before you can apply LongString. That takes a bit of Excel magic, but if you need to do this, I recommend you read up on =INDIRECT() and =OFFSET(), which will help you get that original order back, assuming you saved that item order somewhere.

Final Steps

Once you have Max LongString calculated for each participant, create a histogram of those values to see if any strange outliers appear. If you see clear outliers (i.e., a cluster of very high Max LongString values off by itself, away from the main distribution), congratulations, because it’s obvious which cases you should drop.   If you don’t see clear outliers, then it’s probably safer to ignore LongString for this analysis.
  1. Meade AW, & Craig SB (2012). Identifying careless responses in survey data. Psychological Methods, 17 (3), 437-55 PMID: 22506584 [