Menu Search
Jump to the content X X
Smashing Conf Barcelona

You know, we use ad-blockers as well. We gotta keep those servers running though. Did you know that we publish useful books and run friendly conferences — crafted for pros like yourself? E.g. our upcoming SmashingConf Barcelona, dedicated to smart front-end techniques and design patterns.

The Rainbow Spreadsheet: A Collaborative Lean UX Research Tool

Liverpool FC fans sing “You’ll Never Walk Alone”1 to their players during matches. UX research is best done when a team is involved. When you run UX research on your own without active observers, you are missing its point. This article describes and gives you a tool I created called the Rainbow Spreadsheet.

With it, you will be able to collaboratively observe UX research sessions with team members (or clients). You will be able to conduct research that involves the entire product team, with results that are turned around quickly and that team members will be committed to acting on. And all of this without writing a formal exhaustive research report that no one wants to read.

Further Reading on SmashingMag: Link

What Is The Rainbow Spreadsheet? Link

The Rainbow Spreadsheet, which takes its name from the different colors used in it to represent the study’s participants, is a spreadsheet with which all of the data collected during a UX study is centrally and simultaneously documented by a team of people; for example, through a Google Doc. It serves as the centerpiece for lessons learned from a study, and later turns into the final report.

Preview of the Rainbow Spreadsheet6
The “Observation” sheet: repeated observations are highlighted in different colors. (Large preview7)

Before The Study Link

As you prepare to run the study, your goal is to have a spreadsheet ready to be used, with all of the known information about participants and tasks entered. You also want your team to be well informed on how to use it and how to document its observations.

  1. Finalize your study’s protocol or discussion guide. Have a very good idea of what you will ask participants to do and what questions you will ask them to answer, and in what order.
  2. Schedule 45-minute breaks between study sessions. You will use those breaks not only to prepare for the next sessions but to discuss observations with your team. Here is how a four-participant, one-day study schedule might look like:
    10:00 – 11:00 Participant 1
    11:00 – 11:45 Break 1
    11:45 – 12:45 Participant 2
    12:45 – 1:45 Break 2 (including lunch)
    1:45 – 2:45 Participant 3
    2:45 – 3:30 Break 3
    3:30 – 4:30 Participant 4
    4:30 – 5:30 Summary
  3. Make a copy of the master rainbow spreadsheet8 for your own use.
  4. Enter the information about participants that you collected during the recruiting stage in the sheet titled “Participants”:
    The 'Participants' sheet: details of the characteristics of participants and links to session recordings. 9
    The “Participants” sheet: details of the characteristics of participants and links to session recordings. (Large preview10)
  5. Enter predetermined observations in the sheet titled “Observations.” These might be behaviors that you and your team (or client) expect participants to demonstrate; for example, “Noticed the green ‘Calculate’ button.” Do not enter double-barreled observations, such as “Noticed and used the green ‘Calculate’ button” because determining what to indicate in case a participant demonstrates only one of the behaviors is sometimes hard (in this case, either noticing or using the button).
  6. Customize the columns for participants to match the number of the study’s participants. The master spreadsheet contains 10 columns, each in a different color. If you have five participants, remove (or hide) columns P6 to P10. If you have 12 participants, add two more colored columns.
  7. Share the spreadsheet with your team in an email or meeting. Explain what it is, and tell them it will serve as a summary for the study and that there will not be another report. Encourage them to attend as many study sessions as possible.The observation sheet with 12 participant columns.11
    The “Observation” sheet, with 12 columns for participants. (Large preview12)
    The observation sheet with 5 participant columns.13
    The “Observation” sheet, with five columns for participants. (Large preview14)
  8. Ask team members to read the “Participants” sheet prior to the study to better understand who participants are.
  9. Ask team members to use the “Observations” sheet during study sessions to note the behaviors of participants. Instruct them to color a participant’s cell when a certain behavior occurs. If a particular behavior repeats in sessions with other participants, ask team members to color the relevant participants’ cells, rather than add one more line for a behavior that’s already been noted. To clarify: there is only one spreadsheet, which everyone accesses and works on at the same time.
  10. Identify UX metrics to monitor during the study with your team, and prepare the sheet titled “Metrics” to collect them. The master spreadsheet is prepared to collect three basic metrics: the success rates of tasks, the time to complete tasks, and self-reported satisfaction ratings. Collect any metric you see fit.
    The metrics sheet with sample data for task success, time on task, and satisfaction.15
    The “Metrics” sheet, with sample data for task success, time on task, and satisfaction. (Large preview16)
  11. Ask team members to add notes to the “Raw” sheet. Tell them it is there for anyone to add anything that doesn’t belong in the other sheets. My experience is that this sheet usually remains empty, but some people feel more comfortable taking notes there.
  12. Add sheets as you see fit. My team was once interested in what questions participants ask when they use a certain product, so we added a “Questions” sheet and assigned a “Chief Questions Officer” to log that data. There are no rules. Feel free to customize this spreadsheet to your team’s needs.

During The Study Link

As the day of the study begins, you and your team will begin entering data into the spreadsheet. As the day progresses, you’ll notice that your team is getting comfortable with the mechanics of using it and is doing an even better job of inputting its observations. Facilitating quick debriefings after each study session will increase the quality and tightness of the data entered.

  1. Enter data into the “Observations” sheet as soon as the first session of the study starts. Although moderating the session, paying attention to what participants say and do, taking notes and minding the observers is somewhat challenging, try to see if they are adding observations. If they aren’t, contact them through an instant messenger and remind them to feed the sheet with observations. If needed, pause the session, apologize to the participant, and finish the short IM conversation. Yes, it is that important.
  2. Don’t worry about overriding each other’s data. People usually self-manage their entries to prevent this from happening. Let it be.
  3. Fill in the “Metrics” sheet with data. You can definitely delegate this part to one of the observers, especially one who plans to attend the entire study. Another option is to assign metric-collecting responsibilities. So, for example, one team member would collect and log the success rates of tasks, another would take care of the time to complete tasks, and so on.
  4. Talk with your team between sessions. After the first session is over and after each session, break for a few minutes. Use this break to prepare the study room for the next participant and to collect your thoughts and organize your entries in the spreadsheet. Then, go to where your team is and discuss its observations. Help people understand observations that are not clear to them (rephrasing if needed). Tighten the list of observations by agreeing with team members on what should be removed. Candidates for removal from this sheet would be duplicate entries and any entry that cannot be considered an observation (such as a conclusion, thought, inference, solution to a problem or action item). Clarify to the team that, at this point, everyone should be entering only things that participants do or say, and that you’ll discuss everything else by the end of the study.
  5. Enter data into any other sheets you’ve created. Don’t feel bad if something doesn’t work out. This is a learning opportunity for you and your team. Maybe you should do something differently the next time around. Decide together.

After The Study Link

Although you and your team will be tired after a long day of research, it is time for one last effort to summarize, come to conclusions and assign action items. Here is what needs to be done to wrap up the study and to prepare the spreadsheet for future action:

  1. Assemble the team for a summary discussion as soon as the last participant has left the building. The goal of this discussion is to agree on the answers to the research questions17 that you defined in your study plan, to identify the primary findings of the study and to decide on the next steps.
  2. Add a “Summary” sheet as the first in the spreadsheet. This sheet will have four areas (see the sample rainbow spreadsheet18):
    • research questions,
    • answers to research questions,
    • action items,
    • primary findings.
    The summary sheet with research questions, answers, action items, and primary findings.19
    The summary sheet, with research questions, answers, action items and primary findings. (Large preview20)
  3. Facilitate the discussion until you reach the point that the team feels it has captured the essence of what happened during the study and is ready to take action. Don’t overanalyze. Avoid “analysis paralysis.” If you have 10 important action items, you are in a pretty good shape. There’s no need to exhaust every tiny detail of the study’s results.
  4. Add links to video recordings of the sessions (if you recorded them) to the “Participants” sheet.
  5. Declare the study done, and publish the spreadsheet. When you share the spreadsheet outside of your immediate team, don’t call it the “Rainbow Spreadsheet.” Instead, call it “the report.”

When To Use The Rainbow Spreadsheet? Link

The most appropriate kind of study in which to use the Rainbow Spreadsheet is one that involves live, moderated sessions; for example, a traditional usability test in which participants interact with a design, while other people (stakeholders, team members, the client) observe in the next room or remotely. Any other moderated research study is also appropriate.

The spreadsheet also works well for walkthroughs in which users show you how they use a product or interviews with a product’s users (while others observe).

When Not To Use It? Link

Because the spreadsheet is best used live, using it in any unmoderated activity would not work. It would be irrelevant, say, in an online study that uses a tool such as Userzoom21, Usabilla22 or Loop1123. Using it in fieldwork would also be hard unless the study is being broadcast live or being recorded for viewing later by people gathered together (which is feasible, yet usually harder to do).

Advantages Link

The Rainbow Spreadsheet has several advantages:

  • It involves the entire team.
  • Research results are turned around quickly.
  • The team is committed to act on research results.
  • The visual representation of observations helps people quickly understand what’s important.
  • There is no formal report. When was the last time you heard someone say, “Yes! I get to read a report today!”

Disadvantages Link

There are also some drawbacks to keep in mind:

  • It requires the team to stop whatever it is working on and observe the sessions. That might be too costly, or even unfeasible for small companies.
  • There is no formal report. Some people in your organization might expect a formal, organized document to follow up the research.
  • People who can’t observe the sessions will feel left out.
  • It cannot be used in organizations that prohibit online collaborative documents.

What If Your Manager’s Manager’s Manager Asks For The Report? Link

Give it to them! If you feel uncomfortable sharing something that does not look like a report, you can do one (or more) of the following:

  • Ask them what they wish to know, and meet them for a few minutes to address their need.
  • Copy and paste the “Summary” sheet into an email, and send it to them.
  • Copy and paste the “Summary” sheet into a Word document, make it look like a report (perhaps adding a screenshot of the “Observations” sheet), and send it to them.
  • Present whatever you send or say as “the team’s deliverable” or “conclusions.” It’s not your research — it’s everyone’s.

Remember that people don’t come to work saying, “Yes! I get to read a report today!” So, chances are they’ll be thankful for a short document.

A Final Word Link

UX research is not about producing reports. It’s about answering people’s questions and helping the organization develop empathy for its customers. Deliverables and reports are means to that end. Don’t pay extra attention to them. Focus on your team’s learning. That can only happen in a collaborative way.

The rainbow spreadsheet is a tool to support such collaboration. If it doesn’t work for you, then customize it to your team’s needs or develop your own tools. Writing a report for a week will not serve your team’s needs very well. Find creative ways to learn about users together!

Rainbow Spreadsheet Templates Link

(il) (al)

Footnotes Link

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
  16. 16
  17. 17
  18. 18
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
  24. 24
  25. 25

↑ Back to top Tweet itShare on Facebook

Tomer is a user experience researcher at Google Search in New York City and author of the book, It's Our Research: Getting stakeholder buy-in for user experience research projects (2012). He founded and led UPA Israel and is the co-founder and organizer of leanUXmachine, a weekend of UX learning, collaboration, and mentorship for Israeli startups. Tomer holds a master’s degree in Human Factors in Information Design from Bentley University.

  1. 1

    One obvious key disadvantage you left out is being tied down to presupposed and expected observations. Sure, you get quick results, but you’re also not documenting unexpected behaviors or scenarios.

    • 2

      Lauri K I could not agree more, it was my first first thought. It also feels a bit like somehow requiring a group agreement, if the observer has no ties to the original design and observes things as someone who will not be personally effected by the thoughts of the testee, it doesn’t seem necessary.

      It could be seen as a neat ploy to get observers more engaged, I just don’t see the metric being valuable, it’s more important to test 3 people and compare the results, then it is to test 1 person and have 3 people observe and compare contradicting observations. Recording the session is a nice way to keep the facilitating/observing party honest. I am just not sure what value this would add accept leading to conversations of “I felt that person was frustrated about this” “Oh wow did you? I didn’t see that at all.” I’ve rarely done a usability study and had that happen, we ask the user on a scale of 1 to 4 how much it frustrated them, we don’t allow the observes to somehow decide how that other person felt. You might be surprised how the action someone took might be perceived as frustrating because it took them too long by our standards, and yet the person truly felt completely fine and felt it took just the right amount of time. You only know if you ask.

    • 3

      I think the “Raw” section could be used for unexpected observations.

  2. 4

    This is similar to my own technique which I call the 3 envelopes.

    I have a philosophy that I leave no stone unturned in my quest for the perfect UX – and that quest will be relentless.

  3. 5

    I started UX for one reason and that was to make a difference for people, not just as designers but as human beings.

  4. 6

    To add to your already great article;

    They sing “You never walk alone” in the Netherlands in the city of Enschede as well, for FC Twente.

    I’m certain that little nugget of information will enrich the lives of the hundreds (if not thousands) of Smashing readers. ;)

  5. 7

    Régine Lambrecht

    April 12, 2013 4:10 am

    This seems trying to create quantitative data (color-based occurrence-severity ratings) from qualitative research (think aloud and live observation). It can only be an annexe of a report.

    I don’t believe the monitor can remember all the items and scan them in order to adapt the rating color during the test.

  6. 8

    Thomas Ploeg

    April 12, 2013 6:26 am

    A useless piece if I might be so bold. A terrible practice leaving way to much room for prejudicie and own interpretation. Moreover, it would be far more interesting if you told about using the results, instead of gathering them. Now you just state: discus them.

    I am sorry, just nothing new and definetly nothing worth reading. Standard practice and a bad one at that.

  7. 9

    I wonder… aren’t we quantifying things that are qualitative? I’ve never really believed in counting mouse clicks… and the method you’re proposing is different, but it still looks like it’s trying to count things. I do see the value in trying to write observations down as you go as a team, but is there actual value in getting metrics out of usability tests? I think quantitative data is very useful (and can more easily be done by a computer), but shouldn’t we count things that actually make sense to be counted, and just leave the counting out if we’re testing to see what problems might occur? Tomer, and fellow ux-people, I’d like to hear your view on this?

    • 10

      Tomer Sharon

      April 25, 2013 6:56 am


      I’m not sure what you mean by “count things” and by “getting metrics out of usability tests”. What is it that you find so horrible? Measuring task success, time to complete tasks, and satisfaction? Or is it the popularity of observations in the rainbow spreadsheet that you treat as counting things? Did you get a chance to read “Measuring the user experience” or “Quantifying the user experience”?

      There’s no problem whatsoever with applying statistics on a sample of 5 people if you know what you are doing.
      Either I don’t understand you or completely disagree with you. Not sure yet. Please clarify.
      Thanks for your comment.


  8. 11

    Stephen Mitchell

    April 12, 2013 3:07 am

    When I was at Anfield last weekend. The Liverpool Fans didn’t sing a thing. Place was like a library.

  9. 13

    Steph Burningham

    April 13, 2013 12:16 am

    I’m afraid I think the comments are absolutely right when saying that this seems like recording qualitative info in a quantitative manner. The importance here seems focus on the observer rather than the participants. I don’t think the observers’ collective observations can ever really understand the participants’ thought processes.

  10. 14

    And one other important reminded whenever I see colour based schemes, don’t forget that 8% or so of the male population is red-green colourblind, so test accordingly. (And of course make sure you have different hues so that it can print out in greyscale!)

  11. 15

    I think the “Raw” section could be used for unexpected observations.

  12. 16

    Thanks for sharing

  13. 17

    Lakshmi Muthaiah

    April 29, 2013 1:47 pm

    It was a good read. I was able to compare it a lot with the traditional pencil and paper protocol. This is digital process though with a mix and match of the video recording and the paper protocol. :)

  14. 18

    steve mellor

    June 5, 2014 1:04 pm


    I used this recently whilst analysing 10 user tests in 4 markets. In each market there were between 35-50 usability issues. It was easy to take from market to market and gradually a picture emerged of local and global issues. It was also really useful to help prioritise the areas for the report.

    To those who suggest it’s quantitative – of course it is! But it’s useful in directing the reporting of usability issues, freeing your mind to develop the other qualitative elements that are also important.

  15. 19

    I added a small fix to this spreadsheet.
    Now you don’t have to color the cells manually – just type any value in the cell and it will light up.
    The original one used to require 3 clicks to color a cell, now it’s possible to do it using keyboard only.

    Here is the updated version:


↑ Back to top