How to interpret survey data in Google Sheets with dynamic charts and drop down menus

A slightly simpler, but equally awesome solution by Ben Collins is available here.

Running a survey is SO much work. Full stop. From designing meaningful questions, to creating a survey form, to implementing a communications plan to remind people to take the survey… even before you get the results back, you’ve dedicated a LOT of resources. Which is all the more reason to have a plan for interpreting the results!

What if I told you that you didn’t have to rely on the auto charts from Google Forms, and instead, you could develop a fully automated, responsive, Google sheets dashboard for disaggregating data and drawing insights from the data?

In this GIF, I show how you can take two columns from your survey data and compare results between a sub group and the full group on that, particular question.

In this survey, there are “Values” and “Demographics” and “Priorities,” I substituted the “real” values with fake versions. Values have become colors, demographics have become locations, priorities have become silly things to do.

How do you feel about the color red?
How do you feel about the color orange?
How do you feel about the color yellow?
How do you feel about the color green?
How do you feel about the color blue?
How do you feel about the color purple?
How do you feel about the color indigo?
How do you feel about the color magenta?

Have you been to the aquarium?
Have you been to the zoo?
Have you been to the garden?
Have you been to the pool?
Have you been to the museum?
Have you been to the farm?
Have you been to the playground?
Have you been to the restaurant?

Eating ice cream every day
Painting murals on every wall
Swimming in a pool of Orbeez
Bringing a manicurist
Going to as many protests as possible
Custom designed Crocs
Going camping every weekend
Eat cheese from the moon

In a survey like this, it is useful to see how the WHOLE GROUP responds to a value or a priority, but it is even more useful to search for nuance in how different demographics of people respond, assuming there is a big enough sample size in the demographic group to be meaningful. In a survey of 229 participants, I set a threshold of 10 respondents to be a significant enough group to be valid.

Here is an example of a question that I can ask AND answer using this data tool:

How do people who “have been to the garden” feel about “the color green” compared to how the whole group feels about “the color green”?

This is a reframe of, “how do LGBTQ identified people” feel about the value, “we treat each other with respect”? (This is also a fake example, but closer to the purpose of the survey).

Let’s talk about how to set up YOUR data to make a similar tool!

Step One: The Survey

When you are designing the survey, I suggest that you keep your responses as simple and reusable as possible. For example, all of the questions in this example are structured this way:

• On a scale of 1-5: How do you feel about [color]?
• Yes/No: Have you been to [place]?

This means that no matter which question I am pulling into a chart, the answers are the same range of options. I don’t have to deal with complex “sentence segments” like “my favorite color is blue” vs “my favorite color is purple.” Or worse, the dreaded (but very qualitatively useful…) free response box. That is difficult to search for.

As a result of applying these survey design principles, my Survey Responses sheet looks like this:

If your survey responses are NOT set up this way, you might want to do some “find and replace” to make your Sheet easier to “query.”

Step 2: Drop Down Menus

The drop down menu interface in this tool is surprisingly easy to set up, and VERY popular with my team!

All you need to do is leverage a feature called “data validation” in Google Sheets which restricts cells to have only certain values from a drop down menu. I created a separate tab called Data Selector Choices. Then, I copied the column headers that I wanted for each group into that tab. Finally, in the cell where the dropdown menu should be, I clicked Data > Data Validation and used my curser to select the list of values from my Data Selector Choices tab. They need to be an exact match to column headers from your data tab!

I added some stylish colors to make the spreadsheet more beautiful. This is optional.

Step 3: Translate Drop Down Menus into column locations

I got some help on this from this post.

When someone selects a particular Value or Demographic, we want to filter the data from the main Data tab. To do this, we need to figure out which column in the data corresponds to the value selected in the drowdown menu! This was surprisingly/weirdly complicated. Here’s how I did it:

Step 3a: I used the formula =Match() to basically search all of the columns for the text I wanted (ie the name of the value or demographic I had identified) and return the column number it was in.

=match(B2, 'Modified Data'!1:1, 0)

This formula takes the text in Cell B2 and searches for it in Row 1 (header row) of the main Data tab.  The answer is 5, which means this text is found in the 5th column.

Step 3b: I need to reference this location in another formula later on, but columns are always referred to with LETTERS, not numbers! I need to convert this number into a letter. There’s a formula for that!

=substitute(address(1, c2, 4), 1, "")

This formula takes the number in cell C2, looks up the Letter Equivalent, and plugs it back in.

Now, repeat the steps above for your next drop down menu!

Step 4: Filter your data

I got some help on this part from Emily Hicks-Rotella.

Now we know which columns we need to look for data in!

In other words, we want to see COLUMN E data, but only for people who said “Yes” in Column [xyz] (demographic data).

Sounds like a case for the =FILTER() formula (one of my favorites!). We also need a little help from the =INDIRECT() formula.

In cell A6, I started typing the Filter formula. If I wasn’t using “dynamic” criteria, I could just type or select exactly the data I wanted. I could call this “hard coded” as in, it will be locked to these specific values regardless of changing the “selectors.”

=filter('Modified Data'!E:E, 'Modified Data'!BG:BG="Yes")

This formula takes the range in column E from the Main Data tab (column E = "How do you feel about the color green?") and shows only the rows where Column BG ("Have you been to the garden?") is Yes.  In this case, there are only 9 people who "have been to the garden," so there are 9 values returned.

=filter(indirect("'Modified Data'!"&D2&":"&D2),indirect("'Modified Data'!"&D4&":"&D4)="Yes")

This formula does the same thing, but instead of typing the cell references, we are building them to dynamically point to the Column Name in Cell D2 (where the column that houses the Selected Value data) and Cell D4 (the column that houses the Selected Demographic data).

For convenience, I kept the “Filter” results in my Sheet, but I turned the text color “white” so that it would be “hidden!”

Step 5: Summarize your data

Cool. We now have the ability to create a simple list of JUST the data we want: How does a subgroup of people feel about this larger strategic question?

In order to form a chart, we need to count up the responses. How many 1’s? How many 2’s? Etc!

To do that, I made a simple table (also hidden) that looks like this:

What you see in Column J (1, 2, 3, 4, 5) is typed in (by moi) but you could use a dedupe formula if you don’t know what the range of values could be. In this case, because I designed my survey to ONLY produce answers on a scale of 1-5, I could make this table pretty simply. The first section summarizes the results of the Filter formula (from above) which happens to dump data in Column A from cell 6 until whereever it ends.

The section below is a bit more complicated, as we need to use =Indirect() again to reference the column from the Values Selector.

Same table above and below, just selected a different cell to show you different formulas.

=Countif(A6:A, J6)

This formula looks at data pasted from the Filter formula and summarizes how many rows have the value "1", which is located in J6.

=countif(indirect("'Modified Data'!"&D2&":"&D2), 1)

This formula looks at data from the Selected Value column in the main Data table (plugs in the appropriate Column Name), and counts how many rows have the column "1."  I could have done a cell reference for 1 (J12) but I decided not to for some reason.

Step 6: Design your charts

Now that we have tables that summarize the Subgroup (by demographic) and the Full Group, we can build a chart! This is the easiest part of the exercise. Simply highlight the 1:5 table that you want to summarize and select Insert>Chart. From here, you can use the Chart Menu to select chart shape, label types, and other preferences.

Step 7: Design your dashboard layout

You’re almost at the end of the gauntlet! You have created Two selectors, TWO invisible tables, and a set of charts. Now, I suggest you move these components around so that they fit well on the screen real estate. You can enter some text boxes or use cells to write instructions. You may want to play with the background color, text color, column widths, etc to make your dashboard as convenient as possible.

Step 8: Document and celebrate

This took me about 3 hours to build (I bet I could cut that way down now that I’ve reflected on the process!). When I completed the first full draft, I recorded a video of me explaining how to use it. This not only helped ME remember choices that I made, but it also helped my team be fully empowered to use the tool.

Making survey data useable and fun is a daunting, but rewarding project. I commend all of you who are taking it on! If anything in this blog post needs further elaboration, please leave me a comment so that I can provide further support. Now, let’s go out there and LEARN!