Filter, Auto filter and advanced filter:

Filter,Auto filter and advanced filter Ms excel 2007:Filter is use to extract only the records that match criteria. A database is called list in excel. Commands in excel can have different results on database records while using filters.

Filter command is uses the worksheet contains a large list and the user wants to see the data that meets a specified condition. Filter automatically analyzes the data in the list and determines the unique items in each column. It makes the items available in a drop-down list in the cell that contains heading each column. The user can select an item for each column to display the data that meets the criteria. Excel temporarily hides the remaining data.

Auto Filter:

Auto Filter is used to display only those records that match particular criteria. The Auto Filter command applies pull-down lists directly to the column heading in the database. These menus are  used to select the field contents, which determine what records will display.

Auto Filter is an easy technique to display the roes that meet one or more criteria. so A drop-down list appears on each field in the list. The list contains all the unique vales for that field. The user can select a value from the list. The user can also get a range of values.

Advanced Filter

The Advanced Filter command is use to extract the records from database based on a criterion and then move the results to a different location on current worksheet. And it is also uses to place the results on a new worksheet to overcome the problems of using certain features of excel like printing, formatting and editing options while the database is filtered

Understanding criteria Ranges:

The user should define criteria range to range to use advance filter command. The range must consist of at least two rows and one column. The first row of the range contains the field name(s) and the other row(s) will contain the values that must be met. The user can give AND or OR conditions in criteria range. Values entered on the same row will imply an AND condition. The values entered on different roes imply OR condition.

Setting up Auto Filter

The following procedure is use to use Auto filler feature.

• Click on any cell of you data list.
• Choose data filter Auto Filter from the menu bar. Excel places down arrows next to each column heading in the list.
• Select Auto Filter drop down arrow next to region column heading. Excel displays the unique occurrence of each region names including All, Top 10, custom and Blank.
• Choose Fsd from the selection list as how in above sheet.
• Excel will now show only the record with Fsd in Region column.

Turning off auto Filter

You can Turn off Auto Filter feature by clicking Data > Filter > Auto Filter. The list arrows disappear when you turn off the feature from the menu bar.

Defining custom filter:

The user may want to see records that fall within a range of values and not one specific value. The custom Auto Filter option is used to father refine filtering choices.

The following example filters records that fall between sales value of 10000 and 27000.

• Make sure the Auto Filter Feature is on.
• Click on drop down arrow next to sales column heading. Excel displays each unique occurrence of each sales number, which is not very useful for selecting records.
• Click on custom option in drop-down list and custom auto filter dialog box appears.
• Excel displays the name of filed up which you are performing the custom Auto filter.
• Click on drop down arrow in the show rows and click on is greater than or equal to as shown in figure. Press TAB to move to the next text area.
• Type 10000 in the text area. Make sure the And radio button is selected.
• Click on the drop –down arrow for the next logical expression.
• Choose is less than or equal to. Press TAB to move to the next area.
• Type 27000 in the text area. Click on OK to perform the filtering.
• Excel displays the record whose sales are greater than or equal to 10000 and less than or equal to 27000.