Feb 24 2015
In this lesson, we’ll be learning how to slice and dice, filter and sort your data with the function QUERY. Those of you who have worked with databases before and SQL (Sequenced Query Language) will find this familiar, but even if all this is new to you it’s definitely doable.
See Example 5 and notice that there is a new tab in it called “Sorted”. Take a look at it. Make a copy, then follow along with this example on the “Your Turn” tab.
At its most basic level, a query simply looks up data. So, you could take your Form Responses 1 sheet and query it using:
=QUERY(‘Form Responses 1’!A:F)
This will return all of the data for that range, very similarly to an arrayformula. However, QUERY gives us many more options to manipulate that data. For example, in our Form Responses sheet we have the Full Name as the column furthest to the right. Logically, it should be on the far left side.
To fix this, we use the select parameter. We rewrite the function to be:
=QUERY(‘Form Responses 1’!A:F,”select *”)
This will return the same result as the first formula, but notice that here we are specifying exactly what it is that we are selecting: namely, everything (or * in computer land). We can then modify this to say which columns we want to select:
=QUERY(‘Form Responses 1’!A:F,”select F,C,D,E”)
We don’t need the Timestamp (A) column, we don’t really need to know who is giving the feedback, so all we need are columns C to F, with F first. This gives us “select F,C,D,E”.
Next we want to order this by the student receiving the feedback. To do this we add the clause “order by C”, so it becomes “select F,C,D,E order by C”. Try it out now.
What happened? Did it seem like everything disappeared? If so, scroll down way to the bottom of the screen and you may see your data. Google is sorting the data with the blanks on top. We need to change this, by filtering out blank entries.
To do this we add another clause:
This will remove any blanks and will put our data up on the top where we want it. The order of these clauses is particular:
- order by
That means that our overall statement is “select F,C,D,E where B!=” order by C”. That should give us the data we need, nicely organized by the student receiving the feedback.
Google Sheets Lessons 1-9
Click on a lesson below for a written tutorial demonstrating the lesson.
- Lesson 1 – CONCATENATE
- Lesson 2 – VLOOKUP
- Lesson 3 – ARRAYFORMULA
- Lesson 4 – formatting ARRAYFORMULA
- Lesson 5 – QUERY
- Lesson 6 – UNIQUE
- Lesson 7 – INDEX & MATCH
- Lesson 8 – conditional INDEX & MATCH
- Lesson 9 – Autocrat Add-on