Excel Filtering, sorting, and conditionally formatting data

Filtered data displays only the rows that meet criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) that you specify and hides rows that you do not want displayed. After you filter data, you can copy, find, edit, format, chart, and print the subset of filtered data without rearranging or moving it.

You can also filter by more than one column. Filters are additive, which means that each additional filter is based on the current filter and further reduces the subset of data.

Using AutoFilter, you can create three types of filters: by a list values, by a format, or by criteria. Each of these filter types is mutually exclusive for each range of cells or column table. For example, you can filter by cell color or by a list of numbers, but not by both; you can filter by icon or by a custom filter, but not by both.

Important For best results, do not mix storage formats, such as text and number or number and date, in the same column because only one type of filter command is available for each column. If there is a mix of storage formats, the command that is displayed is the storage format that occurs the most. For example, if the column contains three values stored as number and four as text, the filter command that is displayed is Text Filters. For more information, see Convert numbers stored as text to numbers and Convert dates stored as text to dates.

Top of Page

Filter text

Do one of the following:

Range of cells

Select a range of cells containing alphanumeric data.
On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

Table

Make sure that the active cell is in a table column that contains alphanumeric data.

Click the arrow in the column header.
Do one of the following:

Select from a list of text values

In the list of text values, select or clear one or more text values to filter by.

The list of text values can be up to 10,000. If the list is large, clear (Select All) at the top, and then select the specific text values to filter by.

Tip To make the AutoFilter menu wider or longer, click and drag the grip handle at the bottom.

Create criteria

Point to Text Filters and then click one of the comparison operator (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.) commands, or click Custom Filter.

For example, to filter by text that begins with a specific character, select Begins With, or to filter by text that has specific characters anywhere in the text, select Contains.

In the Custom AutoFilter dialog box, in the box on the right, enter text or select the text value from the list.

For example, to filter by text that begins with the letter "J", enter J, or to filter by text that has "bell" anywhere in the text, enter bell.

If you need to find text that shares some characters but not others, use a wildcard character.

How to use wildcard characters

Use	To find
? (question mark)	Any single character For example, sm?th finds "smith" and "smyth"
* (asterisk)	Any number of characters For example, *east finds "Northeast" and "Southeast"
~ (tilde) followed by ?, *, or ~	A question mark, asterisk, or tilde For example, fy06~? finds "fy06?"

Optionally, filter by one more criteria.

How to add one more criteria

Do one of the following:
- To filter the table column or selection so that both criteria must be true, select And.
- To filter the table column or selection so that either or both criteria can be true, select Or.
In the second entry, select a comparison operator, and then in the box on the right, enter text or select a text value from the list.

Top of Page

Filter numbers

Do one of the following:

Range of cells

Select a range of cells containing numeric data.
On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

Table

Make sure that the active cell is in a table column that contains numeric data.

Click the arrow in the column header.
Do one of the following:

Select from a list of numbers

In the list of numbers, select or clear one or more numbers to filter by.

The list of numbers can be up to 10,000. If the list is large, clear (Select All) at the top, and then select the specific numbers to filter by.

Tip To make the AutoFilter menu wider or longer, click and drag the grip handle at the bottom.

Create criteria

Point to Number Filters and then click one of the comparison operator (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.) commands or click Custom Filter.

For example, to filter by a lower and upper number limit, select Between.

In the Custom AutoFilter dialog box, in the box or boxes on the right, enter numbers or select numbers from the list.

For example, to filter by a lower number of 25 and an upper number of 50, enter 25 and 50.

Optionally, filter by one more criteria.

How to add one more criteria

Do one of the following:
- To filter the table column or selection so that both criteria must be true, select And.
- To filter the table column or selection so that either or both criteria can be true, select Or.
In the second entry, select a comparison operator, and then in the box on the right, enter a number or select a number from the list.

Top of Page

Filter dates or times

Do one of the following:

Range of cells

Select a range of cells containing numeric data.
On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

Table

Make sure that the active cell is in a table column that contains dates or times.

Click the arrow in the column header.
Do one of the following:

Select from a list of dates or times

In the list of dates or times, select or clear one or more dates or times to filter by.

By default, all dates in the range of cells or table column are grouped by a hierarchy of years, months, and days. Selecting or clearing a higher level in the hierarchy selects or clears all nested dates below that level. For example, if you select 2006, months are listed below 2006, and days are listed below each month.

The list of values can be up to 10,000. If the list of values is large, clear (Select All) at the top, and then select the values to filter by.

Tip To make the AutoFilter menu wider or longer, click and drag the grip handle at the bottom.

Create criteria

Point to Date Filters and then do one of the following:

Common filter

Note A common filter is one based on a comparison operator (comparison operator: A sign that is used in comparison criteria to compare two values. Operators include: = Equal to, > Greater than, < Less than, >= Greater than or equal to, <= Less than or equal to, and <> Not equal to.).

Click one of the comparison operator commands (Equals, Before, After, or Between) or click Custom Filter.
In the Custom AutoFilter dialog box, in the box on the right, enter a date or time, select a date or time from the list, or click the Calendar button to find and enter a date.

For example, to filter by a lower and upper date or time, select Between.

In the Custom AutoFilter dialog box, in the box or boxes on the right, enter a date or time, select dates or times from the list, or click the Calendar button to find and enter a date.

For example, to filter by an earlier date of "3/1/2006" and a later date of "6/1/2006", enter 3/1/2006 and 6/1/2006. Or, to filter by an earlier time of "8:00 AM" and a later time of "12:00 PM", enter 8:00 AM and 12:00 PM.

Dynamic filter

Note A dynamic filter is one where the criteria can change when you reapply the filter.

Click one of the pre-defined date commands.

For example, to filter all dates by the current date, select Today, or by the following month, select Next Month.

Click OK.

Notes

The commands under the All Dates in the Period menu, such as January or Quarter 2, filter by the period no matter what the year. This can be useful, for example, to compare sales by a period across several years.
This Year and Year to Date are different in the way that future dates are handled. This Year can return dates in the future for the current year, whereas Year to Date only returns dates up to and including the current date.

Optionally, filter by one more criteria.

How to add one more criteria

Do one of the following:
- To filter the table column or selection so that both criteria must be true, select And.
- To filter the table column or selection so that either or both criteria can be true, select Or.
In the second entry, select a comparison operator, and then in the box on the right, enter a date or time, select a date or time from the list, or click the Calendar button to find and enter a date.

Notes

All date filters are based on the Gregorian calendar.
Fiscal years and fiscal quarters always start in January of the calendar year.
If you want to filter by days of the week, format the cells to show the day of the week. If you want to filter by the day of the week regardless of the date, convert them to text by using the TEXT function. However, the TEXT function returns a text value, and so the filter command that is displayed would be Text Filters, not Date Filters. For more information, see Show dates as days of the week.

Top of Page

Filter for top or bottom numbers

Do one of the following:

Range of cells

Select a range of cells containing numeric data.
On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

Table

Make sure that the active cell is in a table column that contains numeric data.

Click the arrow in the column header.
Point to Number Filters and then select Top 10.
In the Top 10 AutoFilter dialog box, do the following.
1. In the box on the left, click Top or Bottom.
2. In the box in the middle, enter a number.
3. In the box on the right, do one of the following:
  - To filter by number, click Items.
  - To filter by percentage, click Percent.

Note Top and bottom values are based on the original range of cells or table column and not the filtered subset of data.

Top of Page

Filter for above or below average numbers

Do one of the following:

Range of cells

Select a range of cells containing numeric data.
On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

Table

Make sure that the active cell is in a table column that contains numeric data.

Click the arrow in the column header.
Point to Number Filters and then do one or more of the following:
1. To filter by numbers that are above the average, click Above Average.
2. To filter by numbers that are below the average, click Below Average.

Note Above and below average numbers are based on the original range of cells or table column and not the filtered subset of data.

Top of Page

Filter for blanks or nonblanks

Do one of the following:

Range of cells

Select a range of cells.
On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

Table

Make sure that the active cell is in a table column.

Click the arrow in the column header.
Do one of the following:
1. To filter for nonblanks, in the AutoFilter menu at the top of the list of values, select (Select All), and then at the bottom of the list of values, clear (Blanks).
2. To filter for blanks, in the AutoFilter menu at the top of the list of values, clear (Select All), and then at the bottom of the list of values, select (Blanks).

Note The (Blanks) check box is available only if the range of cells or table column contains at least one blank cell.

Top of Page

Filter by cell color, font color, or icon set

If you have manually or conditionally formatted a range of cells, by cell color or font color, you can also filter by these colors. You can also filter by an icon set created through a conditional format.

Do one of the following:

Range of cells

Select a range of cells containing formatted by cell color, font color, or an icon set.
On the Home tab, in the Editing group, click Sort & Filter, and then click Filter.

Table

Make sure that the table column contains data formatted by cell color, font color, or an icon set (No selection is required).

Click the arrow in the column header.
Select Filter by Color, and then depending on the type of format, select Filter by Cell Color, Filter by Font Color, or Filter by Cell Icon.
Depending on the type of format, select a color, font color, or cell icon.

Top of Page

Filter by selection

You can quickly filter data with criteria that is equal to the contents of the active cell.

In a range of cells or table column, right click a cell containing the value, color, font color, or icon you want to filter by.
Click Filter, and then do one of the following:
- To filter by text, number, or date or time, click Filter by Selected Cell's Value.
- To filter by cell color, click Filter by Selected Cell's Color.
- To filter by font color, click Filter by Selected Cell's Font Color.
- To filter by icon, click Filter by Selected Cell's Icon.

Top of Page

Ungroup the hierarchy of dates in the AutoFilter menu

For the list of dates at the bottom of the AutoFilter menu in a date filter, you can change the hierarchical grouping of dates to a nonhierarchical list of dates. For example, you can filter for just two-digit years by manually selecting them from a nonhierarchical list.

Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
In the Display options for this workbook section, select a workbook, and then clear the Group dates in the AutoFilter menu check box.

Top of Page

Filter for unique values or remove duplicate values

Excel 2007

In Microsoft Office Excel 2007, you have several ways to filter for unique values or remove duplicate values:

To filter for unique values, use the Advanced command in the Sort & Filter group on the Data tab.
To remove duplicate values, use the Remove Duplicates command in the Data Tools group on the Data tab.
To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab.

What do you want to do?

Learn about filtering for unique values or removing duplicate values

Filter for unique values

Remove duplicate values

Conditionally format unique or duplicate values

Learn about filtering for unique values or removing duplicate values

Filtering for unique values and removing duplicate values are two closely related tasks because the displayed results are the same — a list of unique values. The difference, however, is important: When you filter for unique values, you temporarily hide duplicate values, but when you remove duplicate values, you permanently delete duplicate values.

A duplicate value is one where all values in the row are an exact match of all the values in another row. Duplicate values are determined by the value displayed in the cell and not necessarily the value stored in the cell. For example, if you have the same date value in different cells, one formatted as "3/8/2006" and the other as "Mar 8, 2006", the values are unique.

It's a good idea to filter for or conditionally format unique values first to confirm that the results are what you want before removing duplicate values.

Top of Page

Filter for unique values

Select the range of cells, or make sure the active cell is in a table.
On the Data tab, in the Sort & Filter group, click Advanced.

In the Advanced Filter dialog box, do one of the following:
1. To filter the range of cells or table in place, click Filter the list, in-place.
2. To copy the results of the filter to another location, do the following:
  1. Click Copy to another location.
  2. In the Copy to box, enter a cell reference.

Alternatively, click Collapse Dialog to temporarily hide the dialog box, select a cell on the worksheet, and then press Expand Dialog .

Select the Unique records only check box, and click OK.

The unique values from the selected range are copied to the new location.

Top of Page

Remove duplicate values

When you remove duplicate values, only the values in the range of cells or table are affected. Any other values outside the range of cells or table are not altered or moved.

Caution Because you are permanently deleting data, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.

Select the range of cells, or make sure that the active cell is in a table.
On the Data tab, in the Data Tools group, click Remove Duplicates.

Do one or more of the following:
- Under Columns, select one or more columns.
- To quickly select all columns, click Select All.
- To quickly clear all columns, click Unselect All.

If the range of cells or table contains many columns and you want to only select a few columns, you may find it easier to click Unselect All, and then under Columns, select those columns.

Click OK.

A message is displayed indicating how many duplicate values were removed and how many unique values remain, or if no duplicate values were removed.

Click OK.

Issue: I'm having problems removing duplicate values from data that is outlined or that has subtotals.

Top of Page

Conditionally format unique or duplicate values

Note You cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.

Quick formatting

Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules.

Select Duplicate Values.
Enter the values that you want to use, and then select a format.

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

Do one of the following:
- To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

To change a conditional format, do the following:
1. Make sure that the appropriate worksheet or table is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

Under Select a Rule Type, click Format only unique or duplicate values.
Under Edit the Rule Description, in the Format all list box, select unique or duplicate.
Click Format to display the Format Cells dialog box.
Select the number, font, border, or fill format that you want to apply when the cell value meets the condition, and then click OK.

You can choose more than one format. The formats that you select are displayed in the Preview box.

Top of Page

See Also

Add, change, or clear conditional formats

Excel > Filtering, sorting, and conditionally formatting data

Filter by using advanced criteria

Excel 2007

To filter a range of cells by using complex criteria (criteria: Conditions you specify to limit which records are included in the result set of a query. For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000: Order Amount > 30000.), use the Advanced command in the Sort & Filter group on the Data tab.

Overview of advanced filtering

Filter by using multiple criteria in one column where any criteria can be true

Filter by using multiple criteria in multiple columns where all criteria must be true

Filter by using multiple criteria in multiple columns where any criteria can be true

Filter by using multiple sets of criteria where each set includes criteria for multiple columns

Filter by using multiple sets of criteria where each set includes criteria for one column

Filter by using wildcard criteria to find text values that share some characters but not others

Filter by using a formula for values greater than the average of all values in the data range

Filter by using a formula for text in a case-sensitive search

Overview of advanced filtering

The Advanced command works differently from the Filter command in several important ways.

It displays the Advanced Filter dialog box instead of the AutoFilter menu.
You type the advanced criteria in a separate criteria range on the worksheet and above the range of cells or table you want to filter. Microsoft Office Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the advanced criteria.

Example: Criteria range (A1:C4) and data range (A6:C10) used for the following procedures

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7
8
9
10

A	B	C
Type	Salesperson	Sales




Type	Salesperson	Sales
Beverages	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

Using the equal sign to type text or a value

Because the equal sign (=) is used to indicate a formula when you type text or a value in a cell, Excel evaluates what you type; however, this may cause unexpected filter results. To indicate an equality comparison operator for either text or a value, type the criteria as a string expression in the appropriate cell in the criteria range:

=''=entry''

Where entry is the text or value you want to find. For example:

What you type in the cell	What Excel evaluates and displays
="=Davolio"	=Davolio
="=3000"	=3000

Considering case-sensitivity

When filtering text data, Excel does not distinguish between uppercase and lowercase characters. However, you can use a formula to perform a case-sensitive search. For an example, see

Filter by using wildcard criteria to find text values that share some characters but not others

Using pre-defined names

You can name a range Criteria, and the reference for the range will appear automatically in the Criteria range box. You can also define the name Database for the range of data to be filtered and define the name Extract for the area where you want to paste the rows, and these ranges will appear automatically in the List range and Copy to boxes, respectively.

Creating criteria by using a formula

You can use a calculated value that is the result of a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) as your criterion. Remember the following important points:

The formula must evaluate to TRUE or FALSE.
Because you are using a formula, enter the formula as you normally would, and do not type the expression in the following way:

=''=entry''

Do not use a column label for criteria labels; either keep the criteria labels blank or use a label that is not a column label in the range (in the examples below, Calculated Average and Exact Match).

If you use a column label in the formula instead of a relative cell reference or a range name, Excel displays an error value such as #NAME? or #VALUE! in the cell that contains the criterion. You can ignore this error because it does not affect how the range is filtered.

The formula that you use for criteria must use a relative reference (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) to refer to the corresponding cell in the first row. In the example, Filter by using a formula for values greater than the average of all values in the data range, you would use C7, and in the example, Filter by using a formula for text in a case-sensitive search, you would use A7).
All other references in the formula must be absolute references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.).

Top of Page

Filter by using multiple criteria in one column where any criteria can be true

Boolean logic: (Salesperson = "Davolio" OR Salesperson = "Buchanan")

Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7
8
9
10

A	B	C
Type	Salesperson	Sales




Type	Salesperson	Sales
Beverages	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

To find rows that meet multiple criteria for one column, type the criteria directly below each other in separate rows of the criteria range. In the example, you would enter:

	A	B	C
1	Type	Salesperson	Sales
2		="=Davolio"
3		="=Buchanan"

Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.

Do one of the following:
1. To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
2. To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

Tip When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.

In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $A$1:$C$3.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .

In the example, the filtered result for the range of data would be:

	A	B	C
6	Type	Salesperson	Sales
8	Meat	Davolio	$450
9	produce	Buchanan	$6,328
10	Produce	Davolio	$6,544

Top of Page

Filter by using multiple criteria in multiple columns where all criteria must be true

Boolean logic: (Type = "Produce" AND Sales > 1000)

Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7
8
9
10

A	B	C
Type	Salesperson	Sales




Type	Salesperson	Sales
Beverages	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

To find rows that meet multiple criteria in multiple columns, type all of the criteria in the same row of the criteria range. In the example, you would enter:

	A	B	C
1	Type	Salesperson	Sales
2	="=Produce"		>1000

Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.

Do one of the following:
1. To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
2. To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $A$1:$C$2.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .

In the example, the filtered result for the range of data would be:

	A	B	C
6	Type	Salesperson	Sales
9	produce	Buchanan	$6,328
10	Produce	Davolio	$6,544

Top of Page

Filter by using multiple criteria in multiple columns where any criteria can be true

Boolean logic: (Type = "Produce" OR Salesperson = "Davolio")

Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7
8
9
10

A	B	C
Type	Salesperson	Sales




Type	Salesperson	Sales
Beverages	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

To find rows that meet multiple criteria in multiple columns, where any criteria can be true, type the criteria in the different columns and rows of the criteria range. In the example, you would enter:

	A	B	C
1	Type	Salesperson	Sales
2	="=Produce"
3		="=Buchanan"

Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.

Do one of the following:
1. To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
2. To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $A$1:$B$3.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .

In the example, the filtered result for the range of data would be:

	A	B	C
6	Type	Salesperson	Sales
9	produce	Buchanan	$6,328
10	Produce	Davolio	$6,544

Top of Page

Filter by using multiple sets of criteria where each set includes criteria for multiple columns

Boolean logic: ( (Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500) )

Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7
8
9
10

A	B	C
Type	Salesperson	Sales




Type	Salesperson	Sales
Beverages	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

To find rows that meet multiple sets of criteria, where each set includes criteria for multiple columns, type each set of criteria in separate columns and rows. In the example, you would enter:

	A	B	C
1	Type	Salesperson	Sales
2		="=Davolio"	>3000
3		="=Buchanan"	>1500

Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.

Do one of the following:
1. To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
2. To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $A$1:$C$3.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .

In the example, the filtered result for the range of data would be:

	A	B	C
6	Type	Salesperson	Sales
9	produce	Buchanan	$6,328
10	Produce	Davolio	$6,544

Top of Page

Filter by using multiple sets of criteria where each set includes criteria for one column

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

Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7
8
9
10

A	B	C
Type	Salesperson	Sales




Type	Salesperson	Sales
Beverages	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

To find rows that meet multiple sets of criteria, where each set includes criteria for one column, include multiple columns with the same column heading. In the example, you would enter:

	A	B	C	D
1	Type	Salesperson	Sales	Sales
2			>6000	<6500
3			<500

Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.

Do one of the following:
1. To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
2. To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $A$1:$D$3.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .

In the example, the filtered result for the range of data would be:

	A	B	C
6	Type	Salesperson	Sales
8	Meat	Davolio	$450
9	produce	Buchanan	$6,328

Top of Page

Filter by using wildcard criteria to find text values that share some characters but not others

To find text values that share some characters but not others, do one or more of the following:

Type one or more characters without an equal sign (=) to find rows with a text value in a column that begin with those characters. For example, if you type the text Dav as a criterion, Excel finds "Davolio," "David," and "Davis."
Use a wildcard character.

Use	To find
? (question mark)	Any single character For example, sm?th finds "smith" and "smyth"
* (asterisk)	Any number of characters For example, *east finds "Northeast" and "Southeast"
~ (tilde) followed by ?, *, or ~	A question mark, asterisk, or tilde For example, fy91~? finds "fy91?"

Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7
8
9
10

A	B	C
Type	Salesperson	Sales




Type	Salesperson	Sales
Beverages	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

In the rows below the column labels, type the criteria that you want to match. In the example, you would enter:

	A	B	C
1	Type	Salesperson	Sales
2	Me
3		="=?u*"

Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.

Do one of the following:
1. To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
2. To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $A$1:$B$3.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .

In the example, the filtered result for the range of data would be:

	A	B	C
6	Type	Salesperson	Sales
7	Beverages	Suyama	$5,122
8	Meat	Davolio	$450
9	produce	Buchanan	$6,328

Top of Page

Filter by using a formula for values greater than the average of all values in the data range

Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7
8
9
10

A	B	C
Type	Salesperson	Sales




Type	Salesperson	Sales
Beverages	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

In the rows below the column labels, type the criteria that you want to match as a formula that finds a value in the Sales column greater than the average of all the Sales values. In the example, you would enter:

	A	B	C	D
1	Type	Salesperson	Sales	Calculated Average
2				=C7>AVERAGE($C$7:$C$10)

Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.

Do one of the following:
1. To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
2. To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $D$1:$D$2.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .

In the example, the filtered result for the range of data would be:

	A	B	C
6	Type	Salesperson	Sales
7	Beverages	Suyama	$5,122
9	produce	Buchanan	$6,328
10	Produce	Davolio	$6,544

Top of Page

Filter by using a formula for text in a case-sensitive search

Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the range.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7
8
9
10

A	B	C
Type	Salesperson	Sales




Type	Salesperson	Sales
Beverages	Suyama	$5122
Meat	Davolio	$450
produce	Buchanan	$6328
Produce	Davolio	$6544

In the rows below the column labels, type the criteria that you want to match as a formula by using the EXACT function to perform a case-sensitive search . In the example, you would enter:

	A	B	C	D
1	Type	Salesperson	Sales	Exact Match
2				=EXACT(A7, "Produce")

Click a cell in the range. In the example, you would click any cell in the range, A6:C10.
On the Data tab, in the Sort & Filter group, click Advanced.

Do one of the following:
1. To filter the range by hiding rows that don't match your criteria, click Filter the list, in-place.
2. To filter the range by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the area where you want to paste the rows.

In the Criteria range box, enter the reference for the criteria range, including the criteria labels. In the example, you would enter $D$1:$D$2.

To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .

In the example, the filtered result for the range of data would be:

	A	B	C
6	Type	Salesperson	Sales
10	Produce	Davolio	$6,544

Reapply a filter and sort, or clear a filter

Once you have filtered or sorted data in a range of cells or table column, you can either reapply a filter or sort to get up-to-date results, or clear a filter to redisplay all of the data.

What do you want to do?

Learn more about reapplying a filter and sort

Reapply a filter or sort

Clear a filter for a column

Clear all filters in a worksheet and redisplay all rows

Learn more about reapplying a filter and sort

To determine if a filter is applied, note the icon in the column heading:

A drop-down arrow means that filtering is enabled but not applied.

Tip When you hover over the heading of a column with filtering enabled but not applied, a screen tip displays "(Showing All)".

A Filter button means that a filter is applied.

Tip When you hover over the heading of a filtered column, a screen tip displays the filter applied to that column, such as "Equals a red cell color" or "Larger than 150".

When you reapply a filter or sort, different results appear for the following reasons:

Data has been added, modified, or deleted to the range of cells or table column.
The filter is a dynamic date and time filter, such as Today, This Week, or Year to Date.
Values returned by a formula have changed and the worksheet has been recalculated.

Note When you use the Find dialog box to search filtered data, only the data that is displayed is searched; data that is not displayed is not searched. To search all the data, clear all filters.

Top of Page

Reapply a filter or sort

Note For a table, filter and sort criteria are saved with the workbook so that you can reapply both the filter and sort each time that you open the workbook. However, for a range of cells, only filter criteria are saved with a workbook, not sort criteria. If you want to save sort criteria so that you can periodically reapply a sort when you open a workbook, then it's a good idea to use a table. This is especially important for multicolumn sorts or for sorts that take a long time to create.

To reapply a filter or sort, on the Home tab, in the Editing group, click Sort & Filter, and then click Reapply.

Top of Page

Clear a filter for a column

To clear a filter for one column in a multicolumn range of cells or table, click the Filter button on the heading, and then click Clear Filter from <Column Name>.

Top of Page

Clear all filters in a worksheet and redisplay all rows

On the Home tab, in the Editing group, click Sort & Filter, and then click Clear.

Top of Page

Excel > Filtering, sorting, and conditionally formatting data

Guidelines and examples for sorting and filtering data by color

Excel 2007

Sorting and filtering data by color is an eagerly anticipated new feature in Microsoft Office Excel 2007. The following guidelines and examples can help you shine a brighter light on your Office Excel 2007 reports.

Don’t have the 2007 Office release? You can download a free 60-day trial or buy it now.

When white light passes through a color spectrum, you see six colors: red, orange, yellow, green, blue, and violet.

In this article

Overview of sorting and filtering data by color and icon set

Using color effectively when analyzing data

Choosing the best colors for your needs

Walking through some examples

Overview of sorting and filtering data by color and icon set

Sorting and filtering data, along with conditionally formatting data, are integral parts of data analysis and can help you answer questions such as the following:

Who has sold more than $50,000 dollars worth of services this month?
Which products have greater than 10% revenue increases from year to year?
Who are the highest performing and lowest performing students in the freshman class?
Where are the exceptions in a summary of profits over the past five years?
What is the overall age distribution of employees?

You sort data to quickly organize your data and to find the data that you want. You filter data to display only the rows that meet criteria that you specify and hide rows that you do not want displayed, for one or more columns of data. You conditionally format data to help you visually explore and analyze data, detect critical issues, and identify patterns and trends. Together, sorting, filtering, and conditionally formatting data can help you and your users make more effective decisions based on your data.

Using Office Excel 2007, you can sort and filter by format, including cell color and font color, whether you have manually or conditionally formatted the cells.

You can also sort and filter by an icon set that you created through a conditional format. Use an icon set to annotate and classify data into three to five categories that are separated by a threshold value. Each icon represents a range of values. For example in the following table of icon sets, 3 Arrows icon set, the green arrow that points upward represents higher values, the yellow sideways arrow represents middle values, and the red arrow that points downward represents lower values.

Table of icon sets

In general, the conditional formatting feature has been greatly expanded in Office Excel 2007. You can: format cells by using a two-color scale, three-color scale, data bars, and icon sets; format cells that contain specific text, number, date or time values, top or bottom ranked values, above or below average, unique, or duplicate values; and create many rules and manage rules more easily.

Find links to more information about sorting, filtering, and conditionally formatting data in the See Also section.

Top of Page

Using color effectively when analyzing data

Almost everyone likes colors. The effective use of color in any document can dramatically improve the document's attractiveness and readability. Good use of color and icons in your Excel reports improves decision making by helping to focus users' attention on critical information and by helping users visually understand results. Good use of colors can provide a positive emotional feeling right from the start. On the other hand, bad use of color can distract users, and even cause fatigue if over-used. The following sections provide guidelines to help you make good use of colors, and to avoid bad use of colors.

More about document themes With Office Excel 2007, it is easy to create consistent themes, and add custom styles and effects. Much of the thought that is required to combine colors effectively has already been done for you by the use of predefined document themes that use attractive color schemes. You can quickly and easily format an entire document to give it a professional and modern look by applying a document theme. A document theme is a set of formatting choices that includes a set of theme colors, a set of theme fonts (including heading and body text fonts), and a set of theme effects (including lines and fill effects). Find links to more information about themes in the See Also section.

Use standard colors and limit the number of colors

When you sort and filter by color, you might choose colors that you prefer, and the results may look good to you. But, a critical question that needs to be asked is, "Do your users prefer and see the same colors?" Your computer is capable of displaying 16,777,216 colors in 24-bit color mode. However, most users can only distinguish a tiny fraction of these colors. Furthermore, color quality can vary on computers. Room lighting, paper quality, screen and printer resolution, and browser settings all can be different. Up to 10% of the population has some difficulty distinguishing and seeing some colors. These are important variables that you probably don't have control over.

But you do have control over such variables as color choice, the number of colors, and the worksheet or cell background. By making good choices based on fundamental research, you can help make your colors communicate the correct message and interpretation of your data. You can also supplement colors with icons and legends to help ensure that users understand your intended meaning.

Consider color contrast and background

In general, use colors with a high color saturation, such as bright yellow, medium green, or dark red. Make sure that the contrast is high between the background and the foreground. For example, use a white or gray worksheet background with cell colors, or a white or gray cell color with a font color. If you must use a background color or picture, make the color or picture as light as possible so that the cell or font color is not washed out. If you are relying just on font color, consider increasing the size of the font or setting the font in bold. The larger the font, the easier it is for a user to see or distinguish the color. If necessary, adjust or remove the banding or rows or columns because the banding color might interfere with the cell or font color. All of these considerations go a long way towards helping all users correctly understand and interpret color.

Avoid using color combinations that might decrease the color visibility or confuse the viewer. You don't want to inadvertently create eye-popping art or an optical illusion. Consider using a cell border to distinguish problematic colors, such as red and green, if it is unavoidable to prevent the colors from being next to each other. Use complementary and contrasting colors to enhance contrast, and avoid using similar colors. It pays to know the basic color wheel and how to determine similar, contrasting, and complementary colors.

A similar color is one next to another color on the color wheel (for example, violet and orange are similar colors to red).

A contrasting color is three colors away from a color (for example, blue and green are contrasting colors to red).

Complementary colors are opposite each other on the color wheel (for example, blue-green is the complementary color of red).

If you have time, test out your colors, run them by a few colleagues, try them out in different lighting conditions, and experiment with different computer screen and printer settings.

Tip If you print the document in color, double-check the cell color and cell font for readability. If the cell color is too dark, consider using a white font to improve readability.

Top of Page

Choosing the best colors for your needs

Need a quick summary? Use red, yellow, green, or blue, with a white or gray background.

Assign meaning to the colors that you choose based on your audience and intended purpose. If necessary, provide a legend to specifically clarify the meaning of each color. Most people can easily distinguish seven to ten colors in the same worksheet. Up to 50 colors are possible to distinguish, but would require specialized training, and is beyond the scope of this article.

The Top 10 colors

When you sort and filter data by color, use the following table to help you decide which colors to choose. These colors provide the most dramatic contrast, and, in general, are the easiest for most people to distinguish.

You can easily apply these colors to cells and fonts by using the Fill Color or Font Color buttons in the Font group on the Home tab.

Using colors that naturally convey meaning

When reading financial data, numbers are either in the red (negative) or in the black (positive). A red color conveys meaning because it is an accepted convention. If you want to highlight negative numbers, then red is a top color choice. Depending on what type of data that you have, you may be able to use specific colors because they convey meaning to your audience, or perhaps there is an accepted standard for their meaning. For example:

If your data is about temperature readings, you could use the warm colors (red, yellow, and orange) to indicate a hotter temperature, and the cool colors (green, blue, and violet) to indicate colder temperatures.
If your data is about topographical data, you could use blue for water, green for vegetation, brown for desert and mountains, and white for ice and snow.
If your data is about traffic and safety, you could use red for stopped or halted conditions, orange for equipment danger, yellow for caution, green for safety, and blue for general information.
If your data is about electrical resistors, you could use the standard color code of black, brown, red, orange, yellow, green, blue, violet, gray, and white.

Top of Page

Walking through some examples

Let us say that you are preparing a set of reports on product descriptions, pricing, and inventory levels. The following sections illustrate questions that you typically ask about this data, and how you can answer each question by using color and icon sets.

Sample Data

The following sample data is used in the examples.

To copy the data to a blank workbook, do the following:

How to save the sample data as a .xlsx file

Start Microsoft Notepad.
Select the sample text, and then copy and paste the sample text to Notepad.
Save the file with a file name and extension such as Products.csv.
Exit Notepad.
Start Office Excel 2007.
Open the file you saved from Notepad.
Save the file as a .xlsx file.

Sample data

What are the different types of product packaging?

Problem

You want to find out the different types of containers for your products, but there is no Container column. You can use the Quantity Per Unit column to manually color each cell, and then sort by color. You can also add a legend to clarify to the user what each color means.

Results

Solution

To manually color each cell according to the color scheme in the preceding table, click each cell, and then apply each color by using the Fill Color button in the Font group on the Home tab.

Tip Use the Format Painter button in the Clipboard group on the Home tab to quickly apply a selected color to another cell.

Click a cell in the Quantity Per Unit column, and on the Home tab in the Editing group, click Sort & Filter, and then click Custom Sort.
In the Sort dialog box, select Quantity Per Unit under Column, select Cell Color under Sort On, and then click Copy Level twice.
Under Order, in the first row, select the red color, in the second row, select the blue color, and in the third row, select the yellow color.

If a cell does not contain any of the colors, such as the cells colored white, those rows remain in place.

Note The colors that are listed are the available colors in the column. There is no default color sort order, and you cannot create a custom sort order by using a custom list.

Add a legend using cells on the side of the report by using the following table as a guide.

Legend:
Red	Packages and boxes

Blue	Cans and Tins

Green	Jars and Bottles

White	(Not sure)

Which products have a markup above 67% or below 34%?

Problem

You want to quickly see the highest and lowest markup values at the top of the report.

Results

Solution

Select cells E2:E26, and on the Home tab, in the Style group, click the arrow next to Conditional Formatting, click Icon Set, and then select the Three Arrows (Colored) icon set.
Right-click a cell in the Markup column, point to Sort, and then click Custom Sort.
In the Sort dialog box, select Markup under Column, select Cell Icon under Sort On, and then click Copy Level.
Under Order, in the first row, select the green arrow that points upward, and in the second row, select the red arrow that points upward.

Which products need to be reordered right away?

Problem

You want to quickly generate a report of products that must be reordered right away, and then mail the report to your staff.

Results

Solution

Select cells I2:I26, and on the Home tab, in the Style group, click the arrow next to Conditional Formatting, point to Highlight Cells Rules, and then click Equal To.
Enter Yes in the first box, and then select Light Red Fill with Dark Red Text from the second box.
Right-click any formatted cell in the column, point to Filter, and then select Filter By Selected Cell's Color.

Tip Hover over the Filter button in the column header to see how the column is filtered.

Which products have the highest and lowest prices and costs?

Problem

You want to see the highest and lowest prices and costs grouped together at the top of the report.

Results

Solution

For cells C2:C26 and D2:D26, do the following:
- On the Home tab, in the Style group, click the arrow next to Conditional Formatting, point to Top/Bottom Rules, and then click Top 10 Items.
- Enter 1 in the first box, and then select Yellow Fill with Dark Yellow Text from the second box.
- On the Home tab, in the Style group, click the arrow next to Conditional Formatting, point to Top/Bottom Rules, and then click Bottom 10 Items.
- Enter 1 in the first box, and then select Green Fill with Dark Green Text from the second box.
For the Cost and Price columns, do the following:
- Right-click the lowest value , point to Sort, and then select Sort By Selected Cell's Color.
- Right-click the highest value, point to Sort, and then select Sort By Selected Cell's Color.

Top of Page

Sort data in a range or table

Excel 2007

Tags column headings; sort

What are tags?

Sorting data is an integral part of data analysis. You might want to put a list of names in alphabetical order, compile a list of product inventory levels from highest to lowest, or order rows by colors or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.

Note To find the top or bottom values in a range of cells or table, such as top 10 grades or bottom 5 sales amounts, use AutoFilter or conditional formatting. For more information, see Filter data in a range or table and Add, change, or clear conditional formats.

What do you want to do?

Sort by cell color, font color, or icon

Sort by a custom list

Sort by a partial value in a column

Sort rows

Sort by more than column or row

Sort one column in a range of cells without affecting the others

Learn more about general issues with sorting

Learn about sorting

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by a custom list (such as Large, Medium, and Small) or by format, including cell color, font color, or icon set. Most sort operations are column sorts, but you can also sort by rows.

Sort criteria are saved with the workbook so that you can reapply the sort each time that you open the workbook for an Excel table, but not for a range of cells. If you want to save sort criteria so that you can periodically reapply a sort when you open a workbook, then it's a good idea to use a table. This is especially important for multicolumn sorts or for sorts that take a long time to create.

Top of Page

Sort text

Select a column of alphanumeric data in a range of cells, or make sure that the active cell is in a table column containing alphanumeric data.
On the Home tab, in the Editing group, and then click Sort & Filter.

Do one of the following:
- To sort in ascending alphanumeric order, click Sort A to Z.
- To sort in descending alphanumeric order, click Sort Z to A.
Optionally, you can do a case-sensitive sort.

How to sort by case-sensitivity

On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.
In the Sort dialog box, click Options.
In the Sort Options dialog box, select Case sensitive.
Click OK twice.

Issue: Check that all data is stored as text If the column that you want to sort contains numbers stored as numbers and numbers stored as text, then you need to format them all as text. If you do not, the numbers stored as numbers are sorted before the numbers stored as text. To format all of the selected data as text, on the Home tab, in the Font group, click the Format Cell Font button, click the Number tab, and then under Category, click Text.

Issue: Remove any leading spaces In some cases, data imported from another application might have leading spaces inserted before data. Remove the leading spaces before sorting the data.

Top of Page

Sort numbers

Select a column of numeric data in a range of cells, or make sure that the active cell is in a table column containing numeric data.
On the Home tab, in the Editing group, click Sort & Filter, and then do one of the following:
- To sort from low numbers to high numbers, click Sort Smallest to Largest.
- To sort from high numbers to low numbers, click Sort Largest to Smallest.

Issue: Check that all numbers are stored as numbers If the results are not what you expected, the column might contain numbers stored as text and not as numbers. For example, negative numbers imported from some accounting systems or a number entered with a leading ' (apostrophe) are stored as text. For more information, see Convert numbers stored as text to numbers.

Top of Page

Sort dates or times

Select a column of dates or times in a range of cells, or make sure that the active cell is in a table column containing dates or times.
Select a column of dates or times in a range of cells or table.
On the Home tab, in the Editing group, click Sort & Filter, and then do one of the following:
- To sort from an earlier to a later date or time, click Sort Oldest to Newest.
- To sort from a later to an earlier date or time, click Sort Newest to Oldest.

Issue: Check that dates and times are stored as dates or times If the results are not what you expected, the column might contain dates or times stored as text and not as dates or times. For Excel to sort dates and times correctly, all dates and times in a column must be stored as a date or time serial number. If Excel cannot recognize a value as a date or time, the date or time is stored as text. For more information, see Convert dates stored as text to dates.

Note If you want to sort by days of the week, format the cells to show the day of the week. If you want to sort by the day of the week regardless of the date, convert them to text by using the TEXT function. However, the TEXT function returns a text value, and so the sort operation would be based on alphanumeric data. For more information, see Show dates as days of the week.

Top of Page

Sort by cell color, font color, or icon

If you have manually or conditionally formatted a range of cells or table column, by cell color or font color, you can also sort by these colors. You can also sort by an icon set created through a conditional format.

Select a column of data in a range of cells, or make sure that the active cell is in a table column.
On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.

The Sort dialog box is displayed.

Under Column, in the Sort by box, select the column that you want to sort.
Under Sort On, select the type of sort. Do one of the following:
- To sort by cell color, select Cell Color.
- To sort by font color, select Font Color.
- To sort by an icon set, select Cell Icon.
Under Order, click the arrow next to the button, and then, depending on the type of format, select a cell color, font color, or cell icon.
Under Order, select how you want to sort. Do one of the following:
- To move the cell color, font color, or icon to the top or left, select On Top for a column sort, and On Left for a row sort.
- To move the cell color, font color, or icon to the bottom or right, select On Bottom for a column sort, and On Right for a row sort.

Note There is no default cell color, font color, or icon sort order. You must define the order that you want for each sort operation.

To specify the next cell color, font color, or icon to sort by, click Add Level, and then repeat steps three through five.

Make sure that you select the same column in the Then by box and that you make the same selection under Order.

Keep repeating for each additional cell color, font color, or icon that you want included in the sort.

Top of Page

Sort by a custom list

You can use a custom list to sort in a user-defined order. Excel provides built-in, day-of-the-week and month-of-the year custom lists, and you can also create your own custom list.

Optionally, create the custom list.

How to create a custom list

In a range of cells, enter the values that you want to sort by, in the order that you want them, from top to bottom. For example:

	A
1	High
2	Medium
3	Low

Select the range that you just typed. In the example above, you would select cells A1:A3.
Click the Microsoft Office Button , click Excel Options, click the Popular category, and then under Top options for working with Excel, click Edit Custom Lists.
In the Custom Lists dialog box, click Import, and then click OK twice.
You can only create a custom list based on a value (text, number, and date or time). You cannot create a custom list based on a format (cell color, font color, and icon).
The maximum length for a custom list is 255 characters, and the first character must not begin with a number.

Select a column of data in a range of cells, or make sure that the active cell is in a table column.
On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.

The Sort dialog box is displayed.

Under Column, in the Sort by or Then by box, select the column that you want to sort by a custom list.
Under Order, select Custom List.
In the Custom Lists dialog box, select the list that you want. In the preceding example, you would click High, Medium, Low.
Click OK.

Top of Page

Sort rows

Select a row of data in a range of cells, or make sure that the active cell is in a table column.
On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.

The Sort dialog box is displayed.

Click Options.
In the Sort Options dialog box, under Orientation, click Sort left to right, and then click OK.
Under Column, in the Sort by box, select the row that you want to sort.
Do one of the following:

By value

Under Sort On, select Values.
Under Order, do one of the following:
- For text values, select A to Z or Z to A.
- For number values, select Smallest to Largest or Largest to Smallest.
- For date or time values, select Oldest to Newest or Newest to Oldest.

By cell color, font color, or cell icon

Under Sort On, select Cell Color, Font Color, or Cell Icon.
Click the arrow next to the button, and then select a cell color, font color, or cell icon.
Under Order, select On Left or On Right.

Note When you sort rows that are part of a worksheet outline, Excel sorts the highest-level groups (level 1) so that the detail rows or columns stay together, even if the detail rows or columns are hidden.

Top of Page

Sort by more than column or row

You might sort by more than one column or row when you have data that you want to group by the same value in one column or row, and then sort another column or row within that group of equal values. For example, if you have a Department and Employee column, you can first sort by Department (to group all the employees in the same department together), and then sort by name (to put the names in alphabetical order within each department). You can sort by up to 64 columns.

Note For best results, the range of cells that you sort should have column headings.

Select a range of cells with two or more columns of data, or make sure that the active cell is in a table with two or more columns.
On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.

The Sort dialog box is displayed.

Under Column, in the Sort by box, select the first column that you want to sort.
Under Sort On, select the type of sort. Do one of the following:
- To sort by text, number, or date and time, select Values.
- To sort by format, select Cell Color, Font Color, or Cell Icon.
Under Order, select how you want to sort. Do one of the following:
- For text values, select A to Z or Z to A.
- For number values, select Smallest to Largest or Largest to Smallest.
- For date or time values, select Oldest to Newest or Newest to Oldest.
- To sort based on a custom list, select Custom List.
To add another column to sort by, click Add Level, and then repeat steps three through five.
To copy a column to sort by, select the entry, and then click Copy Level.
To delete a column to sort by, select the entry, and then click Delete Level.

Note You must keep at least one entry in the list.

To change the order in which the columns are sorted, select an entry, and then click the Up or Down arrow to change the order.

Entries higher in the list are sorted before entries lower in the list.

Top of Page

Sort by a partial value in a column

Sorting is based on the entire value in a column. If you want to sort by part of a value in a column, such as a part number code (789-WDG-34), last name (Carol Philips), or first name (Philips, Carol), you first need to split the column into two or more columns so that the value you want to sort by is in its own column. To do this, you can use functions or the Convert Text to Columns Wizard.

For examples and more information, see Split names by using the Convert Text to Columns Wizard and Split text among columns by using functions.

Top of Page

Sort one column in a range of cells without affecting the others

Warning Be careful when using this feature. Sorting by one column in a range may produce results that you don't want, such as moving cells in that column away from other cells in the same row.

Note You cannot do the following procedure in a table.

Select a column in a range of cells containing two or more columns.
To select the column that you want to sort, click the column heading.
On the Home tab, in the Editing group, click Sort & Filter, and then click one of the available sort commands.
The Sort Warning dialog box is displayed.
Select Continue with the current selection.
Click Sort.
Select any other sort options that you want in the Sort dialog box, and then click OK.

If the results are not what you want, click Undo .

Top of Page

Learn more about general issues with sorting

If you get unexpected results when sorting your data, do the following:

Check to see if the values returned by a formula have changed If the data that you have sorted contains one or more formulas, the return values of those formulas can change when the worksheet is recalculated. In this case, make sure that you reapply the sort or do the sort again to get up-to-date results.

Unhide rows and columns before you sort Hidden columns are not moved when you sort columns, and hidden rows are not moved when you sort rows. Before you sort data, it's a good idea to unhide the hidden columns and rows.

Check the locale setting Sort orders vary by locale setting. Make sure that you have the proper locale setting in Regional Settings or Regional and Language Options in Control Panel on your computer. For information about changing the locale setting, see the Windows help system.

Enter column headings in only one row If you need multiple line labels, wrap the text within the cell.

Turn on or off the heading row It's usually best to have a heading row when you sort a column to make it easier to understand the meaning of the data. By default, the value in the heading is not included in the sort operation. Occasionally, you may need to turn on or off the heading so that the value in the heading is or is not included in the sort operation. Do one of the following:

To exclude the first row of data from the sort because it is a column heading, on the Home tab, in the Editing group, click Sort & Filter, click Custom Sort, and then select My data has headers.
To include the first row of data in the sort because it is not a column heading, on the Home tab, in the Editing group, click Sort & Filter, click Custom Sort, and then clear My data has headers.

Top of Page

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

Add, change, or clear conditional formats

Excel 2007

Tags cell color; change color; color; conditional; conditional formatting; format a cell; formatting; highlight; pivot

What are tags?

Use a conditional format to help you visually explore and analyze data, detect critical issues, and identify patterns and trends.

What do you want to do?

Learn more about conditional formatting

Format all cells by using a two-color scale

Format all cells by using a three-color scale

Format all cells by using data bars

Format all cells by using an icon set

Format only cells that contain text, number, or date or time values

Format only top or bottom ranked values

Format only values that are above or below average

Format only unique or duplicate values

Use a formula to determine which cells to format

Clear conditional formats

Learn more about conditional formatting

Conditional formatting helps you answer specific questions about your data. You can apply conditional formatting to a cell range, an Excel table, or a PivotTable report. There are important differences to understand when you use conditional formatting on a PivotTable report.

The benefits of conditional formatting

Whenever you analyze data, you often ask yourself questions, such as:

Where are the exceptions in a summary of profits over the past five years?
What are the trends in a marketing opinion poll over the past two years?
Who has sold more than $50,000 dollars this month?
What is the overall age distribution of employees?
Which products have greater than 10% revenue increases from year to year?
Who are the highest performing and lowest performing students in the freshman class?

Conditional formatting helps to answer these questions by making it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets. A conditional format changes the appearance of a cell range based on a condition (or criteria). If the condition is true, the cell range is formatted based on that condition; if the conditional is false, the cell range is not formatted based on that condition.

Note When you create a conditional format, you can only reference other cells on the same worksheet; you cannot reference cells on other worksheets in the same workbook, or use external references to another workbook.

Conditional formatting for a PivotTable report

Conditional formatting in a PivotTable report is different than a cell range or an Excel table in several ways:

If you change the layout of the PivotTable report, by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is maintained, as long as the fields in the underlying data are not removed.
The scope of the conditional format for fields in the Values area can be based on the data hierarchy and is determined by all the visible children (the next lower level in a hierarchy) of a parent (the next higher level in a hierarchy) on rows for one or more columns, or columns for one or more rows.

Note In the data hierarchy, children do not inherit conditional formatting from the parent, and the parent does not inherit conditional formatting from the children.

There are three methods for scoping the conditional format of fields in the Values area: by selection, by corresponding field, and by value field.

The default method of scoping fields in the Values area is by selection. You can change the scoping method to the corresponding field or value field by using the Apply formatting rule to options button, the New Formatting Rule dialog box, or the Edit Formatting Rule dialog box. The three methods of scoping give you greater flexibility depending on your needs:

Scoping by selection Use this method if you want to select:

A contiguous set of fields in the Values area, such as all of the product totals for one region.
A discontiguous set of fields in the Values area, such as product totals for different regions across levels in the data hierarchy.

Scoping by value field Use this method if you want to:

Avoid making many discontiguous selections.
Conditionally format a set of fields in the Values area for all levels in the hierarchy of data.
Include subtotals and grand totals.

Scoping by corresponding field Use this method if you want to:

Avoid making many discontiguous selections.
Conditionally format a set of fields in the Values area for one level in the hierarchy of data.
Exclude subtotals.

When you conditionally format fields in the Values area for top, bottom, above average, or below average values, the rule is based on all visible values by default. However, when you scope by corresponding field, instead of by using all visible values, you can optionally apply the conditional format for each combination of:

A column and its parent row field.
A row and its parent column field.

Top of Page

Format all cells by using a two-color scale

Color scales are visual guides that help you understand data distribution and variation. A two-color scale helps you compare a range of cells by using a gradation of two colors. The shade of the color represents higher or lower values. For example, in a green and red color scale, you can specify that higher value cells have a more green color and lower value cells have a more red color.

Issue: I don't see my conditional formatting for any cell in the range.

Quick formatting

Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales.

Select a two-color scale.

Tip Hover over the color scale icons to see which icon is a two-color scale. The top color represents higher values, and the bottom color represents lower values.

Tip You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to options button.

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

Do one of the following:
- To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

To change a conditional format, do the following:
1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
- Selection, click Just these cells.
- Corresponding field, click All <value field> cells with the same fields.
- Value field, click All <value field> cells.
Under Select a Rule Type, click Format all cells based on their values.
Under Edit the Rule Description, in the Format Style list box, select 2-Color Scale.
Select a Minimum and Maximum Type. Do one of the following:
- Format lowest and highest values Select Lowest Value and Highest Value.

In this case, you do not enter a Minimum and Maximum Value.

Format a number, date, or time value Select Number, and then enter a Minimum and Maximum Value.
Format a percentage Select Percent, and then enter a Minimum and Maximum Value.

Valid values are from 0 (zero) to 100. Do not enter a percent sign.

Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional.

Format a percentile Select Percentile and then enter a Minimum and Maximum Value.

Valid percentiles are from 0 (zero) to 100. You cannot use a percentile if the range of cells contains more than 8,191 data points.

Use a percentile when you want to visualize a group of high values (such as the top 20thpercentile) in one color grade proportion and low values (such as the bottom 20th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.

Format a formula result Select Formula, and then enter a Minimum and Maximum Value.

The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

Notes
- Minimum and Maximum values are the minimum and maximum values for the range of cells. Make sure that the Minimum value is less than the Maximum value.
- You can choose a different Minimum and Maximum Type. For example, you can choose a Minimum Number and Maximum Percent.
To choose a Minimum and Maximum color scale, click Color for each, and then select a color.

If you want to choose additional colors or create a custom color, click More Colors.

The color scale that you select is displayed in the Preview box.

Top of Page

Format all cells by using a three-color scale

Color scales are visual guides that help you understand data distribution and variation. A three-color scale helps you compare a range of cells by using a gradation of three colors. The shade of the color represents higher, middle, or lower values. For example, in a green, yellow, and red color scale, you can specify that higher value cells have a green color, middle value cells have a yellow color, and lower value cells have a red color.

Issue: I don't see my conditional formatting for any cell in the range.

Quick formatting

Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales.

Select a three-color scale. The top color represents higher values, the center color represents middle values, and the bottom color represents lower values.

Tip Hover over the color scale icons to see which icon is a three-color scale.

Tip You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to options button.

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

Do one of the following:
- To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

To change a conditional format, do the following:
1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
- Selection, click Just these cells.
- Corresponding field, click All <value field> cells with the same fields.
- Value field, click All <value field> cells.
Under Select a Rule Type, click Format all cells based on their values.
Under Edit the Rule Description, in the Format Style list box, select 3-Color Scale.
Select a Minimum, Midpoint, and Maximum Type. Do one of the following:
- Format lowest and highest values Select a Midpoint.

In this case, you do not enter a Lowest and Highest Value.

Format a number, date, or time value Select Number, and then enter a Minimum, Midpoint, and Maximum Value.
Format a percentage Select Percent, and then enter a Minimum, Midpoint, and Maximum Value.

Valid values are from 0 (zero) to 100. Do not enter a percent sign.

Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional.

Format a percentile Select Percentile and then enter a Minimum, Midpoint, and Maximum Value.

Valid percentiles are from 0 (zero) to 100. You cannot use a percentile if the range of cells contains more than 8,191 data points.

Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) in one color grade proportion and low values (such as the bottom 20th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.

Format a formula result Select Formula, and then enter a Minimum, Midpoint, and Maximum Value.

Notes
- Minimum, Midpoint, and Maximum values are the minimum, midpoint, and maximum values for the range of cells. Make sure that the Minimum value is less than the Midpoint value, which in turn, is less than the Maximum value.
- You can choose a different Minimum, Midpoint, and Maximum Type. For example, you can choose a Minimum Number, Midpoint Percentile, and Maximum Percent.
- In many cases, the default Midpoint value of 50 percent works best, but you can adjust this to fit unique requirements.
To choose a Minimum, Midpoint, and Maximum color scale, click Color for each, and then select a color.

If you want to choose additional colors or create a custom color, click More Colors.

The color scale that you select is displayed in the Preview box.

Top of Page

Format all cells by using data bars

A data bar helps you see the value of a cell relative to other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher and lower numbers, especially with large amounts of data, such as top selling and bottom selling toys in a holiday sales report.

Issue: I don't see my conditional formatting for any cell in the range.

Quick formatting

Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Style group, click the arrow next to Conditional Formatting, click Data Bars, and then select a data bar icon.

Tip You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to options button.

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

Do one of the following:
- To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

To change a conditional format, do the following:
1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
- Selection, click Just these cells.
- Corresponding field, click All <value field> cells with the same fields.
- Value field, click All <value field> cells.
Under Select a Rule Type, click Format all cells based on their values.
Under Edit the Rule Description, in the Format Style list box, select Data Bar.
Select a Shortest Bar and Longest Bar Type. Do one of the following:
- Format lowest and highest values Select Lowest Value and Highest Value.

In this case, you do not enter a Shortest Bar and Longest Bar Value.

Format a number, date, or time value Select Number, and then enter a Shortest Bar and Longest Bar Value.
Format a percentage Select Percent, and then enter a Shortest Bar and Longest Bar Value.

Valid values are from 0 (zero) to 100. Do not enter a percent sign.

Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional.

Format a percentile Select Percentile and then enter a Shortest Bar and Longest Bar Value.

Valid percentiles are from 0 (zero) to 100. You cannot use a percentile if the range of cells contains more than 8,191 data points.

Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) in one data bar proportion and low values (such as the bottom 20th percentile) in another data bar proportion, because they represent extreme values that might skew the visualization of your data.

Format a formula result Select Formula, and then enter a Shortest Bar and Longest Bar Value.

Notes
- Make sure that the Shortest Bar value is less than the Longest Bar value.
- You can choose a different Shortest Bar and Longest Bar Type. For example, you can choose a Shortest Bar Number and Longest Bar Percent.
To choose a Shortest Bar and Longest Bar color scale, click Bar Color.

If you want to choose additional colors or create a custom color, click More Colors.

The bar color that you select is displayed in the Preview box.

To show only the data bar and not the value in the cell, select Show Bar Only.

Top of Page

Format all cells by using an icon set

Use an icon set to annotate and classify data into three to five categories separated by a threshold value. Each icon represents a range of values. For example, in the 3 Arrows icon set, the green up arrow represents higher values, the yellow sideways arrow represents middle values, and the red down arrow represents lower values.

Issue: I don't see my conditional formatting for any cell in the range.

Quick formatting

Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Style group, click the arrow next to Conditional Formatting, click Icon Set, and then select an icon set.

Tip You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to options button.

Advanced formatting

Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

The Conditional Formatting Rules Manager dialog box is displayed.

Do one of the following:
- To add a conditional format, click New Rule.

The New Formatting Rule dialog box is displayed.

To change a conditional format, do the following:
1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
2. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .
3. Select the rule, and then click Edit rule.

The Edit Formatting Rule dialog box is displayed.

Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
- Selection, click Just these cells.
- Corresponding field, click All <value field> cells with the same fields.
- Value field, click All <value field> cells.
Under Select a Rule Type, click Format all cells based on their values.
Under Edit the Rule Description, in the Format Style list box, select Icon Set.
- Select an icon set. The default is 3 Traffic Lights (Unrimmed). The number of icons and the default comparison operators and threshold values for each icon can vary for each icon set.
- If you want, you can adjust the comparison operators and threshold values. The default range of values for each icon are equal in size, but you can adjust these to fit your unique requirements. Make sure that the thresholds are in a logical sequence of highest to lowest from top to bottom.
- Do one of the following:
  1. Format a number, date, or time value Select Number.
  2. Format a percentage Select Percent.

Valid values are from 0 (zero) to 100. Do not enter a percent sign.

Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional.

Format a percentile Select Percentile.

Valid percentiles are from 0 (zero) to 100. You cannot use a percentile if the range of cells contains more than 8,191 data points.

Format a formula result Select Formula, and then enter a formula in each Value box.

To make the first icon represent lower values and the last icon represent higher values, select Reverse Icon Order.
To show only the icon and not the value in the cell, select Show Icon Only.

Notes

You may need to adjust the column width to accommodate the icon.
There are three sizes of icons. The size of the icon that is displayed depends on the font size that is used in that cell.

Top of Page

Format only cells that contain text, number, or date or time values

To more easily find specific cells within a range of cells, you can format those specific cells based on a comparison operator. For example, in an inventory worksheet sorted by categories, you can highlight the products with fewer than 10 items on hand in yellow. Or, in a retail store summary worksheet, you can identify all stores with profits greater than 10%, sales volumes less than $100,000, and region equal to "SouthEast".

Note You cannot conditionally format fields in the Values area of a PivotTable report by text or date, only by number.

Quick formatting

Select one or more cells in a range, table, or PivotTable report.
On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules.

Select the command that you want, such as Between, Equal To Text that Contains, or A Date Occurring.
Enter the values that you want to use, and then select a format.