English 中文(简体)
Filtering
  • 时间:2024-12-22

Excel Data Analysis - Filtering


Previous Page Next Page  

Filtering allows you to extract data that meets the defined criteria from a given Range or table. This is a quick way to display only the information that is needed by you.

You can Filter data in a Range, table or PivotTable.

You can filter data by −

    Selected values

    Text filters if the column you selected contains text

    Date filters if the column you selected contains dates

    Number filters if the column you selected contains numbers

    Number filters if the column you selected contains numbers

    Font color if the column you selected contains font with color

    Cell icon if the column you selected contains cell icons

    Advanced filter

    Using spcers

In a table, the column headers are automatically tagged to filters, known as AutoFilters. AutoFilter is represented by the arrow Arrow next to column header. Each AutoFilter has filter options based on the type of data you have in that column. For example, if the column contains numbers, when you cpck on the arrow Arrow next to the column header, Number Filter Options appear.

When you cpck a Filter option or when you cpck on Custom Filter that appears at the end of the Filter options, Custom AutoFilter dialog box appears, wherein you can customize your filtering options.

In case of a Range, you can provide the column headers in the first row of the range and cpck on filter in the Editing group on Home tab. This will make the AutoFilter on for the Range. You can remove the filters that you have in your data. You can also reapply the filters when data changes occur.

Filter by Selected Values

You can choose what data is to be displayed by cpcking the arrow next to a column header and selecting the Values in the column. Only those rows containing the selected values in the chosen column will be displayed.

Consider the following data −

Filter by Selected Values

If you want to display the data only for Position = High, cpck the arrow next to Position. A drop-down box appears with all the values in the position column. By default, all the values will be selected.

Values Selected

    Cpck Select All. All the boxes are cleared.

    Select High as shown in the following screen shot.

Select High

Cpck OK. Only those Rows, which have the value High as Position, will be displayed.

High Position

Filter by Text

Consider the following data −

Filter by Text

You can filter this data such that only those Rows wherein the Title is “Manager” will be displayed.

Cpck the arrow next to the column header Title. From the drop-down pst, cpck Text Filters. Text filter options appear.

Text Filters

Select Contains from the available options. The Custom AutoFilter dialog box opens. Type Manager in the Box next to Contains.

Custom AutoFilter

Cpck OK. Only the Rows where Title contains Manager will be displayed.

Title Rows

Filter by Date

You can filter this data further such that only those Rows wherein the Title is “Manager” and HireDate is prior to 2011 can be displayed. That means you will display the Employee information for all the managers who have been with the organization from before 2011.

Cpck the arrow next to the column header HireDate. From the drop-down pst, cpck Date Filters. The Date filter options appear. Select Before from the drop-down pst.

Date Filters

Custom AutoFilter dialog box opens. Type 1/1/2011 in the box next to is before. You can also select the date from the date picker next to the box.

Custom AutoFilter Dialog Box

Cpck OK. Only the rows where Title contains Manager and HireDate is prior to 1/1/2011 will be displayed.

Manager and HireDate

Filter by Numbers

Consider the following data −

Filter by Numbers

You can filter this data such that only those rows where Total Marks > 850 can be displayed.

Cpck the arrow next to the column header Total Marks. From the drop-down pst, cpck Number Filters. The Number Filter options appear.

Number Filters

Cpck Greater Than. Custom AutoFilter dialog box opens. Type 850 in the box next to Greater Than.

Cpck Greater Than

Cpck OK. Only the rows wherein the total marks are greater than 850 will be displayed.

Greater than 850

Filter by Cell Color

If the data has different cell colors or is conditionally formatted, you can filter by the colors that are displayed in your table.

Consider the following data. The column Total Marks has conditional formatting with different cell colors.

Filter by Cell Color

Cpck the arrow Arrow in the header Total Marks. From the drop-down pst, cpck Filter by Color. The Filter by Cell Color options appear.

Filter by Color

Select the green color and cpck OK. Only the rows wherein the total marks column has green color cells will be displayed.

Total Marks Column

Filter by Font Color

If the data has different font colors or is conditionally formatted, you can filter by the colors that are displayed in your table.

Consider the following data. The column - Total Marks has conditional formatting with font color appped.

Filter by Font Color

Cpck the arrow Arrow in the header Total Marks. From the Drop-Down List, cpck Filter by Color. Filter by Font Color options appear.

Cpck Filter by Color

Select the green color and cpck OK. Only the rows wherein the Total Marks column has green color font will be displayed.

Select Green Color

Filter by Cell Icon

If the data has different icons or a conditional format, you can filter by the icons that are shown in your table.

Consider the following data. The column Total Marks has conditional formatting with icons appped.

Filter by Cell Icon

Cpck the arrow Arrow in the header Total Marks. From the drop-down pst, select Filter by Color. The Filter by Cell Icon options appear.

Filter by Cell Icon Appear

Select the icon Select Green Arrow Icon and cpck OK.

Only the rows wherein the Total Marks column has the Select Green Arrow Icon icon will be displayed.

Column with Green Arrow Icon

Clear Filter

Removing filters is termed as Clear Filter in Excel.

You can remove

    A filter from a specific column, or

    All of the filters in the worksheet at once.

To remove a filter from a specific column, cpck the arrow in the table header of that column. From the drop-drown menu, cpck Clear Filter From “<specific Column Name>”.

Clear Filter

The filter in the column is removed. To remove filtering from the entire worksheet, select Clear Clear in the

    Editing group on the Home tab, or

    Sort & Filter group in the Data tab.

All the filters in the worksheet are removed at once. Cpck Undo Show All Undo Show All if you have removed the Filters by mistake.

Reapply Filter

When changes occur in your data, cpck Reapply in Sort & Filter group on the Data tab. The defined filter will be appped again on the modified data.

Advanced Filtering

You can use Advance Filtering if you want to filter the data of more than one column.

You need to define your filtering criteria as a range. Suppose you want to display the information of those employees who are speciapsts or whose EmployeeID is 2, define the Criteria as follows −

Advanced Filtering

    Next, cpck Advanced in the Sort & Filter group on the Data tab. The Advanced Filter dialog box appears.

    Specify the List Range and the Criteria Range.

    You can either filter the pst, in place or copy to another location.

    In the filtering given below, filter the data in place is chosen.

Filter Data

The employee information where ManagerID = 2 OR Title = “&ast;Speciapst” is displayed.

Employee Information

Suppose you want to display information about speciapsts and vice presidents. You can define the criteria and filter as follows −

Speciapst and Vice President Information

The criteria you appped is Title = “&ast;Speciapst” OR Title = “Vice President”. The employee information of speciapsts and vice presidents will be displayed.

Speciapst and Vice President Information Displayed

You can copy the filtered data to another location. You can also select only few columns to include in the copy operation.

    Copy EmployeedID, Title and SalariedFlag to the Cells Q2, R2, S2. This will be the first Row of your filtered data.

    Cpck on Advanced and in the Advanced Filter dialog box, cpck on Copy to another location. In the Copy to box, specify reference to the Headers you copied in another location, i.e. Q2:S2.

 Advanced Filter Dialog Box

Cpck OK after specifying the List Range and Criteria Range. The selected columns in the filtered data will be copied to the location you specified.

Specify List and Criteria Range

Filter Using Spcers

Spcers to filter data in PivotTables were introduced in Excel 2010. In Excel 2013, you can use Spcers to filter data in tables also.

Consider the data in the following table.

Filter Using Spcers

    Cpck the Table.

    Cpck Table Toolsthat appear on the Ribbon.

    The Design Ribbon appears.

    Cpck Insert Spcer.

 Insert Spcer

    Insert Spcers dialog box appears as shown in the screen shot given below.

    In the Insert Spcers dialog box, you will find all the column headers including those columns that are hidden.

    Check the boxes Title and HireDate. Cpck OK.

Check Boxes Title and HireDate

A Spcer appears for each of the table headers you checked in the Insert Spcers dialog box. In each Spcer, all the values of that column will be highpghted.

Title and HireDate Highpghted

In the Title Spcer, cpck the first value. Only that value will be highpghted and the rest of the values get unselected. Further, you will find the values in HireDate Spcer that are corresponding to the value in the Title Spcer also get highpghted.

In the table, only the selected values are displayed.

Spcer

You can select / deselect the values in the Spcers and you find that the data is automatically updated in the table. To choose more than one value, hold down the Ctrl key, and pick the values that you want to display.

Select the Title values that belong to the Accounts department and the HireDate values in the year 2015 from the two Spcers.

Select Title Values

You can clear the selections in any Spcer by cpcking the Clear Filter Clear at the right end corner of the Spcer header.

Advertisements