![](/uploads/1/2/7/2/127226973/872923456.jpg)
When sorting your Excel tables and worksheets in Office 2011 for Mac, you’re likely to use ascending and descending sort orders most often. The quick way to sort a table or data range is to select a cell in the column you want to sort. Then go to the Ribbon’s Data tab, find the Sort and Filter group, and click Sort.
The tutorial explains the basics of Excel's Advanced Filter and shows how to use it to find the records that meet one or more complex criteria.If you had a chance to read our previous tutorial, you know that provides a variety of options for different data types. Those inbuilt filtering options for text, numbers, and dates can handle many scenarios. Many, but not all!
![Filter Filter](http://img.wonderhowto.com/img/89/76/63475384645892/0/set-insert-formula-name-into-excel-print-area.1280x600.jpg)
When a regular AutoFilter can't do what you want, use the Advanced Filter tool and configure the criteria exactly suited to your needs.Excel's Advanced Filter is really helpful when it comes to finding data that meets two or more complex criteria such as extracting matches and differences between two columns, filtering rows that match items in another list, finding exact matches including uppercase and lowercase characters, and more.Advanced Filter is available in all versions of Excel 2016, Excel 2013, Excel 2010, Excel 2007, and Excel 2003. Please click on the links below to learn more.Excel Advanced Filter vs.
AutoFilterCompared to the basic AutoFilter tool, Advanced Filter works differently in a couple of important ways. Excel AutoFilter is a built-in capability that is applied in a single button click. Regardless of the used in your worksheet, you should always specify the full date in the Advanced Filter criteria range in the format that Excel can understand, like 7/1/2016 or 1-Jul-2016. Advanced filter for text valuesApart from numbers and dates, you can also use the logical operators to compare text values. The rules are defined in the table below. CriteriaDescription='=text'Filter cells whose values are exactly equal to 'text'.textFilter cells whose contents begin with 'text'.textFilter cells whose values are not exactly equal to 'text' (cells containing 'text' as part of their contents will be included in the filter).textFilter cells whose values are alphabetically ordered after 'text'.
When filtering numeric values that are exactly equal to a given value, you may or may not use the equal sign in the criteria. For instance, to filter records with subtotal equal to 900, you can utilize any of the following Sub-total criteria: ='=900', =900 or simply 900. If your criteria includes just one formula like in this example, be sure to include at least 2 cells in the criteria range (formula cell and heading cell).For more complex examples of multiple criteria based on formulas, please see. Using Advanced Filter with AND vs.
OR logicAs already mentioned in the beginning of this tutorial, Excel Advanced filter can work with AND as well as OR logic depending on how you set up the criteria range:. Criteria on the same row are joined with an AND operator. Criteria on different rows are joined with an OR operator.To make things easier to understand, consider the following examples. The source table in this example contains only four regions: North, South, East and West, therefore we can safely use North in the criteria range. If there were any other regions containing the word 'north' like Northwest or Northeast, then we would use the: ='=North'.
I can do an advanced filter using data on Sheet 1 and filter it onto Sheets 2-5 using different criteria for each time. However, I want the filters to automatically update when I add data to Sheet 1.For example, I have data in cells A2:E28 on Sheet 1 and have filtered successfully onto the four sheets using different criteria each time. However, when I add a new row of data into A29:E29 on Sheet 1, I want this to automatically be filtered as per the advanced filters without having to reapply the filters each time.Any help would be great. Thanks in advance. Another 'not sure if this is going to be useful to you, but it might be to someone browsing later'. You can do this with a formula - type it as if you are doing it for the first row to be filtered.i.e.Filter=AND(OR(A2='X',B2='Y'),OR(D2='Z',E2='Q'))Quotes only required if X,Y,Z,Q are strings.
Then your criterion range is the cell containing 'filter' (or a blank cell, or a cell containing anything else which is not a heading in the filtered range) and the cell containing the formula, and no other cells.
![](/uploads/1/2/7/2/127226973/872923456.jpg)