Organising Data with Forms and Sheets

Switching to Google Sheets

In my work supporting online learning, I use a lot of Google Forms to collect and curate information. One of the problems that can occur is that there are many sheets with data spread across the all. Here then are some of the steps that I have taken to streamline some of the steps and processes.

Multiple Sheets Imported Together

Summary sheets linked to forms can add up quickly. One way around this is to use the Import Range formula to collect a number of responses in one place. Along with formulas to translate text, generate sparklines and fetch financial data, IMPORTRANGE is a part of the Google collection:

=IMPORTRANGE(spreadsheetkey, rangestring)

The formula allows users to bring in a range of data from one spreadsheet to another. It needs to be noted that the first time it is used, the user will be prompted to grant permissions.

Conditioning Completed

Another issue with forms is that long lists of data can become unwieldy. One particular use is submitting responses and feedback. Often these tasks involve an action, however it can be difficult to manage these. One answer was to add an additional column and use this information as a trigger for conditional formatting to colour a whole row. The following custom formula that allows this is:

=$A1=”TEXT”

‘A’ is the column that includes the trigger, while ‘TEXT’ being the actual trigger.

Developing a Dashboard

Having all the data imported into separate tabs within the one spreadsheet is one step in organising information. The next step is representing this content in the form of a dashboard. My first iteration was to provide a summary of the responses across all the sheets. To capture this I counted the responses by focussing on emails, using the UNIQUE formula (thanks Martin Hawksey:

=COUNTA(UNIQUE(A:A))

The reason that I included ‘UNIQUE’ is because some people submitted multiple responses for various reasons. Although there are other means of avoiding this (submit once or adjust responses), these solutions sometimes create their own issues and confusions.
Once this summary table was complete, I used it to create a chart to visualise it. To share this particular information, I made it a separate tab and published it. This way I do not need to give access to the sheet and instead can give access to the summary. Although this is not technically ‘a dashboard’, I will most likely share the whole dashboard as I develop it further. For more information on designing a dashboard, I recommend this post from Ben Collins.


So these are some of the ways in which I have streamlined data and the way in which people are able to engage with it. The add to my previous tips and tricks associated with Google Sheets. What are some of the ways in which you use Forms and Sheets? As always, comments welcome.


If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.