advance filter

फ़िल्टर को सीखने का आसान तरीका

Filter data in a range or table

Using AutoFilter or built-in comparison operators such as "greater than" and “top 10” in Excelcan show the data you want and hide the rest. Once you have filtered data in a range of cells or table, you can either reapply a filter to get up-to-date results, or clear a filter to redisplay all of the data.

Windows



Online

Filtered data displays only the rows that meet criteria 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.

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.

Learn more about filtering

The three types of filters

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.

Reapplying a filter

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.
  
  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.
  
  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, 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.

Do not mix data types

For best results, do not mix data types, 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 data types, the command that is displayed is the data type that occurs the most. For example, if the column contains three values stored as number and four as text, the Text Filters command is displayed .

Filter a range of data

1. Select the data you want to filter. For best results, the columns should have headings.
   
   
   
2. Click the arrow in the column header, and then click Text Filters or Number Filters.
3. Click one of the comparison operators. For example, to show numbers within a lower and upper limit, select Between.
   
   
   
4. In the Custom AutoFilter box, type or select the criteria for filtering your data. For example, to show all numbers between 1,000 and 7,000, in the is greater than or equal to box, type 1000, and in the is less than or equal to box, type 7000.
   
   
   
5. Click OK to apply the filter.
   Note: Comparison operators aren’t the only way to filter by criteria you set. You can choose items from a list or search for data. You can even filter data by cell color or font color.

Filter data in a table

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



For quick filtering, do this:

1. Click the 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.
   
   
   
3. Click OK.

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

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

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 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.
   
   
   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

Note: Slicers are not available in Excel 2007.

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.



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.
   
   
   
2. Click Design > Insert Slicer.
   
   
   
3. In the Insert Slicers dialog box, check the boxes you want to create slicers for.
4. Click OK.
   A slicer appears for each table header you checked in the Insert Slicers dialog box.
   
   
5. 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.