Analyzing Card Sort Results with a Spreadsheet Template - Boxes and Arrows: The design behind the design

Analyzing Card Sort Results with a Spreadsheet Template

byJoe Lamantia and Joe Lamantiaon 2003/08/26 | [22 Comments]

The template provides visually attractive results showing:

• In which categories each card appears
• How often a card appears in any given category
• Where cards appear by percentage
• The number of unique cards in a category
• Color coding to simplify interpretation
• Summaries of category contents

You can see a preview of the results, using sample data, at right.

Process and time requirements are minimal — all you need to do ispaste your lists of cards and categories into the worksheet, enter yourdata, and tweak a few formatting rules.

The template was developed as an analysis tool for interpreting theresults of a card-sort exercise involving more than 100 items andapproximately fifteen participants. The best known freely availableautomated card sorting tools (EZSort and WebCAT, for example) cannotwork with item sets of this size. This template will accommodate 180cards and 30 categories, but is easy to expand further via simplecutting and pasting of the cell references and formulae.

Before You Begin
Create and conductyour card sort, using numbered cards with a label and a shortdescription. More information on how to complete a card sort isavailable in the references section at the end of this article.

To help you get started quickly, I’ve provided a spreadsheet template, with examples, which you can download here.

Preparing Your Spreadsheet
Your first task is to alter the template to reflect the number of cards in your sort.

Step 1: Open the Excel file and click the tab at the bottom of the window for the worksheet called Initial Card Count.

Step 2: Cut and paste an alphabetical list of the titles of your cards into the column labeled Card Title (column A).

The template includes a column for the card numbers (column B),which you may need for later reference. I recommend including thedescriptions that appeared on your cards in the worksheets, as it ismuch easier for people who did not directly conduct the study to readand interpret the results when they have the original card descriptionreadily at hand. I usually place the descriptions in a new column(column C, in this layout) adjacent to the card numbers; you can seethis on the last worksheet, “Summary.”

Step 3: Paste this same list of card titles into column A ofthe other three worksheets: “Low & High Card Count,” “Card PlacementPercentage,” and “Summary.”

(I prefer to color code the card names on each worksheet accordingto different schemes, so I do not link the cells to the list on thefirst worksheet.)

Don’t worry about customizing the spreadsheet for your particularanalysis needs just yet; it’s easier to adjust the formulas andformatting logic for the worksheets after you’ve seen the results andfeel comfortable with what they’re telling you. There’s more on this inthe “Reading Your Results” section, and a complete list of the variousformulas used in the worksheets in the “Formula Reference” sectionsbelow.

Standardizing Categories for Analysis
Ifyour card sort uses predetermined categories (that is, if it’s a closedsort), cut and paste the names of your categories into row 1 of the“Raw Data” worksheet; the template will automatically transfer thesecategory names into the appropriate cells of the other worksheets.

In an open sort you’ll have to standardize the categories yourparticipants create in order to effectively compare the placement ofyour cards (unless, of course, all the participants create exactly thesame set of categories). This step is very important but tricky, sotake care to think about the implications of ostensibly easy decisions.

Here are three quick steps to help you arrive at a list of standardized categories:

Step 1: Sort the category names your participants createdinto an alphabetical list. Strip any common prefixes (e.g., yourcompany’s name, the word “our”) from the user-created categories toexpose the underlying topic or subject of the category.

Step 2: Scan the list for groups of category names that aresimilar. Common methods of gauging similarity are by root word (noun orverb), by word order, or by meaning. Use the example below forreference. Combine categories with similar names into clusters, andchoose a representative label for each cluster.

Step 3: Review the remaining user-created categories,searching for common synonyms of the cluster labels (e.g., “employment”and “careers”). Add these to the initial clusters.

Here’s an example of how a list of raw categories might look when clustered:

Step 4: Add any remaining categories from the original list of raw categories to the labeled clusters you’ve created.

You now have a set of standardized categories to use in comparingcard placement and category strength. Additionally, this list now mapsuser-created categories to the standard categories you’re using foranalysis. Make sure to save it, as you’ll need it for reference whenentering data into the spreadsheet.

Some categories in the raw list will be unique, or won’t easily fitinto an existing cluster. Unless you’re left with a large number (halfor more of the total) of unique categories in the raw list, don’t worry.If you do find a large number of unique raw categories, it’s possiblethat your participants have widely different models of how to organizethe content, which means that you’ll need to rethink the starting set ofitems, or your approach, or both.

Step 5: Cut and paste the final list of standardizedcategories (Edit > Paste Special, and check the box for “transpose”)across the Row 1 of the Raw Data worksheet.

I like to color the cell backgrounds to make it easier to tell them apart when I’m entering the raw data.

Entering the Raw Data
Now for the fun part…

Step One: Take a single group of sorted cards (cards in oneraw category) from the collection of groups created by one participant,and match this raw category with the corresponding standardized categoryyou chose earlier. You will likely need to refer to the document thatmaps the user-created raw categories to your standardized categories toensure that you’re entering the raw card data correctly.

Step Two: Record the individual card numbers of the cards inthe participant’s raw category in the matching column for yourstandardized category in the “Raw Data” worksheet, entering only onecard number into each cell in the column. Complete this process for oneraw category at a time, until you’ve recorded the locations of all thecards in all the raw categories for one participant.

Step Three: Moving through the collections of sorted cardsfrom each participant, enter the card numbers from the raw categoriesthey created into the columns for your corresponding standardizedcategories on the “Raw Data” worksheet.

The template accommodates up to 250 entries in any one standardizedcategory. Should you find that any of your standardized categories endup including more than 250 entries, you will need to alter the formulasin the spreadsheets to count the values from cells with row numbersgreater than 250. This is explained further in the Formula ReferenceSection below.

Here’s a look at the Raw Data worksheet, showing sample categories and card data:

(Click to enlarge.)

When you finish, you’ll see that the other worksheets are populated with values, and some cells are in color.

Reading Your Results and Customizing the Worksheets
Onceyou’ve completed the data entry, you can immediately review yourresults. The template uses Excel’s Conditional Formatting features tocolor and highlight cells with specific values, so you will probablyneed to make some simple changes to the logic that drives the formattingto accurately reflect the number of participants in your card sort, andthe way that you wish to see results displayed.

You can change the conditional formatting settings in any worksheet by:

1. selecting a block of cells in a worksheet
2. choosing the “Conditional Formatting” option from the Format menu

You will then see a dialog box that shows up to three conditionsdefined by simple Boolean operators and numeric values, each accompaniedby a combination of formatting guidelines (font, color, background).

Conditional Formatting dialog.

In this example, taken from a cell in the “Card PlacementPercentage” worksheet, you can see three conditions defined in theformatting dialog box. The first condition sets the text color in allcells with a value of zero to white, which is an easy way to makeresults easier to read by cutting down on the number of cells that havevisible results. The second condition sets the background color ofcells with values between .01 and .34 to yellow, and the text color to astandard black. Since the values in these cells correspond topercentages, this condition makes it easy to spot cells with values inthe bottom third of the results. The third condition sets thebackground color of cells with values between .66 and 1 to green, andthe text color to black; this highlights cells with values in the topthird of the results.

Now let’s take a look at the other worksheets.

Worksheet 1 – Raw Data
This worksheet contains the sourcedata used to drive the values calculated on each subsequent worksheet inthe template. It contains no formulas and no formatting.

Worksheet 2 – Initial Card Count
The Initial Card Countworksheet shows how often each card appears in each category. This isuseful, but can be hard to digest, especially with a large set of cardsand categories.

Here’s the Initial Card Count worksheet showing sample data:

(Click to enlarge.)

Worksheet 3 – Low & High Card Count
The Low & HighCard Count worksheet shows the same numerical data, but employs somebasic formatting features to highlight areas of interest and make theresults easier to read.

Here’s the Low & High Card Count worksheet showing sample data:

(Click to enlarge.)

Numbers are only visible in cells with card counts of one or higher.Using Conditional Formatting, I’ve specified rules that color thebackgrounds of cells with card counts in the lowest third (light yellow)and highest third (light green) of the results to make them easier topick out. (You might choose different color combinations to suit yourneeds and circumstances.) The rationale behind this is that a highoccurrence count in a single category indicates clear agreement amongstusers on where a card belongs, whereas a low count indicates a card thatfew agree on.

You can change the thresholds for the formatting by opening theConditional Formatting dialog box for any of the cells and altering thenumeric values listed in the conditions to reflect your parameters.

When you decide what rules to employ, remember that the total numberof participants determines the thresholds for each segment of theresults. The template assumes you have data from nine participants andwish to highlight what are roughly the highest and lowest thirds of theresults. Accordingly, the conditional formatting rules come into effectfor values of three or less, and six or greater.

At the bottom of the Low & High Card Count worksheet, you’ll find summary rows that show:

• the total number of cards per category
• the number of different cards per category
• the ratio of these two measurements

I manually color-code the top five values in each summary row, againto make them easy to call out. While this isn’t a solid statisticalassessment of the data, it does give you an easy way to rapidly identifywhich categories are broadly and tightly defined, and to make somerough comparisons between them.

Categories with a high ratio of total cards to different cardsinclude many repeated cards. This indicates that your participantsagreed often on the placement of those repeated cards within this singlecategory. Compare the ratio to the number of participants in yoursort. When these two numbers are close it’s more likely that thiscategory is strongly and consistently defined in the minds ofparticipants.

Categories with a low ratio of total cards to different cards do notinclude as many repeated placements of cards, which indicates that yourparticipants agreed less often on which cards belong in this category.

The real meaning of these numbers lies entirely in their context, solet’s use a simple example to illustrate some of the conclusions youmight derive from reviewing the results on this worksheet:

Say that you ran a sort with 100 different cards; 30 of these cardsincluded the names of songs written by Miles Davis, and the other 70included a mixture of jazz songs by seven or eight other artists, thenames of important jazz ensembles, musical styles related to jazz,famous jazz and blues albums, etc.

If one of the standardized categories created during this sort was“Songs Written by Miles Davis,” you would expect it to containapproximately thirty different cards (one for each of the songs). Thetotal number of cards in this category should be close to 300 — tenrepeat placements of each of the thirty different cards for songs byMiles — and the ratio of total cards to different cards would beapproximately 10 to 1, matching nicely with the number of participants.Even though this category contains nearly 30 percent of the originalunsorted items, it makes sense in this context.

If half of the participants chose to group songs by their openingkey, and not by composer, you might see ratios much lower than 10,meaning that fewer participants agreed on how to create their categorystructures.

Worksheet 4 – Card Placement Percentage
The Card PlacementPercentage worksheet lets you quickly assess the percentiledistribution of placements for any card in relation to one another.Reading from left to right along a single row, you’ll see percentagesthat represent the placement of each card in each standardized categoryas a percentage of all of the different placements for that card. Highpercentages indicate that more participants consistently placed thatcard in that category; naturally, the highest percentage is 100. Irefer to these percentages as the level of participant “agreement” onthe placement of the cards.

CardPlacement Percentage worksheet. (Click to enlarge.)In the screenshot atleft, look across the row for card #124. You’ll see that it appearedin Category 1 (column AC) 50% of the time, and in Category 3 (column AE)only 13% of the time. By contrast, looking at the categories wherecard #147 was placed, you’ll see that it appeared in Category 2 (columnAD) 83% of the time. Clearly, a significant majority of participantsagreed that this card belongs in Category 2 (column AD).

The far right columns of the Card Placement Percentage worksheetshow the number of different categories each card appeared in across allof the results sets, as well as the average of all the percentagevalues. In these summary columns, conditional formatting highlightscards that appear in a large number of different categories (in thiscase, six or more, which appear in red), and those that appear in onlytwo categories (in tan). Again, the rationale is to identify items thatrequire immediate attention, or that offer ready opportunities forredefinition.

At the bottom of the Card Placement Percentage worksheet, summary rows show:

• how many high-agreement cards appear in each category
• how many medium-agreement cards appear in each category
• how many low-agreement cards appear in each category
• the average-agreement index of all cards in the category

These are additional indicators of the strength of a category, andthe way that cards were distributed across the different categories. Ioften manually color the top five results to allow faster spotting ofstrong and weak categories.

The Card Placement Percentage worksheet, showing category summaries:

(Click to enlarge.)

Note: Remember: The template includes a hidden row (row 183,labeled “# zero % cards”), used as a shortcut to calculate the values inthe three rows below. Be careful not to accidentally delete this row.

Worksheet 5 – Summary
The Summary worksheet shows thesummary columns from the far right of the Card Placement Percentageworksheet directly adjacent to the column containing the description ofeach card. It also includes suggested columns to use in comparing thecurrent category location of each card with the location you understandthe participants to prefer, and tracking recommendations and labelingchanges for each card.

I use this display for presentations with clients, developers, andbusiness owners who need abbreviated results, rather than the exhaustivedetail of the other worksheets.

Interpreting the Results
Interpretingthe results of a card sort depends largely on the context of theexercise: what items you included, who participated, and what questionsyou hoped to answer or identify will all be important in shaping whatyou derive from the analysis. The strength of this tool is that itsupports pattern analysis at more than one level: you can investigateindividual cards, whole categories, and even — if you’ve defined them inadvance — groups of cards and groups of categories.

If you’re using a card sort to drive the design of a new informationarchitecture for an existing resource (perhaps navigation for awebsite), comparing the current location of items that fall into thelowest and highest results groupings with their user-preferred locationscould indicate problems that require immediate attention or offer thegreatest opportunity for improvement.

Categories that include mostly high-agreement cards and fewlow-agreement cards are probably well understood in the minds of theparticipants, and represent structures you’ll probably want toaccommodate in your information architecture.

Categories with many low agreement cards may indicate thatparticipants were looking for a place for items they do not value orunderstand. Or it may mean that the labeling and content of the items isinconsistent, and users couldn’t find a location that suited both thecard name and description.

Autofilters
Using Excel’sAutofilters (available under the “Data” menu via Data > Filter >Autofilter), you can display specific combinations of cards andcategories to speed interpretation of the numeric results — for example,cards that participants placed in many categories, which indicateconflict about the cards’ labeling and description.

This is the Autofilter Dialog box:

You can zoom in to display all the cards in a single category by percentage:

(Click to enlarge.)
Or by count:

(Click to enlarge.)

Or all the cards that appear in more than X number of categories:

(Click to enlarge.)

While it is possible to use more specialized features in spreadsheettools like Excel, or even dedicated statistical analysis packages, timeand resource constraints make this a practical alternative for quicklyderiving insight from the results of this common but usuallylabor-intensive user research technique.

Formula Reference
For easy referencewhile you prepare your own spreadsheet, this section collects all theformulas from the worksheets in the template. Each example formula isfrom the cells corresponding to the last card in the last category inthe template. Most of the formulas are composed of simple functionslike summing a range of numbers, comparing one value to another, orincrementing a counter to reach a total. Even if you’re not an Excelpower user, if you know just a little bit about how to use Excel’sformulas (which is certainly the case for me), you should be able tochange these as necessary to suit your specific needs.

Here are a few tips on what to look for and expect when customizing the template:

• Adjust the template to fit the number of cards in your sort and the number of final categories by cutting and pasting whole rows and whole columns; Excel will update formulas and references automatically to reflect your changes.
• The Initial Card Count worksheet counts up to 250 total cards in any one category on the Raw Data worksheet. If you end up with more (!) than 250 cards in any one category, you’ll have to increase the value in the Main cell formula (see below) to make the template count all the cards.
• Several formulas reference other worksheets; if you change the names of any of the worksheets in this template, most versions of Excel will automatically update all of the affected formulas.

Worksheet 1 – “Initial Card Count” Formulas
Main cell formula
=COUNTIF

# Cards Per Category
=SUM

Worksheet 2 – “Low & High Card Count” Formulas
Total # Cards Per Category
=SUM

# Different Cards Per Category
=COUNTIF

Ratio # Total Cards: # Different Cards
=(AF180/AF182)

Worksheet 3 – “Card Placement Percentage” Formulas
Main cell formula
=’Initial Card Count’!C2/MAX, 1)

# Categories With This Card
=COUNTIF

Average Agreement Per Category
=SUM/AH178

# Different Cards
=COUNTIF

# zero % cards
=COUNTIF

# High Agreement Cards
=COUNTIF

# Medium Agreement Cards
=AF182-SUM

# Low Agreement Cards
=AF186-AF183

Average Card Agreement
=SUM/’Low & High Card Count’!AF182

Worksheet 4 – “Summary” Formulas
# Categories With This Card
=COUNTIF

Average Agreement Per Category
=SUM/D178

Information & Design — a usability consultancy in Australia — provides a brief and useful overview of how to conduct a card sort here: http://www.infodesign.com.au/usabilityresources/design/cardsorting.asp

UsabilityNet.org discusses card sorting by placing it in context of the broader landscape of user research and usability tools here:
http://www.hostserver150.com/usabilit/tools/cardsorting.htm