Basic and advanced Filtering
Filtering is a way that you can use Excel to quickly extract certain data from your spreadsheet. Unlike sorting, filtering doesn’t just reorder the list. It actually hides the rows or columns containing data that do not meet the filter criteria you define. Excel has an AutoFilter feature that makes it very easy to extract data from your spreadsheet.
To use the AutoFilter:
- Click on any cell in your spreadsheet.
- Select the Home tab.
- Under the Editing group, press the Sort and Filtering button and select the Filter button.
- Drop-down menus will appear next to each cell heading.
- Clicking on any drop-down menu will provide you with options for sorting or filtering.
By applying above process again and again for different column, we can filter multiple columns as per our requirement.
To clear a filter:
After applying a filter, you may want to remove, or clear, it from your worksheet so you’ll be able to filter content in different ways.
1. Click the drop-down arrow for the filter you wish to clear.
2. The Filter menu will appear.
3. Choose Clear Filter From [COLUMN NAME] from the Filter menu.
4. The filter will be cleared from the column.
This example teaches you how to apply an advanced filter to only display records that meet complex criteria.
When you use the Advanced Filter, you need to enter the criteria on the worksheet. Create a Criteria range (blue border below for illustration only) above your data set. Use the same column headers. Be sure there’s at least one blank row between your Criteria range and data set.
To display the product MS Excel for 1-aug-14, execute the following steps.
1. Enter the criteria shown below on the worksheet.
2. Click any single cell inside the data set.
3. On the Data tab, in the Sort & Filter group, click Advanced.
4. Click in the Criteria range box and select the required range.
5. Click OK.
Notice the options to copy your filtered data set to another location and display unique records only (if your data set contains duplicates).
Result looks like:
Formula as Criteria
To display the product price which is grater than 200 tk, execute the following steps.
1. Enter the criteria (+formula) shown below on the worksheet.
2. On the Data tab, click Advanced, and adjust the Criteria range to required range.
3. Click OK. Result looks like:
How to Use Custom Filters in Excel 2013
In addition to filtering a data list to records that contain a particular field entry in Excel 2013, you can create custom AutoFilters that enable you to filter the list to records that meet less-exacting criteria (such as last names starting with the letter E) or ranges of values (such as product price between 200tk and 500tk).
To create a custom filter for a field, you click the field’s AutoFilter button and then highlight Text Filters, Number Filters, or Date Filters (depending on the type of field) on the drop-down list and then click the Custom Filter option at the bottom of the continuation list. When you select the Custom Filter option, Excel displays a Custom AutoFilter dialog box.
You can also open the Custom AutoFilter dialog box by clicking the initial operator (Equals, Does Not Equal, Greater Than, and so on) on the field’s Text Filters, Number Filters, or Date Filters submenus.
In this dialog box, you select the operator that you want to use in the first drop-down list box. Then enter the value (text or numbers) that should be met, exceeded, fallen below, or not found in the records of the database in the text box to the right.