https://userresearch.blog.gov.uk/2019/09/13/how-a-spreadsheet-can-make-usability-analysis-faster-and-easier/

How a spreadsheet can make usability analysis faster and easier

Analysing usability notes can be difficult and time consuming

Affinity mapping is usually the technique researchers use when it’s time to analyse the research and create insights. It’s the main technique we reference in the Service Manual. It can be great for involving the team and giving everyone a shared understanding of issues.

There are pros and cons with any technique. When doing usability tests, affinity mapping can be time consuming and it can be easy to think findings are significant because lots of people made a note of something even if it doesn’t matter in the user journey. The results can be influenced by the number of notes instead of the significance of the issue.

The ‘Rainbow Spreadsheet’ method

The ‘Rainbow Spreadsheet’ method uses a spreadsheet to record whether participants acted in the way your team expected. It provides a structure for notetakers to record what participants did during the test as opposed to asking. 

The spreadsheet has the following columns:

  • Task 
  • Assumption
  • One column per participant (i.e. P1, P2)
  • Notes, for observers to record extra detail

GDS has shared a template so that anyone can look at this spreadsheet and make their own copy.

The participants column in this example uses two colours green for yes and red for no, making it easy to visualise summarise which participants validated the teams assumptions and which did not.

The common columns of a rainbow spreadsheet include: Task, Assumption, as many participant columns as needed, a notes column for observation of participants behaviour or direct quotes. The participants column in this example uses two colours green for yes and red for no, making it easy to visualise summarise which participants validated the teams assumptions and which did not
Image of what my spreadsheet looks like

The common columns of a rainbow spreadsheet include:

  • Task 
  • Assumption 
  • As many participant columns as needed 
  •  A notes column for observation of participants behaviour or direct quotes

The participants column in this example uses two colours green for yes and red for no, making it easy to visualise summarise which participants validated the teams assumptions and which did not.

Tomer Sharon wrote about the Rainbow Spreadsheet in 2013. Since then, there have been lots of articles online with different takes on the format for this spreadsheet.

How to use this method?

This is how I’ve used this method on several projects.

1. Walk through the journeys you plan to test as a team

Together, run through each step of the journey you expect people to do on your prototype or live service. As you do this, list what the team expects people to do, for example ‘click on home tab’. Including lots of detailed assumptions will help you spot any issues more easily so it’s better than having a general assumption for each task. 

Make sure that your assumptions are observable actions that can be classed as ‘yes’ or ‘no’. For example, clicks on the start button or enters credit card details. 

2. Add your assumptions into a spreadsheet

Put all of the assumptions into a spreadsheet as separate rows. Next, add a column for each participant, this is where the note takers will record yes or no for each participant against each assumption. Then add a further column so notetakers can add in further details during the session instead of having to write in a separate document.

3. Assign notetakers

If you have enough observers, get one note taker to record what participants say and another to record what they do. For the note taker recording what participants do, give them access to the spreadsheet so they can record yes and no and add extra notes. You might need quite a few volunteers to cover all of your sessions but it’s worth it. Having people to cover the two different types of notes helps you capture people’s exact words, which gives you useful context during each task.

Even if your note takers have done it before, it’s good to remind them of good practice when taking notes. You should also make sure note takers for the spreadsheet understand what they’re supposed to do and walk them through the format.

A photo showing post its of observations from usability testing. They follow good practice guidelines of one observation or quote per post it about what did or didn’t work, a participant number.
A photo showing post its of observations from usability testing. They follow good practice guidelines of one observation or quote per post it about what did or didn’t work, a participant number.

4. Observing the research

In the session, the note taker for what the person does, will update the spreadsheet when the user follows or diverges from the team’s assumptions. If they have extra notes they can add them into the spreadsheet’s extra column. It’s good to check in with the note takers after each session just in case they’re encountering any issues that you might be able to resolve in the next session.

By the end of the session you’ll have a table which clearly shows which assumptions were correct.

5. Print out the notes for analysis

Print out the notes on what participants said as separate documents and a copy of the rainbow spreadsheet. You want to make sure that everyone in the team has a document, either the spreadsheet or participant script. If you have more team members, print some extra copies of the spreadsheet. Giving everyone a copy of something means that they can be involved in the session rather than just listening or not paying attention.

6. Run through the tasks with these notes

Hand out scripts and spreadsheets to your team so that everyone has something to read from. Start on task 1. Get someone to look at the notes in the spreadsheet and to explain what participant 1 did. Use the script from what the participant said to help flesh out any further context. 

When the user’s actions diverge from your assumptions or when they encounter issues with the product, record it on a post-it note, including the task and participant number. Then move on to the next participant. As you work through each task, you’ll end up with notes to supplement the spreadsheet.

When you do this task, you may need to take on a facilitating role to keep people focused on the notes and let them know when they should move on to the next section.

Types of research this method works well with

The ‘Rainbow Spreadsheet’ method works best with usability testing of live and prototype journeys. In these scenarios, your team will have assumptions of how users will use the system. So, the method will help you track the accuracy of these beliefs. 

This method isn’t suited to interviews or contextual research where it’s harder to predict what people will do so you may struggle to formulate provable assumptions. In these situations where you’re looking to find information rather than test a design, you’re better off affinity mapping or coding the responses and activities into a spreadsheet afterwards.

Benefits of this method

Compared to note taking followed by affinity mapping, the ‘Rainbow Spreadsheet’ method has a number of benefits: 

  • it can be faster and more focused that starting with a big board of post-it notes
  • there is less writing that the note takers need to do 
  • it’s obvious when the team’s assumptions about how people will use the product are different to how they actually use the product
  • it focuses on user behaviour and what actually happened during your research sessions, rather than opinions or explanations
  • there’s a clear record of your research findings that’s easy to check in the future
  • distributing the notes among the team means that everyone gets involved – not just the loudest voices

Drawbacks of this method

This method also has some drawbacks:

  • your note takers may be too focused on looking for the assumptions to happen and may not record everything that the participants are actually doing
  • it may need a lot of facilitation during the team discussion to get everyone to contribute

Have you used the ‘Rainbow Spreadsheet’ method?

If you’ve tried this method or other methods for analysis, share your reflections and experiences in the comments below. 

If you’d like to try using this method, we have created a template so you can create a copy for yourself to use.

Related posts

https://userresearch.blog.gov.uk/2017/12/20/how-we-did-a-large-scale-group-analysis-of-user-research-data/

https://userresearch.blog.gov.uk/2018/10/23/how-user-support-ticket-analysis-shapes-what-we-do-on-government-as-a-platform/

 

Sharing and comments

Share this page

2 comments

  1. Comment by Nikaesh Rattan posted on

    The Spread Sheet method has shared a template so that anyone can look at this spreadsheet and make their own copy.
    It's an invaluable tool in distributing notes to the Team.
    A great post by the GDS team and highly recommend the download option of the template.

  2. Comment by Steven Wall posted on

    I have used a similar “traffic light” method to create a participant/task matrix. So green is task success with no problems, orange is an indirect success (the interface led the participant to back track, hesitate, or otherwise take an inefficient route) or red for a failure (the participant gave up or the moderator had to help them to get through the task). We create this on the wall of the observation room using sticky notes. The team members write their observations directly on the sticky notes so we capture why we assigned that particular colour. At the end of the day you can quickly look at each task and grade it based on the “worst” colour observed. So if the task had at least one red rating in your sample, it recieves a red overall, and so on.