Google Sheets’ best kept secrets

Make the most of the analysis of results from your online surveys.

Make the most of the analysis of results from your online surveys 

Have you heard about Data Driven? It is a model that runs practically through all the areas of business, from Finance, Commerce, to Human Resources and, of course, Marketing.  Between return on investment, cost per acquisition, and more, business professionals are always looking for different metrics and reports on progress.

In that sense, online surveys emerge as an excellent tool to capture more valuable information about customer behavior and opinion.

Now, once you’ve captured all that information, it’s time for you to process it. In some cases, one faces thousands of data, spread across several online forms.

In recent years, online survey platforms have integrated features that allow the analysis of the information collected both in the space itself and in third-party sites. Thus, one of the most popular calculation processors today is the cloud-based collaborative tool, Google Sheets, which the California company introduced in 2012 and many users have adopted.

Using Google Sheets has several benefits and one of them (maybe the most important one) is that it is free. Nevertheless, when it comes to processing the information from your online form it is important to know some of the applications that the tool has in order to make the most out of the analysis of results.

Here we present you some tips to use Google Sheets more efficiently and make the most out of it when you have to process the information extracted from the online survey platform. 

When it comes to processing your online survey’s information, it's important to know some of the applications that Google Sheets has in order to make the most out of the analysis of results.

1. Generate Heat maps using conditional formatting rules 

If you have included the classic Net Promoter Score (NPS) question “how likely are you to recommend our service to a friend?”, you will need to attribute the categories of “Promoters”, “Neutral” and “Detractors”  to those who responded.

For now you only have numbers from 1 to 10 in the answers. What 's missing? Basically, that you classify the responses. 

You'll be able to do this through the “conditional formatting rules” tool, which lets you assign a color to the responses.  Select the column with the values and apply the rules for each response of the NPS index:

  • If values are between 9 and 10: Promoters, green color.
  • if values are between 7 and 8: Neutral, yellow color.
  • If values are between 0 and 6: Detractors, red color.

2. Clean the values from the fields (CLEAN AND TRIM)

Has it happened to you that in the “Name” field the responses received do not arrive in the same format? Do you need to unify criteria in order to organize the information?

The CLEAN and TRIM feature of Google Sheets are useful to remove all the unnecessary characters, adjust upper and lower case at the beginning, and remove spaces.

3. Protect the data on cells

One of the main benefits of Google Sheets is its collaborative character. That is, unlike other spreadsheets, the document can be completed  by several people at the same time, without the need for it to be hosted on the same computer or server, since Google Sheets is a cloud service.

Maybe it has happened to you that, in the rush to complete a report, several people on the team complete at the same time the same form and then modify the data (numbers or text) that another person completed. This happens because although Google recognizes and displays the users working at the same time on the document, there is some latency to update modifications. Or sometimes, it happens that we leave an open document to edit it and another co-worker modifies it later without consulting.

To avoid this kind of setback, Google Sheets allows you to lock data to prevent modification errors. You can protect one or more rows and columns, even a single cell.

4. Names and last names separately? Yes, it can be done

If you need to divide the data content into one single cell or several, you can use the “divide text into columns” option (you’ll find it in the Data menu).  This resource can be extremely useful if you need to clean up data. For example, if you need to divide first and last names into separate columns on your sheet.

In line with the function explained above, many times it happens that the information in a field needs to be divided. The most clear example for this is, as we already mentioned, the case of the fields with names and last names.

Have you ever consolidated two spreadsheets in which there were different fields? In the case of names and surnames, to match that information we have two options: do it manually or, the most convenient, use the Google Sheets “Divide text to columns” tool.

Click on the drop-down menu next to “Separator” to do this.

Also, you can eliminate rows and columns with repeated data and eliminate additional spaces with the same function.  

5. Import data from other sheets

Instead of keeping data on multiple sheets, you can simply import data from one sheet to another. This also means that you only need to update the data in one place (and not in multiple sheets) and ultimately save a lot of time.

The functionality in question is called IMPORTRANGE and it will allow you to have several online forms running at the same time and will also let you visualize everything in the same place.

This is an exclusive function from Google which allows you to import a range of cells from a specific spreadsheet.  Thus, IMPORTRANGE consists of allowing information to be passed from a “Source Sheet”, that is, the sheet that contains the initial information, to a “Destination sheet”.

In the Destination sheet, the information will be synchronized from the Source sheet and this will be done in a unidirectional way, which means it will synchronize from the source sheet to the target sheet but not vice versa (from the target sheet, the information on the source sheet cannot be modified).

6. Generate “Tiny graphics” with SPARKLINE

You can easily add mini graphics to your spreadsheets to quickly see the tendencies in the information. It's the SPARKLINE function.

This can be especially useful if you want to compare data (like metrics from Google Analytics) or if you wish to convert your sheet into a control panel.

In the case of the online forms, the function is used for punctuation questions. There, SPARKLINE allows you to see rapidly how the user opinions are modified on a graphic inside Google Sheets.

7. Explore the information through the graphics

Are you running out of ideas on how to visualize data? You can use the Explore function to see other suggested ways of presenting your data.

Use Explore to view files, images, or information that can be used to finish the data analysis of your online form. Explore "Topics" to see search results for topics related to your document. Also add a "Related Research" to what is in your document by inserting a quote.

In addition, it is used to add images or information from other documents or the web, add an image, graphic, a footnote, a link or a suggestion.

8. Turn the data from your online survey into an app!

Can you create an app within a few minutes taking information from Google Sheets and without programming knowledge? Yes, you can.

The answer comes from Glide Apps, a simple and entertaining online tool with which you can take the information from the spreadsheet and turn it into an easy-to-use and aesthetically attractive app.

As an example, a Human Resources department can use Glide Apps to create an app with a customized directory of the entire company, with names, titles, new hires and birthdays of the collaborators. All that, of course, taking the information from a Google Sheets spreadsheet.

You can also create an app from a spreadsheet with the inventory of your business. Glide Apps will create a basic app with the information included in the template (for example, products, available quantities, prices, etc.). You can also customize it by adding elements, modifying the design, changing colors, adding photos and logos of your company and even by activating chats and comments.

Then, you can share the app with a link to your audience and they can add it on their smartphone or other devices.

These are just two examples of what you can do with Glide Apps. In addition, the app remains synchronized at all times with the Google spreadsheet. Therefore, if you make any changes to the latter, it will be immediately reflected in the app.

Glide Apps is a freemium platform, with different individual and organizational plans.

Was this information helpful? We invite you to learn how the Survey Kiwi platform works. Our software has integrations with Google Sheets and other digital and cloud tools, which will enhance the value of your survey data. Enter our website now! You will be able to create your first online survey in just a few steps. 

Subscribe to our blog and receive notifications of new notes

Start now to make better decisions

Create account

Free - No credit card needed - Cancel anytime