Kano Analysis in Excel and Google Sheets: Formulas and Chart

A Worked Example: Four Features for an Invoicing SaaS

Here is a fictional invoicing tool with four features to test. Each feature gets one functional question and one dysfunctional question. The answer pair shown for each feature is an example of a single respondent. It is not real data.

Notice that each question describes something a user can picture. "Search results appear as you type" works better than "faster search indexing".

• PDF export: functional question "If you could export any invoice as a PDF, how would you feel?", dysfunctional question "If you could not export invoices as PDFs, how would you feel?" Example answer pair: I expect it / I dislike it. Category: Must-be.
• Instant invoice search: functional question "If search results appeared as you type, how would you feel?", dysfunctional question "If search results did not appear as you type, how would you feel?" Example answer pair: I like it / I dislike it. Category: Performance.
• Automatic payment reminders: functional question "If the tool emailed late payers a reminder for you, how would you feel?", dysfunctional question "If the tool did not send reminders for you, how would you feel?" Example answer pair: I like it / I am neutral. Category: Attractive.
• Dark mode in the editor: functional question "If the invoice editor had a dark mode, how would you feel?", dysfunctional question "If the invoice editor had no dark mode, how would you feel?" Example answer pair: I am neutral / I am neutral. Category: Indifferent.

Example: One Feature Across Six Respondents

The list below shows fictional answers from six respondents for automatic payment reminders, written as functional answer / dysfunctional answer, then category. Six answers are far too few for a real study. The point is to show how each row becomes a category and how the scores follow.

Counts: Attractive 3, Performance 1, Must-be 0, Indifferent 1. The questionable answer is left out. Better = (3 + 1) / (3 + 1 + 0 + 1) = 0.8. Worse = (1 + 0) / 5 = 0.2, shown as minus 0.2.

In this example, reminders lift satisfaction a lot when present and hurt little when missing. That is the profile of a delighter.

• Respondent 1: I like it / I am neutral, Attractive.
• Respondent 2: I like it / I can live with it, Attractive.
• Respondent 3: I like it / I dislike it, Performance.
• Respondent 4: I am neutral / I am neutral, Indifferent.
• Respondent 5: I like it / I expect it, Attractive.
• Respondent 6: I like it / I like it, Questionable.

The Spreadsheet Setup, Tab by Tab

Tab 1, Raw: column A holds the respondent ID and column B the segment. Then each feature takes two columns: functional in C, dysfunctional in D for feature 1, then E and F for feature 2, and so on. Leave the header row with clear names such as "PDF export F" and "PDF export D".

Tab 2, Grid: type the five answers in B1 to F1 (dysfunctional answers) and in A2 to A6 (functional answers), in this order: I like it, I expect it, I am neutral, I can live with it, I dislike it. Then fill the grid row by row as below. Each row starts with the functional answer and reads across the dysfunctional answers in the same order.

• I like it: Questionable, Attractive, Attractive, Attractive, Performance.
• I expect it: Reverse, Indifferent, Indifferent, Indifferent, Must-be.
• I am neutral: Reverse, Indifferent, Indifferent, Indifferent, Must-be.
• I can live with it: Reverse, Indifferent, Indifferent, Indifferent, Must-be.
• I dislike it: Reverse, Reverse, Reverse, Reverse, Questionable.

Tab 3: Categories, One Formula per Feature

Column A repeats the respondent ID. Column B is feature 1, column C is feature 2, and so on. In B2, write this formula:

=IFERROR(INDEX(Grid!$B$2:$F$6, MATCH(Raw!C2, Grid!$A$2:$A$6, 0), MATCH(Raw!D2, Grid!$B$1:$F$1, 0)), "")

The first MATCH finds the row of the functional answer. The second finds the column of the dysfunctional answer. INDEX returns the category where they cross. IFERROR leaves the cell blank if someone skipped a question.

Fill the formula down. For feature 2 in column C, point it at Raw!E2 and Raw!F2. Because each feature uses two raw columns, write the formula once per feature column rather than dragging it right. The same formula works in Excel and Google Sheets.

Formulas to Count Categories and Score Better and Worse

Tab 4, Summary: put feature names in column A, starting at A2. Put the category names in B1 to G1: Attractive, Performance, Must-be, Indifferent, Reverse, Questionable. In B2, write:

=COUNTIF(INDEX(Categories!$B$2:$Z$1000, 0, ROW()-1), B$1)

INDEX with 0 as the row picks the whole column of the feature on that row. Fill the formula right to G and down to your last feature. Then add three columns for the main category, the better score and the worse score, using the formulas listed below.

Better runs from 0 to 1 and shows how much having the feature lifts satisfaction. Worse runs from 0 to minus 1 and shows how much leaving it out hurts. Reverse and questionable answers stay out of both.

If two categories in a row are almost tied, filter the Raw tab by segment and recount. Two user groups often see the same feature very differently.

• Main category in H2: =INDEX($B$1:$F$1, MATCH(MAX(B2:F2), B2:F2, 0))
• Better in I2: =(B2+C2)/(B2+C2+D2+E2)
• Worse in J2: =-(C2+D2)/(B2+C2+D2+E2)

How to Read the Kano Chart

Select the Worse and Better columns and insert a scatter chart. Put the absolute value of the worse score on the horizontal axis (use =ABS(J2) in a helper column) and the better score on the vertical axis. Add the feature names as data labels.

A common convention is to draw a line at 0.5 on each axis. That gives the four zones listed below. Then turn each zone into roadmap items.

For help structuring the result, see our guides on how to make a roadmap and on the B2B SaaS product roadmap. Kano says nothing about effort, so many teams pair it with an effort score such as RICE before final ordering.

• High worse, low better: must-be. Fix these first.
• High worse, high better: performance. Invest where it links to your main goal.
• Low worse, high better: attractive. Add one or two per release.
• Low worse, low better: indifferent. Pause or cut them, and write down why.