Advertisement
  1. Business
  2. Google Drive
Business

Turn Your Google Docs Form Responses Into Beautiful Visualizations

by
Difficulty:IntermediateLength:MediumLanguages:

One of Google Docs best features is its forms, which are a free way to survey your users and gather information. The survey data is then automatically saved into a Google Sheets spreadsheet, where you can put the data to work—and that's where things get really interesting. Unlike most form apps where you'll simply get a list of your responses, Google Sheets lets you turn your data into charts and other visualizations in seconds.

It'll take a bit of extra effort, but once you're done, your form will be far more powerful than just a form. This tutorial will show you everything you need to turn your Google Docs form responses into beautiful visualizations, no other apps needed.

Looking for a Shortcut?

If you want a quick, easy way to create beautiful charts from form responses, try the qForms app available on Envato Market.

qForms is a stylish and powerful forms management tool. It could be used to create surveys, questionnaires, contact forms, registration forms, and more; the possibilities are endless.

Each response can be viewed online and qForms can also produce a detailed report, equipped with charts and more. No need to crunch the numbers yourself. It was created to be a fully-functional (and much better!) alternative to Google Forms and similar software/services.

qForms
qForms

If you'd prefer to create the charts yourself from scratch, read on for the full process.

1. Pick Your Question Types Carefully

It all starts with your form. If you know there are certain questions that you will want be able to show a graph of responses, my recommendation is to use a multiple choice, scale, choose from a list or check box question type. The reasoning behind this will become apparent later on, but for now, take my word for it! Think of it like grading a test- the multiple choice questions provide quick data with no room for variation- there are only so many responses available! If you used a text box question type, even for a simple question like "How old are you?", answer formats will vary depending on the respondent and would therefore be difficult to chart (ex. I am 12, 11 years old, thirteen, and more).

2. Name Your Range

 Once your audience has responded to the form, their answers will populate a Google spreadsheet. In the first row the spreadsheet, you'll see your original questions. In order to make the next part of the process easier, you are going to give each column a "nickname" or named range. If you were using the example from the previous step, the named range for the question "How old are you?" might simply be Age.

To name a range, highlight the column of question and responses that you want to graph. After highlighting the row, select Data on the toolbar, and then Named and protected ranges.  

named range
In this example, I wanted to graph responses to the question in Column B. I highlighted the column by clicking on B at the top of the spreadsheet, then selected Data on the toolbar. Finally, I scrolled down to Named and protected ranges.

Next, select Add range, type in a short nickname for the column of data, and then select Done.

naming a range
I named this range peasy because the product I was surveying about began with the letter "P" and I was questioning ease of use. 

3. Add a New Sheet

Select the sign at the bottom left corner of the screen to add a new spreadsheet.

add a sheet
When you click the + sign at the bottom of the sheet, a new spreadsheet will appear next to Form Responses and will be blank.

4.  Add Responses to Column A

On Sheet2, you will need to fill in all the possible answer choices to your original question. Each possible response answer will go in its own cell in column A. For example, the possible responses to the survey question I used were Strongly Disagree, Disagree, Neutral, Agree, Strongly Agree. If you look at the picture below, you will see that I entered each response into its own cell, being careful to type the responses EXACTLY as they were written on the form. 

enter responses
Be sure to type all the possible responses to your questions in Column A exactly how they are displayed. Not using capitalization or a misspelling will throw off your data.

5.  Use =countif Code in Column B

Here is where things get a little tricky. In order to create a chart, you need to know how many times each response occurred. To do this, you will use a formula called countif, which will count your responses from the previous sheet in the specified column. Once you apply the formula once, it can just be applied to the rest of your rows. 

The countif formula requires two pieces of information: the name of the column you want the spreadsheet to refer back to (which is the named range from step 3) and the response you want it to count (which were typed into column A in step 4). 

Next to your first response, which is most likely in cell A1, you will type in the beginning of the formula as =countif( . A dialogue box will appear with directions as to how to complete the formula correctly. You will continue the formula with the named range, followed by a comma, and the cell location of the response you want to count. 

formula
Although Google provides these directions to fill in your count if formula, the directions they provide may look overwhelming. 

In my example, to count responses to Strongly Disagree, I would type in =countif(peasy, A1) because "peasy" was the name I gave my range in step 3, and A1 is the location of Strongly Disagree in column A.  Finish the formula with a closed bracket, and the formula should be replaced by the number of times that response occurred. 

countif
I completed the formula using peasy as my range, followed by a comma, and A1 as the location of the response I wished to count.

Once you have completed this process for one cell, Google Sheets will automatically apply the formula to the rest of your cells. Hover over the square in the bottom right corner of your formula cell until a cross + appears. Then click down and drag the cross down as many cells as there are responses, and  the formulas will pre-populate and count the responses for you. 

extend the formula
To extend the formula to the rest of your cells, hover your cursor over the square that is circled above, click and hold your mouse, and drag down as far as you need the formula. In my case, I would click and drag down to B5, as my last response is in cell A5. 

Once you have dragged the square down to the final response, you will see numeric values next to each response, representing the number of times each response was given. 

display values
After dragging the formula cell down, numerical values will populate column B to show how many times each response occurred. 

6. Create a Chart

Now that the number of responses has been counted, you can create your graph. First, highlight all your cells, including the responses in column A and their respective values in column B. Next, select Insert on the toolbar and scroll down to Chart.

insert chart
From the toolbar, select Insert, then Chart.

A new dialogue box, the Chart Editor, will open. From here, you will be able to select the type of chart you would like to be displayed. For my example, a coulmn graph or a pie chart would display the data in an appropriate way. I chose a column graph.

chart editor
In the Chart Editor, you can choose your graph or chart type. 

7. Customize Your Chart

Finally, you can customize your chart by giving the chart a title, adding x-axis and y-axis values and titles, and changing background or chart colors.

customize chart
You can customize your chart from the same dialogue box by clicking Customize

Once you have customized your chart, click Insert to return to the spreadsheet, where your graph will be displayed. 

display chart

To save, copy or publish your chart, click the dropdown arrow at the top right corner of the chart. 

chart options
You can save, copy, or publish your chart, whatever your display needs might be!

Conclusion

Spreadsheets are incredibly powerful tools, so tie a online spreadsheet with a simple online form tool, and you've got quite a powerful combination. That's what Google Sheets Forms offers. Next time you need to run a survey, give it a try, and leave a comment below if you have any trouble.

Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.