Filter a list of data in Excel 2016 for Mac

When you filter a list, you temporarily hide some of your content. Filters provide a quick way to find and work with a subset of data in a range or table.

Filter for the top or bottom set of values

  1. Click a cell in the range or table that you want to filter.

  2. On the Data tab, click Filter.

    On the Data tab, select Filter

  3. Click the arrow Arrow showing that column is filtered in the column that contains the content that you want to filter.

  4. Under Filter, click Choose One, and then enter your filter criteria.

    In the Filter box, select Choose One

Notes: 

  • You can apply filters to only one range of cells on a sheet at a time.

  • When you apply a filter to a column, the only filters available for other columns are the values visible in the currently filtered range.

  • Only the first 10,000 unique entries in a list appear in the filter window.

Filter for a specific number or a number range

  1. Click a cell in the range or table that you want to filter.

  2. On the Data tab, click Filter.

    On the Data tab, select Filter

  3. Click the arrow Arrow showing that column is filtered in the column that contains the content that you want to filter.

  4. Under Filter, click Choose One, and then enter your filter criteria.

    In the Filter box, select Choose One

  5. In the box next to the pop-up menu, enter the number that you want to use.

  6. Depending on your choice, you may be offered additional criteria to select:

    In the Filter box, select And or Or to add more criteria

Notes: 

  • You can apply filters to only one range of cells on a sheet at a time.

  • When you apply a filter to a column, the only filters available for other columns are the values visible in the currently filtered range.

  • Only the first 10,000 unique entries in a list appear in the filter window.

  • Instead of filtering, you can use conditional formatting to make the top or bottom numbers stand out clearly in your data.

Filter by font color, cell color, or icon sets

You can quickly filter data based on visual criteria, such as font color, cell color, or icon sets. And you can filter whether you have formatted cells, applied cell styles, or used conditional formatting.

  1. In a range of cells or a table column, click a cell that contains the cell color, font color, or icon that you want to filter by.

  2. On the Data tab, click Filter .

    On the Data tab, select Filter

  3. Click the arrow AutoFilter arrow in the column that contains the content that you want to filter.

  4. Under Filter, in the By color pop-up menu, select Cell Color, Font Color, or Cell Icon, and then click a color.

Filter blank cells

This option is available only if the column that you want to filter contains a blank cell.

  1. Click a cell in the range or table that you want to filter.

  2. On the Data toolbar, click Filter.

    On the Data tab, select Filter

  3. Click the arrow AutoFilter arrow in the column that contains the content that you want to filter.

  4. In the (Select All) area, scroll down and select the (Blanks) check box.

    Notes: 

    • You can apply filters to only one range of cells on a sheet at a time.

    • When you apply a filter to a column, the only filters available for other columns are the values visible in the currently filtered range.

    • Only the first 10,000 unique entries in a list appear in the filter window.

Filter for specific text

  1. Click a cell in the range or table that you want to filter.

  2. On the Data tab, click Filter .

    On the Data tab, select Filter

  3. Click the arrow AutoFilter arrow in the column that contains the content that you want to filter.

  4. Under Filter, click Choose One, and then in the pop-up menu, do one of the following:

    To filter the range for

    Click

    Rows that contain specific text

    Contains or Equals.

    Rows that do not contain specific text

    Does Not Contain or Does Not Equal.

  5. In the box next to the pop-up menu, enter the text that you want to use.

  6. Depending on your choice, you may be offered additional criteria to select:

    In the Filter box, select And or Or to add more criteria

    To

    Click

    Filter the table column or selection so that both criteria must be true

    And.

    Filter the table column or selection so that either or both criteria can be true

    Or.

Filter for the beginning or end of a line of text

  1. Click a cell in the range or table that you want to filter.

  2. On the Data toolbar, click Filter .

    On the Data tab, select Filter

  3. Click the arrow AutoFilter arrow in the column that contains the content that you want to filter.

  4. Under Filter, click Choose One, and then in the pop-up menu, do one of the following:

    To filter for

    Click

    The beginning of a line of text

    Begins With.

    The end of a line of text

    Ends With.

    Cells that contain text but do not begin with letters

    Does Not Begin With.

    Cells that contain text but do not end with letters

    Does Not End With.

  5. In the box next to the pop-up menu, enter the text that you want to use.

  6. Depending on your choice, you may be offered additional criteria to select:

    In the Filter box, select And or Or to add more criteria

    To

    Click

    Filter the table column or selection so that both criteria must be true

    And.

    Filter the table column or selection so that either or both criteria can be true

    Or.

Use wildcard characters to filter

Wildcard characters can be used to help you build criteria.

  1. Click a cell in the range or table that you want to filter.

  2. On the Data toolbar, click Filter.

    On the Data tab, select Filter

  3. Click the arrow AutoFilter arrow in the column that contains the content that you want to filter.

  4. Under Filter, click Choose One, and select any option.

  5. In the text box, type your criteria and include a wildcard character.

    For example, if you wanted your filter to catch both the word "seat" and "seam", type sea?.

  6. Do one of the following:

    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)

    A question mark or an asterisk

    For example, there~? finds "there?"

Remove or reapply a filter

Do any of the following:

To

Do this

Remove specific filter criteria for a filter

Click the arrow AutoFilter arrow in a column that includes a filter, and then click Clear Filter.

Remove all filters that are applied to a range or table

Select the columns of the range or table that have filters applied, and then on the Data tab, click Filter.

Remove filter arrows from or reapply filter arrows to a range or table

Select the columns of the range or table that have filters applied, and then on the Data tab, click Filter.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×