Working With Tables And Filters

Sorting Data in a Table

• Quick sorting allows you to quickly and easily apply common sorting options to data within the table. Quick sorting can be applied to tables by clicking the pull-down arrow beside the name of the header you want to sort. 
• Then choose an ascending or descending sort option: 
  

• The values will now be sorted in ascending or descending order based on the criteria that you selected. 
• The pull-down arrow beside the header name will also show what type of sort has been applied. In the example below, you can see that the Weekly Sales column is now sorted by largest value to smallest: 

• You can also apply a quick sort by clicking Data → Sort A to Z or Sort Z to A:

• While using quick sorts is a simple way to apply basic sorting to your data, you are limited to sorting one column at a time. Additionally, quick sorts can only be used to sort columns, not rows.

What is an AutoFilter?

Use AutoFilter to find values, show or hide values, in one or more columns of data. You can filter based on choices you make from a list, search to find the data that you want to see. When you filter data, entire rows are hidden if values in one or more columns don't meet the filtering criteria.

1. Click the arrow in the column header and do one of the following: 
   
2. Select specific values: Check (Select All) to clear all of the check boxes, and then select only the values you want to see.
3. Search for values: In the Search box, type text or numbers you want to see. 
   
4. Click OK to apply the filter.

Creating a Custom AutoFilter

1. In the Custom AutoFilterbox, 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. 
2. 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. 

Using an Advanced Filter

If the data you want to filter requires complex criteria (such as Type = "Produce" OR Salesperson = "Davolio"), you can use the Advanced Filter dialog box.

• To open the Advanced Filter dialog box, click Data > Advanced.

The Advanced command works differently from the Filter command in several important ways.

• It displays the Advanced Filter dialog box instead of the AutoFilter menu. 
• You type the advanced criteria in a separate criteria range on the worksheet and above the range of cells or table that you want to filter. Microsoft Office Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the advanced criteria.

Using an Advanced Filter with Logical Statements

The criteria range can hold normal text, number or some logical statement.

Copying Filtered Records

By default, filtered records will be generated on the same worksheet as it’s an actual database. Once you got the filtered result, you can choose to duplicate or move the result to different worksheet or workbook.