Filter data in an Excel table

When you put your data in a table, filtering controls are added to the table headers automatically.

An Excel table showing built-in filters

For quick filtering, do this:

  1. Click the arrow Filter drop-down arrow in the table header of the column you want to filter.
  2. In the list of text or numbers, uncheck the (Select All) box at the top of the list, and then check the boxes of the items you want to show in your table.

Filter Gallery

 Tip    To see more items in the list, drag the handle in the bottom-right corner of the filter gallery to enlarge it.

  1. Click OK.

The filtering arrow in the table header changes to this icon Applied filter icon to indicate a filter is applied. Click it to change or clear the filter.

Filter gallery showing Clear Filter command

Filter by specific text or numbers

  1. Click the arrow Filter drop-down arrow in the table header of the column you want to filter.
  2. If the column has numbers, click Number Filters. If the column has text entries, click Text Filters.
  3. Pick the filtering option you want, and then enter your filtering conditions.

For example, to show numbers above a certain amount, pick Greater Than Or Equal To, and then enter the number you have in mind in the adjacent box.

Custom AutoFilter dialog box

To filter by two conditions, enter filtering conditions in both sets of boxes, and pick And for both conditions to be true, and Or for either of the conditions to be true.

Filter items by color

If you've applied different cell or font colors or a conditional format, you can filter by the colors or icons that are shown in your table.

  1. Click the arrow Filter drop-down arrow in the table header of the column that has color formatting or conditional formatting applied.
  2. Click Filter by Color and then pick the cell color, font color, or icon you want to filter by.

Filter by Color options

The types of color options you’ll have available depend on the types of format you have applied.

Create a slicer to filter your table data

In Excel 2010, slicers were added as a new way to filter PivotTable data. In Excel 2013, you can also create slicers to filter your table data. A slicer is really useful because it clearly indicates what data is shown in your table after you filter your data.

Table slicer

Here’s how you can create one to filter your data:

  1. Click anywhere in the table to show the Table Tools on the ribbon.

Table Tools on the ribbon

  1. Click Design > Insert Slicer.

Insert Slicer button on the Design tab of the Table Tools

  1. In the Insert Slicers dialog box, check the boxes you want to create slicers for.
  2. Click OK.

A slicer appears for each table header you checked in the Insert Slicers dialog box.

  1. In each slicer, click the items you want to show in your table.

To choose more than one item, hold down Ctrl, and then pick the items you want to show.

 Tip    To change how the slicers look, click the slicer to show the Slicer Tools on the ribbon, and then apply a slicer style or change settings on the Options tab.

More information about filtering data

 
 
Се однесува на:
Excel 2013