In Excel 2010, you can go a long way with the help of the Filter function, which you can use to specify simpler conditions. But if you need to use slightly more complicated conditions with several different criteria, you have to use the function called Advanced filter.
This function may initially seem a little complicated to use. But you just need to understand how the conditions area works and test yourself with different types of conditions.
How to use the Advanced filter function based on your conditions
1. CREATE A CONDITION AREA. Start by inserting a few blank rows at the top of the spreadsheet. Then copy all the column headings in the list of values and copy the headings into the top row of the spreadsheet.
2. ON YOUR TERMS. Now enter your terms. In our example, we want to retrieve all companies in Stockholm with a turnover over SEK 20 million as well as all companies in Malmö and Kalmar. We enter the conditions under the respective heading in the conditions area.
3. ADVANCED FILTER. After entering the desired conditions, select any cell in the list of values. Then click on the menu tab Data and chooses Advanced in the ribbon Sort and filter. In the dialog box that opens, the list area is now filled.
4. FILTERED LIST. Click in the field Condition range. Select the pasted headings at the top of the spreadsheet and all rows that have conditions (in our example the top four rows). Choose whether the list should be filtered in place or copied to another location and click OK.