Co-occurrence matrix

Welcome UX-Radio and LA UX visitors!

A co-occurrence matrix allows you to analyze how often any pair of cards in a card sort were sorted into the same group. I’ve developed co-occurrence matrix spreadsheets that work with Donna Spencer’s card sort analysis spreadsheets. This page contains links to two versions of the matrix spreadsheets, and instructions for choosing the correct version to use, and for using them with data you have in Donna’s spreadsheet. You can read the original announcement post if you like. The instructions on this page and the spreadsheets are completely free for you to use however you wish.

If you’re new to card sorting, check out this excellent article, or Donna’s excellent book.

Choosing which version of the co-occurrence matrix spreadsheet to use

The co-occurrence matrix spreadsheets work with Donna Spencer’s card sort analysis spreadsheets by linking to the named lists and worksheets in them. Donna has supplied two versions of the card sort analysis spreadsheets: one for up to 20 sorters and 200 cards, and one up to 40 sorters and 400 cards. The co-occurrence matrix spreadsheets also come in two versions, 20×200, and 40×400.

You should use the co-occurrence matrix sheet that corresponds to the version of Donna’s spreadsheet that you’re using. There are three reasons for this:

  • First, the 400×40 spreadsheet contains over 3 million cells with formulas. That’s over 70 times as many formulas as in the 20×200 spreadsheet, and consequently takes much longer to open, and to update.
  • Second, the 40×400 spreadsheet doesn’t cope with data from Donna’s spreadsheet for 20 sorters.
  • Third, since the 40×400 spreadsheet is much larger than the 20×200 version, you’ll download faster and be kinder to my server by taking what you need.


For use with Donna’s template for 20 participants:

For use with Donna’s template for 40 participants:

Using the matrix

When opening the matrix for the first time, Excel may prompt you to update the linked data. Don’t bother. It’s linked to a file on my hard drive anyway and you’ll be linking to your own data soon.

Linking the matrix to your data

  1. Open the Edit Links window. Depending on your version of Excel, how you get to the Edit Links window may vary. Here are examples from Excel 2003 and Excel 2007, both for Windows 7:
    Open the Edit Links window by selecting Edit > Links in Excel 2003.
    Select Edit > Links (Excel 2003)
    Edit Links in the Connections area of the Data ribbon of Excel 2007
    Edit Links (Excel 2007)
  2. In the Edit Links window, select Change Source and choose the card sort analysis spreadsheet you want to link to.
    Change Source button highlighted in the Edit Links window

After a few seconds or a few minutes, your data will be displayed in the matrix.

Reading and navigating the matrix

The Matrix worksheet is the first tab. All the other worksheets are used to collect the card names and the linked data of individual participants.

The 20x200 matrix open to the Matrix worksheet

In an ordinary matrix, data would be duplicated – mirrored across a diagonal line where the cards would intersect with their duplicate card on the other axis. This diagonal line would also present useless data, showing that a card is always grouped with itself. As is typical for a co-occurrence matrix used for card sorting, I’ve eliminated the duplicate data.

Results for one card follow the shape of an L rotated 180 degrees. Each cell shows the percentage of sorters who grouped the two cards intersecting at that cell. Higher percentages are shaded with darker colors.

I put the labels for the horizontal axis along the stair-step edge of the matrix, and duplicated those labels in the top row of the worksheet. The duplicate labels allow for easier navigation while viewing the matrix in Excel. To put the horizontal labels in their own pane, drag the small rectangular control that you find at the top of the vertical scroll bar.

Saving your matrix

To preserve your matrix, you should break the link between the matrix and its data. The formulas that link to the external worksheet will be replaced permanently with their values.

  1. If you haven’t already, use Save As to save a copy of your matrix using a different name.
  2. Open the Edit Links window that you used earlier. (Excel 2003, Edit > Links; Excel 2007, Data ribbon)
  3. Choose Break Link.
    The Edit Links window with the Break Link button highlighted.
  4. Save your matrix worksheet.

Printing the matrix

If you want to print the matrix, you may find it useful to repeat the labels on each page. You can do this, and set the print area to exclude cells that extend beyond your sort data, in the  Page Setup window. In Excel 2003, select File > Page Setup. In Excel 2007, you can set these options in the Page Setup area of the Page Layout ribbon.

Known limitations

  • The formulas assume you number your cards sequentially starting at 1. This is used to automatically calculate the number of sorters. If this is a problem, simply enter the number of sorters in cell A1 of the Matrix worksheet. (Column A is hidden.) I haven’t tested whether it copes with gaps in the card number sequence, but I think it will.
  • The cards are always presented in sequential order. There is no automatic clustering or sorting of results to present cards with high co-occurrence on adjacent rows or columns.
  • The 40×400 version can take a minute or so to open, and few minutes to update when linking new data. There are over 3,000,000 cells with formulas in that version. Be patient. The 20×200 has around 42,000 cells with formulas and consequently is much faster. I recommend the smaller one if it meets your needs.

Leave a Reply

Your email address will not be published. Required fields are marked *