Filter by utilizing superior standards

If the information you wish to filter requires complicated standards (equivalent to Kind = “Produce” OR Salesperson = “Davolio”), you need to use the Superior Filter dialog field.

To open the Superior Filter dialog field, click on Knowledge > Superior.

Overview of superior filter standards

The Superior command works in a different way from the Filter command in a number of vital methods.

It shows the Superior Filter dialog field as an alternative of the AutoFilter menu.
You sort the superior standards in a separate standards vary on the worksheet and above the vary of cells or desk that you simply wish to filter. Microsoft Workplace Excel makes use of the separate standards vary within the Superior Filter dialog field because the supply for the superior standards.

Pattern information

The next pattern information is used for all procedures on this article.

The info consists of 4 clean rows above the record vary that will probably be used as a standards vary (A1:C4) and an inventory vary (A6:C10). The factors vary has column labels and consists of a minimum of one clean row between the factors values and the record vary.

To work with this information, choose it within the following desk, copy it, after which paste it in cell A1 of a brand new Excel worksheet.

Kind	Salesperson	Gross sales




Kind	Salesperson	Gross sales
Drinks	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

Comparability operators

You’ll be able to evaluate two values by utilizing the next operators. When two values are in contrast by utilizing these operators, the result’s a logical worth—both TRUE or FALSE.

Comparability operator	That means	Instance
= (equal signal)	Equal to	A1=B1
> (higher than signal)	Larger than	A1>B1
< (lower than signal)	Lower than	A1
>= (higher than or equal to signal)	Larger than or equal to	A1>=B1
<= (lower than or equal to signal)	Lower than or equal to	A1<=B1
<> (not equal to signal)	Not equal to	A1<>B1

Utilizing the equal signal to sort textual content or a price

As a result of the equal signal (=) is used to point a system whenever you sort textual content or a price in a cell, Excel evaluates what you sort; nevertheless, this will likely trigger surprising filter outcomes. To point an equality comparability operator for both textual content or a price, sort the factors as a string expression within the applicable cell within the standards vary:

=”= entry ”

The place entry is the textual content or worth you wish to discover. For instance:

What you sort within the cell	What Excel evaluates and shows
=”=Davolio”	=Davolio
=”=3000″	=3000

Contemplating case-sensitivity

When filtering textual content information, Excel does not distinguish between uppercase and lowercase characters. Nevertheless, you need to use a system to carry out a case-sensitive search. For an instance, see the part Wildcard standards.

Utilizing pre-defined names

You’ll be able to identify a variety Standards, and the reference for the vary will seem routinely within the Standards vary field. You can even outline the identify Database for the record vary to be filtered and outline the identify Extract for the world the place you wish to paste the rows, and these ranges will seem routinely within the Record vary and Copy to containers, respectively.

Creating standards by utilizing a system

You need to use a calculated worth that’s the results of a system as your criterion. Keep in mind the next vital factors:

The system should consider to TRUE or FALSE.
Since you are utilizing a system, enter the system as you usually would, and don’t sort the expression within the following approach:

=”= entry ”
Don’t use a column label for standards labels; both hold the factors labels clean or use a label that’s not a column label within the record vary (within the examples that observe, Calculated Common and Precise Match).

When you use a column label within the system as an alternative of a relative cell reference or a variety identify, Excel shows an error worth equivalent to #NAME? or #VALUE! within the cell that comprises the criterion. You’ll be able to ignore this error as a result of it doesn’t have an effect on how the record vary is filtered.
The system that you simply use for standards should use a relative reference to discuss with the corresponding cell within the first row of information.
All different references within the system should be absolute references.

A number of standards, one column, any standards true

Boolean logic: (Salesperson = “Davolio” OR Salesperson = “Buchanan”)

Insert a minimum of three clean rows above the record vary that can be utilized as a standards vary. The factors vary will need to have column labels. Ensure that there may be a minimum of one clean row between the factors values and the record vary.

To seek out rows that meet a number of standards for one column, sort the factors immediately beneath one another in separate rows of the factors vary. Utilizing the instance, enter:

Kind	Salesperson	Gross sales
	=”=Davolio”
	=”=Buchanan”

Click on a cell within the record vary. Utilizing the instance, click on any cell within the vary A6:C10.
On the Knowledge tab, within the Kind & Filter group, click on Superior.
Do one of many following:
- To filter the record vary by hiding rows that do not match your standards, click on Filter the record, in-place.
- To filter the record vary by copying rows that match your standards to a different space of the worksheet, click on Copy to a different location, click on within the Copy to field, after which click on the upper-left nook of the world the place you wish to paste the rows.
  
  Tip If you copy filtered rows to a different location, you’ll be able to specify which columns to incorporate within the copy operation. Earlier than filtering, copy the column labels for the columns that you simply wish to the primary row of the world the place you intend to stick the filtered rows. If you filter, enter a reference to the copied column labels within the Copy to field. The copied rows will then embrace solely the columns for which you copied the labels.
Within the Standards vary field, enter the reference for the factors vary, together with the factors labels. Utilizing the instance, enter $A$1:$C$3.

To maneuver the Superior Filter dialog field out of the way in which briefly whereas you choose the factors vary, click on Collapse Dialog .

Utilizing the instance, the filtered consequence for the record vary is:

Kind	Salesperson	Gross sales
Meat	Davolio	$450
produce	Buchanan	$6,328
Produce	Davolio	$6,544

A number of standards, a number of columns, all standards true

Boolean logic: (Kind = “Produce” AND Gross sales > 1000)

Insert a minimum of three clean rows above the record vary that can be utilized as a standards vary. The factors vary will need to have column labels. Ensure that there may be a minimum of one clean row between the factors values and the record vary.

To seek out rows that meet a number of standards in a number of columns, sort all the factors in the identical row of the factors vary. Utilizing the instance, enter:

Kind	Salesperson	Gross sales
=”=Produce”		>1000

Click on a cell within the record vary. Utilizing the instance, click on any cell within the vary A6:C10.
On the Knowledge tab, within the Kind & Filter group, click on Superior.
Do one of many following:
- To filter the record vary by hiding rows that do not match your standards, click on Filter the record, in-place.
- To filter the record vary by copying rows that match your standards to a different space of the worksheet, click on Copy to a different location, click on within the Copy to field, after which click on the upper-left nook of the world the place you wish to paste the rows.
  
  Tip If you copy filtered rows to a different location, you’ll be able to specify which columns to incorporate within the copy operation. Earlier than filtering, copy the column labels for the columns that you simply wish to the primary row of the world the place you intend to stick the filtered rows. If you filter, enter a reference to the copied column labels within the Copy to field. The copied rows will then embrace solely the columns for which you copied the labels.
Within the Standards vary field, enter the reference for the factors vary, together with the factors labels. Utilizing the instance, enter $A$1:$C$2.

To maneuver the Superior Filter dialog field out of the way in which briefly whereas you choose the factors vary, click on Collapse Dialog .

Utilizing the instance, the filtered consequence for the record vary is:

Kind	Salesperson	Gross sales
produce	Buchanan	$6,328
Produce	Davolio	$6,544

A number of standards, a number of columns, any standards true

Boolean logic: (Kind = “Produce” OR Salesperson = “Buchanan”)

Insert a minimum of three clean rows above the record vary that can be utilized as a standards vary. The factors vary will need to have column labels. Ensure that there may be a minimum of one clean row between the factors values and the record vary.

To seek out rows that meet a number of standards in a number of columns the place any standards could be true, sort the factors within the completely different columns and rows of the factors vary. Utilizing the instance, enter:

Kind	Salesperson	Gross sales
=”=Produce”
	=”=Buchanan”

Click on a cell within the record vary. Utilizing the instance, click on any cell within the record vary A6:C10.
On the Knowledge tab, within the Kind & Filter group, click on Superior.
Do one of many following:
- To filter the record vary by hiding rows that do not match your standards, click on Filter the record, in-place.
- To filter the record vary by copying rows that match your standards to a different space of the worksheet, click on Copy to a different location, click on within the Copy to field, after which click on the upper-left nook of the world the place you wish to paste the rows.
Tip: If you copy filtered rows to a different location, you’ll be able to specify which columns to incorporate within the copy operation. Earlier than filtering, copy the column labels for the columns that you simply wish to the primary row of the world the place you intend to stick the filtered rows. If you filter, enter a reference to the copied column labels within the Copy to field. The copied rows will then embrace solely the columns for which you copied the labels.
Within the Standards vary field, enter the reference for the factors vary, together with the factors labels. Utilizing the instance, enter $A$1:$B$3.

To maneuver the Superior Filter dialog field out of the way in which briefly whereas you choose the factors vary, click on Collapse Dialog .

Utilizing the instance, the filtered consequence for the record vary is:

Kind	Salesperson	Gross sales
produce	Buchanan	$6,328
Produce	Davolio	$6,544

A number of units of standards, one column in all units

Boolean logic: ( (Gross sales > 6000 AND Gross sales < 6500 ) OR (Gross sales < 500) )

Insert a minimum of three clean rows above the record vary that can be utilized as a standards vary. The factors vary will need to have column labels. Ensure that there may be a minimum of one clean row between the factors values and the record vary.

To seek out rows that meet a number of units of standards the place every set consists of standards for one column, embrace a number of columns with the identical column heading. Utilizing the instance, enter:

Kind	Salesperson	Gross sales	Gross sales
	>6000	<6500
		<500

Click on a cell within the record vary. Utilizing the instance, click on any cell within the record vary A6:C10.
On the Knowledge tab, within the Kind & Filter group, click on Superior.
Do one of many following:
- To filter the record vary by hiding rows that do not match your standards, click on Filter the record, in-place.
- To filter the record vary by copying rows that match your standards to a different space of the worksheet, click on Copy to a different location, click on within the Copy to field, after which click on the upper-left nook of the world the place you wish to paste the rows.
  
  Tip: If you copy filtered rows to a different location, you’ll be able to specify which columns to incorporate within the copy operation. Earlier than filtering, copy the column labels for the columns that you simply wish to the primary row of the world the place you intend to stick the filtered rows. If you filter, enter a reference to the copied column labels within the Copy to field. The copied rows will then embrace solely the columns for which you copied the labels.
Within the Standards vary field, enter the reference for the factors vary, together with the factors labels. Utilizing the instance, enter $A$1:$D$3.

To maneuver the Superior Filter dialog field out of the way in which briefly whereas you choose the factors vary, click on Collapse Dialog .

Utilizing the instance, the filtered consequence for the record vary is:

Kind	Salesperson	Gross sales
Meat	Davolio	$450
produce	Buchanan	$6,328

A number of units of standards, a number of columns in every set

Boolean logic: ( (Salesperson = “Davolio” AND Gross sales >3000) OR (Salesperson = “Buchanan” AND Gross sales > 1500) )

Insert a minimum of three clean rows above the record vary that can be utilized as a standards vary. The factors vary will need to have column labels. Ensure that there may be a minimum of one clean row between the factors values and the record vary.

To seek out rows that meet a number of units of standards, the place every set consists of standards for a number of columns, sort every set of standards in separate columns and rows. Utilizing the instance, enter:

Kind	Salesperson	Gross sales
	=”=Davolio”	>3000
	=”=Buchanan”	>1500

Click on a cell within the record vary. Utilizing the instance, click on any cell within the record vary A6:C10.
On the Knowledge tab, within the Kind & Filter group, click on Superior.
Do one of many following:
- To filter the record vary by hiding rows that do not match your standards, click on Filter the record, in-place.
- To filter the record vary by copying rows that match your standards to a different space of the worksheet, click on Copy to a different location, click on within the Copy to field, after which click on the upper-left nook of the world the place you wish to paste the rows.
  
  Tip If you copy filtered rows to a different location, you’ll be able to specify which columns to incorporate within the copy operation. Earlier than filtering, copy the column labels for the columns that you simply wish to the primary row of the world the place you intend to stick the filtered rows. If you filter, enter a reference to the copied column labels within the Copy to field. The copied rows will then embrace solely the columns for which you copied the labels.
Within the Standards vary field, enter the reference for the factors vary, together with the factors labels. Utilizing the instance, enter $A$1:$C$3.To maneuver the Superior Filter dialog field out of the way in which briefly whereas you choose the factors vary, click on Collapse Dialog .

Utilizing the instance, the filtered consequence for the record vary can be:

Kind	Salesperson	Gross sales
produce	Buchanan	$6,328
Produce	Davolio	$6,544

Wildcard standards

Boolean logic: Salesperson = a reputation with ‘u’ because the second letter

To seek out textual content values that share some characters however not others, do a number of of the next:

Kind a number of characters with out an equal signal (=) to search out rows with a textual content worth in a column that start with these characters. For instance, should you sort the textual content Dav as a criterion, Excel finds “Davolio,” “David,” and “Davis.”

Use a wildcard character.

Use	To seek out
? (query mark)	Any single character For instance, sm?th finds “smith” and “smyth”
* (asterisk)	Any variety of characters For instance, *east finds “Northeast” and “Southeast”
~ (tilde) adopted by ?, *, or ~	A query mark, asterisk, or tilde For instance, fy91~? finds “fy91?”

Insert a minimum of three clean rows above the record vary that can be utilized as a standards vary. The factors vary will need to have column labels. Ensure that there may be a minimum of one clean row between the factors values and the record vary.

Within the rows beneath the column labels, sort the factors that you simply wish to match. Utilizing the instance, enter:

Kind	Salesperson	Gross sales
=”=Me*”
	=”=?u*”

Click on a cell within the record vary. Utilizing the instance, click on any cell within the record vary A6:C10.
On the Knowledge tab, within the Kind & Filter group, click on Superior.
Do one of many following:
- To filter the record vary by hiding rows that do not match your standards, click on Filter the record, in-place
- To filter the record vary by copying rows that match your standards to a different space of the worksheet, click on Copy to a different location, click on within the Copy to field, after which click on the upper-left nook of the world the place you wish to paste the rows.
  
  Tip: If you copy filtered rows to a different location, you’ll be able to specify which columns to incorporate within the copy operation. Earlier than filtering, copy the column labels for the columns that you simply wish to the primary row of the world the place you intend to stick the filtered rows. If you filter, enter a reference to the copied column labels within the Copy to field. The copied rows will then embrace solely the columns for which you copied the labels.
Within the Standards vary field, enter the reference for the factors vary, together with the factors labels. Utilizing the instance, enter $A$1:$B$3.

To maneuver the Superior Filter dialog field out of the way in which briefly whereas you choose the factors vary, click on Collapse Dialog .