Using the Advanced Filter

In the Standard Filter you only have three slots for info.

That's a bit limiting. So the Advanced Filter lets you enter up to 8 criteria.

Using the Advanced Filter

Here's your data. Click the image to see it larger.

Now, here's how you enter your critera. Copy your headings and paste them somewhere else in the spreadsheet. Then type the values you want. Click this image to see it larger. I've entered Fargo for the city, ND for the state, and =>5 for the Years of Service. Note that they are all on the same row.  This means they are ANDed together.

Click in the data (not the criteria but the main data) and choose Data > Filter > Advanced Filter. In the window, click in the right-hand field and draw a box around the area where you typed the criteria. Click OK.

You'll see the results. Click the image to see it larger.  ( Simon being the first name for both is just a coincidence.)

Now, if you want OR logic, just enter the values in your critera section on different rows, like this. Click to see a larger version of the image.

These are the corresponding results. Because of the OR, you get a lot more results. Click the image to see a bigger version.

Removing the Filter
To turn off the filter, it's the same as with the standard filter. Click in the filter results, and choose Data > Filter > Remove Filter.

---

---

Comments

[TT Raja]

In case I do not require all the fields, how do I get only the required fields?
This is possible in Excel. Can some one help?

[Sovleig]

Hi TT Raja,

In that case you'd want to use the Data Pilot which lets you both filter and restrict the fields.

[Solveig]

OK, I misstype my name once and it keeps saving the misspelling. Just redoing it. ;>

[tower defense]

In case I do not require all the fields, how do I get only the required fields? This is possible in Excel. Can some one help?

[free online games]

Great tutorial.

[jg]

It must be advertised that the 'Advanced' Filter cannot handle more than 8 criteria.

I have a file with about 500 records and I need to filter for about 25 of them using just one field. Everything works fine up to 8 criteria. Then Calc claims there is an error in the criteria!

What it should say is "I cannot count beyond 8".