Google Sheets – Filtering your data

Let’s look at how you can filter your data to show a particular aspect of it. There are two ways to do this:

• Filter (by value & by condition)
• Filter View

---

Filter – By value

If we have a set of data, we can add some filters to enable us find the information we want.

In this example, we have some course books which the teachers are using, and I want to be able to see what books a particular teacher is using.

Select the headers of the columns. Generally, I select all of them just in case I want to filter any of the columns.

Go to the Data menu, and click on “Filter”.

or use the toolbar shortcut: 

Small little boxes with triangles will be added in the header cells.

To access the filter menu, click on one of the squares. For example, I want to filter the list by one of the teachers.

At the bottom of the dialogue box, under “Filter by values”, you will see the list of teachers with ticks next to them. This means that they are selected and that they will be visible in the list.

Here I want to see just the books that Bob has. So, I untick the other teachers and leave Bob ticked. An alternative way, if you have a long list, is to click “Clear” and then tick the one you want. Then click “OK”.

As you can see this hides the other teachers from the list and just leaves Bob.

To reset the list, click back on the same filter square, and click on “Select all”, then click “OK”.

The full list will return.

---

Filter – By condition

The above filters by specific values, which you can hide or unhide. But how do you filter a range of values based on some kind of stated condition? That’s where “Filter by condition” comes in.

Here we have some exam results and I want to filter the list to show those who haven’t achieved the pass mark of 50%.

Click on the filter in the Exam Results cell. Then click on “Filter by condition”.

A box will appear below “None”. Click on that and you will have various conditions to choose from. As I want to see who had less than 50%, I select “Less than”.

Another box will appear below, where you fill in a value, or sometimes a formula. In this I’ll type in 0.5. Note, I need to type 0.5 and not 50, as I’m working with percentages here. Then click “OK”.

As you can see, the list now shows the 3 students that didn’t pass.

You can reset the list by clicking on the same filter again, then click on the condition, in this case, “Less than”.

Then click “None” for no condition.

---

Filter View

 The above filter is great for most cases but it does have a couple of disadvantages.

1. When you filter something anyone else also viewing that sheet will also see the filtered view, and sometimes you don’t want to affect how someone else sees the sheet.
2. Sometimes you want to use the same filters over and over again, and having to set them up each time would be a little bit annoying.

So, you’ve guessed it, there’s an alternative way to filter which addresses these two issues.

Here I have some performance figures of some students in four different classes (A, B, C, and D). I want to create a filter for class A.

1) Select the header row and all the columns you want to include in your filter.

2) Go to the Data menu, click “Filter Views” then click “Create new filter view”.

This brings up the filter view. This is where you tell it what to filter by actually filtering the results.

Click on the Class filter.

Then untick classes B, C, and D, leaving A ticked. Click “OK”.

This will filter the list, showing only Class A. Others viewing the sheet with their accounts, won’t see this view, they will see the sheet as normal.

Now, it’s a good idea to rename the filter, so you know which one is which. Next to “Name:” click on “Filter 1”, then type in the new name, in this case “Class A” and press Enter.

  

To close the filter, click on the cross on the right hand side of the page.

To filter by that filter again, go to the Data menu, click “Filter views”, then click “Class A”.

It will filter exactly what you set up the first time.

You can also set up filters using the “filter by condition” option.

---

Duplicating a filter

Sometimes you want to create filters that are similar to each other, and the best way is to duplicate one. For example, with the list above I want to create a filter for each class. So let’s create one from the Class A filter already made.

1) Click on the cog on the right-hand side of the screen, and click “Duplicate”. This makes a copy of the existing filter.

2) Change the filter name, e.g. Class B.

3) Click on the filter you want, e.g. the class one.

4) Click on the values you want, e.g. B.

4) Click the cross to close the filter.

Clicking on Data > Filter views, you will see the new filter added to the list.

---

If you want to delete a filter, open the filter via the Filter views menu, and click on the cog. Then click on “Delete”.