How to Automatically Sort Google Form Responses in Google Sheets (Full Transcript)

Speaker 1: Today I'm going to show you how to use the information you've gathered in a spreadsheet from a Google Form and separate it out automatically based on respondents answers to questions. So this is a two-step process and the first step in that process is to create an import range formula on a second sheet that actually takes all of the information from your form responses and moves it over here. This seems like an unnecessary step but it's actually really important because the query we want to run cannot be run from the form response sheet it has to be run from this second sheet. All right the first thing you need to do, let me go ahead and clear all the this data so that we can start fresh, the first thing that you want to do is you want to enter in an import range formula here. So I'm going to go ahead and I have it here but it's missing something so notice there's an NA there's an error. I need to get the URL from this first page so let's go ahead and get the shareable URL and we're going to add it to that formula. Okay it took a minute I got a little error but then it does eventually go through so just give it a minute if you get that error message and it should go through. So notice that my sheet 2 is exactly like my first form response and as more information comes in it will automatically import as well because I made my range this entire section not just to the eighth row. Okay so I'm going to rename this sheet to use this sheet for data. Okay just so I know what it is. Now we're going to get into how to query this information to automatically shoot off based on the site response. So the people who filled out this form were asked what site they're at and we have different sites here but I want to look at just one site at a time. So I opened up a new sheet I again copied my headers let's go ahead and turn that into a header. Now I'm going to do a query formula and this query formula is equals query use this sheet sheet for data. Okay because that's the name of the sheet I want to pull from and then I want to set the range of the data that it's going to pull so a 2 through E and I want to tell it what to do. So it's going to select the data where B equals let's do LHS and that's going to give us just the LHS data. Now I can sort this and I could also do just you know I can do this for any of the data from the use this sheet. If I want to look at just who's going to college and then do another query if I want to look at GPAs if I want to look at where they're going to college I can have multiple sheets. I would then name this sheet since I sorted by LHS I'm going to rename it LHS so I know exactly what data is on this sheet.