{"site":{"name":"Koji","description":"AI-native customer research platform that helps teams conduct, analyze, and synthesize customer interviews at scale.","url":"https://www.koji.so","contentTypes":["blog","documentation"],"lastUpdated":"2026-10-16T13:53:48.958Z"},"content":[{"type":"documentation","id":"7f54c1a0-fd38-4234-9da2-81dbe5215a1d","slug":"rainbow-spreadsheet","title":"The Rainbow Spreadsheet: A Fast Way to Synthesise Usability Sessions as a Team","url":"https://www.koji.so/docs/rainbow-spreadsheet","summary":"The rainbow spreadsheet, described by Tomer Sharon in Smashing Magazine in 2013, is a shared sheet for synthesising usability sessions as a team. Rows are specific observed behaviours, each participant has a coloured column, and observers colour a cell when a participant matches a row. Two or three observers per session, a ten-minute tidy-up after each session and sorting by count at the end produce findings within hours of the last session. It suits a handful of moderated usability tests. For open interviews or larger samples, affinity mapping, thematic analysis or an AI interview report that shows how many participants back each finding fit better.","content":"A rainbow spreadsheet is a shared spreadsheet for synthesising usability test sessions as a team, while the sessions are still running. Each row is an observation, such as \"Didn't notice the filter button\", and each participant gets a column with their own colour. Whenever an observer sees a participant do or say something that matches a row, they colour that participant's cell. By the end of the sessions, the rows with the most colour are your most common issues, and everyone who watched has already agreed on what they saw. You get from last session to findings in hours instead of days.\n\nThis guide covers where the method comes from, how to set up the sheet, how to run it during sessions, how to turn it into findings, and when a different method fits better.\n\n## Where the rainbow spreadsheet comes from\n\nTomer Sharon, then a UX researcher at Google, described the method in \"The Rainbow Spreadsheet: A Collaborative Lean UX Research Tool\" (Smashing Magazine, 2013). His problem was a familiar one. Usability studies produced long reports that arrived weeks after the sessions, by which time the team had moved on and nobody read them. He wanted the people who would act on the findings to watch the sessions and build the analysis with him as they happened.\n\nThe spreadsheet gave them a single place to do that. Because it was visual, a product manager could glance at it and see which problems were common without reading a word of a report. Teams from the UK Government Digital Service to university design programmes have since written up their own versions.\n\n## Why it works\n\n- **Analysis starts during the sessions.** Observers log what they see in real time, so there is no long gap between the last session and the first findings.\n- **The team owns the findings.** Designers, engineers and product managers who coloured the cells themselves are much harder to convince that a problem \"was just one user\".\n- **Patterns are visible at a glance.** A row coloured across every column is obviously more important than a row with a single cell.\n- **It needs no special software.** Google Sheets or Excel is enough, and everyone already knows how to use it.\n\n## Setting up the sheet\n\n![Process showing five steps to run a rainbow spreadsheet: set up the sheet, brief observers, log during sessions, debrief and turn rows into findings](/visuals/rainbow-spreadsheet-process-steps/rainbow-spreadsheet-process-steps.png \"The whole method fits in the week of your sessions · Source: Tomer Sharon, Smashing Magazine, 2013\")\n\nCreate one sheet for the study with these columns:\n\n| Column | What goes in it |\n|---|---|\n| Task or area | The task or part of the product the observation belongs to |\n| Observation | One specific behaviour or comment, written as what happened |\n| One column per participant | Coloured when that participant showed this behaviour |\n| Count | How many participants showed it (a simple formula) |\n| Notes and quotes | Short quotes or timestamps that back the observation up |\n\nThen:\n\n1. **Give each participant a colour.** Use a header cell in that colour with a short label, like P1 or \"Ana, freelancer\". Keep personal details out of the sheet.\n2. **Pre-fill expected observations.** Before the first session, write rows for the assumptions you want to test. \"Understands what the free trial includes\" is a good row. It makes the sheet faster to use in the moment.\n3. **Group rows by task.** It keeps the sheet readable when it grows past 50 rows.\n4. **Add a \"positive\" section.** Things that worked well are findings too, and they tell the team what not to break.\n\n## Running it during sessions\n\nInvite at least two or three people from the team to watch each session, live or on a recording. Each observer has the sheet open.\n\n- **When a participant does something that matches an existing row**, colour their cell.\n- **When they do something new**, add a row with a specific description and colour their cell.\n- **Write behaviour, not interpretation.** \"Clicked Back three times on the pricing page\" is an observation. \"Pricing page is confusing\" is a conclusion, and it belongs later.\n- **One observation per row.** If a row says \"Missed the filter and got lost in search results\", split it in two.\n- **Add a quote or timestamp** in the notes column when you can. It makes the finding easy to check and easy to share.\n\nTake ten minutes after each session to tidy the sheet together. Merge duplicate rows, sharpen vague ones and agree on any cells people disagree about. This short debrief is where most of the shared understanding forms.\n\n## Turning the sheet into findings\n\nAfter the last session:\n\n1. **Sort by count.** The rows coloured for most participants come first.\n2. **Group related rows into findings.** Three rows about missing the filter, the sort and the search scope may be one finding about how people narrow results.\n3. **Rate severity.** Frequency is not the same as impact. A problem that blocks checkout for two participants can matter more than a cosmetic one that annoys everyone. See [usability issue severity ratings](/docs/usability-issue-severity-ratings).\n4. **Write each finding with its evidence.** The observation, how many participants showed it, a short quote and a screenshot.\n5. **Share the sheet itself.** Many teams skip the formal report and present the sheet with a short summary of the top findings.\n\n### A short example\n\nA team tests a new booking flow with six participants. Before the first session they pre-fill eight rows, including \"Finds the date picker without help\" and \"Understands the cancellation policy before paying\". By the end of the week the sheet has 41 rows.\n\nSorted by count, the top row is \"Scrolled past the date picker and tapped Continue\", coloured for five of six participants. Two related rows, \"Looked for dates in the search bar\" and \"Asked out loud where to choose dates\", are coloured for three and two people. Together they become one finding: people do not see the date picker because it sits below the fold on mobile. The team ships a fix the following sprint, because the designer and the engineer who will build it both watched it happen.\n\n## Common mistakes\n\n- **Vague rows.** \"Had trouble with navigation\" cannot be coloured consistently. Name the behaviour.\n- **One observer.** The method's value comes from several people watching and agreeing. With one person, it is just a log.\n- **Treating counts as statistics.** With five to eight participants, \"4 of 6\" shows a pattern worth acting on. It is not a percentage of your user base.\n- **Letting the sheet grow without tidying.** Without a short debrief after each session, you end up with near-duplicate rows and colour that means nothing.\n- **Mixing in personal data.** Use labels, not full names or emails.\n\n## When to use something else\n\nThe rainbow spreadsheet is built for a handful of observed sessions with the same tasks, typically moderated usability tests. It struggles in two situations:\n\n- **Open, exploratory interviews**, where every conversation goes somewhere different. [Affinity mapping](/docs/affinity-mapping) or [thematic analysis](/docs/thematic-analysis-guide) fit better.\n- **Large numbers of participants.** At 30 or 50 interviews, colouring cells by hand becomes slow and inconsistent.\n\n## Getting the same \"who said what\" view from AI interviews\n\nWhen you run research as AI-moderated interviews in Koji, the report gives you a version of the rainbow spreadsheet's core view without anyone colouring cells. Each finding shows how many participants it is backed by, as a share and a count, and a **View supporting responses** button opens the quotes behind it. Themes are pulled from every interview automatically, and you can refresh the report as new interviews come in.\n\n![Koji high-impact finding card backed by 26% of participants with a link to supporting responses](/email/shots/shot-finding.jpg \"Each finding shows how many participants back it, with the quotes one click away\")\n\nTeams often combine the two. They run five or six moderated usability sessions with a rainbow spreadsheet to see behaviour, then run a larger set of Koji interviews to hear why people struggle and how widespread it is. If you add a few [structured questions](/docs/structured-questions-guide), such as a scale question on how easy a task felt, you also get numbers you can compare across rounds. See [tracing report findings to evidence](/docs/tracing-report-findings-to-evidence) for how to check each finding against what people said.\n\n## Related Resources\n\n- [How to conduct usability testing](/docs/usability-testing-guide)\n- [Moderated usability testing](/docs/moderated-usability-testing-guide)\n- [Rapid qualitative analysis](/docs/rapid-qualitative-analysis)\n- [Affinity mapping](/docs/affinity-mapping)\n- [Tracing report findings to evidence](/docs/tracing-report-findings-to-evidence)\n- [Structured questions in AI interviews](/docs/structured-questions-guide)","category":"Analysis & Synthesis","lastModified":"2026-10-16T04:17:21.360333+00:00","metaTitle":"Rainbow Spreadsheet: Synthesise Usability Tests as a Team","metaDescription":"How to set up and run a rainbow spreadsheet for usability testing: columns, colour coding, live logging, the debrief and turning rows into findings, with an example.","keywords":["rainbow spreadsheet","rainbow spreadsheet ux research","usability test synthesis spreadsheet","tomer sharon rainbow spreadsheet"],"aiSummary":"The rainbow spreadsheet, described by Tomer Sharon in Smashing Magazine in 2013, is a shared sheet for synthesising usability sessions as a team. Rows are specific observed behaviours, each participant has a coloured column, and observers colour a cell when a participant matches a row. Two or three observers per session, a ten-minute tidy-up after each session and sorting by count at the end produce findings within hours of the last session. It suits a handful of moderated usability tests. For open interviews or larger samples, affinity mapping, thematic analysis or an AI interview report that shows how many participants back each finding fit better.","aiPrerequisites":["usability-testing-guide"],"aiLearningOutcomes":["Set up a rainbow spreadsheet with one coloured column per participant","Log observed behaviour during usability sessions as a team","Turn the most coloured rows into findings with severity and evidence","Know when affinity mapping or an AI interview report fits better"],"aiDifficulty":"beginner","aiEstimatedTime":"7 minutes"}],"pagination":{"total":1,"returned":1,"offset":0}}