Microsoft Excel 2007 tutorial: filter and sort data


Filter and sort data

I. Filter and sort data

 You can filter and sort data that you have entered on a worksheet. The purpose of filtering data is to display the row data that you want and hide the row data that does not match the criteria (condition) you specify. With the data that you filtered you can copy, find, edit, format, chart, and print them without rearranging or moving; and you sort data in ascending or descending order.

I.1. Filter data by using the Filter command

By using the Filter command, you can quickly filter all types of data on a worksheet. To use the Filter command, do the following:

- Click the column header of cells or table that you want to filter.

- On the Data tab, in the Sort & Filter group, click Filter.

Excel 2007 Filter command

I.2. Filter text

If a range of cells does not have a column header, you need to insert a single blank row above the range of cells containing data.It can avoid hiding first row data when you filter the data.

Excel 2007 insert a blank row

To filter text, do the following:

- Select the range of cells by starting from first blank row to the last row data.

- On the Data tab, in the Sort & Filter group, click Filter.

- Click the arrow button Excel 2007 arrow button in the first blank row.

- On the AutoFilter menu, do one of the following:

Excel 2007 AutoFilter menu

To select from a list of text values, in the list under Text Filters, select or clear one or more text values. If the list contains a large text values, clear the Select All check box, then select the specific text values that you want to filter by. And then click Ok button.

Note: The list of text vales can be up to 10,000.

To use the criteria, point to Text Filters, and then click one of the comparison operator commands, or click Custom Filters.

Excel 2007 criteria 

For example, to filter by text that equals "Lavy", click Equals, or to filter by text that ends with letter "e", click Ends With. In the Custom AutoFilter dialog box, select Lavy from the box next to the box containing equals operator, or enter Lavy in that box.

Excel 2007 filter text 

In the Custom AutoFilter dialog box, you can specify either one or two criteria to filter by. For example, to filter by text that contains letter "a", click contains in the the first left box, and then enter a in the first right box.

Excel 2007 filter text containing letter a

Or to filter by text that begins with letter "L" and ends with letter "y", click begins with in the first left box, and then enter l in the opposite box, and click ends with in the the second left box, and then enter y in the opposite box.

   Excel 2007 filter text begins and ends with letters 

Notes:

- Letters that you use as the criteria are not case-sensitive.

- If you use And function, the filtered text will display when two conditions are true.

- If you use Or function, the filtered text will display when either one or two conditions are true.

 For a table or a range of cells that has a column header, you just click the column header, and then follow from step 2 to the last step of filtering text in a range of cells without column header above.

I.3. Filter numbers

You can filter numbers with a list of numbers or with comparison operators, such as Equals, Does Not Equal , Greater Than, Less than, Between, etc that you can see in the list below:

Excel 2007 operator list

To filter numbers, do the following:

- Select the column head of cells or table (the cells or table containing numeric data).

- On the Data tab, in the Sort & Filter group, click Filter.

- Click the arrow button Excel 2007 arrow button in the column header.

- On the AutoFilter menu, do one of the following:

Excel 2007 AutoFilter menu filter number

To select from a list of numbers, in the list under Number Filters, select or clear one or more numbers to filter by. If the list is large, clear the Select All check box, then select the specific specific numbers that you want to filter by. And then click Ok button.

  Note: The list of numbers can be up to 10,000.

To use the criteria, point to Number Filters, and then click one of the comparison operator commands, or click Custom Filters.

For example, you want to filter by salary that is less than $2000.00 in the following range of cells.

NamePosition Salary
LavyIT manager $ 750.00
DaraSale manager $ 1500.00
KhemrinLecturer $ 2000.00

To this task, do the following:

1. Click the Salary column.

2. On the Data tab, in the Sort & Filter group, click Filter.

3. Click the arrow button Excel 2007 arrow button in the column header.

4. Point to Number Filters, and then click Less Than.

Excel 2007 filter numbers

5. In the Custom AutoFilter dialog box, in the right box, select $ 2000.00 from the list or enter 2000, and then click Ok button.

Note: If you want to filter numbers in a range of cells that does not have a column head, follow the steps of filter text in a range of cells without column header.

I.4. Filters dates or times

You can filter dates or times with a list of dates or times or with the criteria including common filter and dynamic filter. To filter dates or times, do the following:

- Select the column head of cells or table (the cells or table containing numeric data).

- On the Data tab, in the Sort & Filter group, click Filter.

- Click the arrow button Excel 2007 arrow button in the column header.

- On the AutoFilter menu, do one of the following:

To select from a list of dates or times, in the list under Date Filters, select or clear one or more dates or times to filter by. All dates in the range of cells or table are grouped by a hierarchy of years, months, and days. If you select or clear a higher level in the hierarchy, it's mean that you select or clear all nested dates below that level.

Excel 2007 AutoFilter menu date time

If the list is large, clear the Select All check box, then select the specific specific dates or times that you want to filter by. And then click Ok button.

  Note: The list of dates or times can be up to 10,000.

To use the criteria, point to Date Filters, and then click the command filter or dynamic filter.

 - Common filter: Refer to the comparison operator, such as Equals, Before, After and Between.

- Dynamic filter: Where the criteria can change when you reapply the filter. Dynamic filter including Yesterday, Today, Tomorrow, This Week, Next Month, etc. 

Ex1: You want to filter date of birth of students who were born before12/23/1993 and after 10/23/1984. To do this task, do the following:

1. Click the column header of date of birth.

2. On the Data tab, in the Sort & Filter group, click Filter.

3. Click the arrow button Excel 2007 arrow button in the column header.

4. On the AutoFilter menu, point to Date Filters, and then click Between.

5. Follow the figure below:

6. Click Ok button.

Ex2: You want to filter by an earlier time of 7:30 AM and a later time of  12:00 PM. To do this task, follow all of steps example 1. You just replace the dates by the times.

Ex3: To filter all dates by current years, click This Year, or by following year, click Next Year.

 


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.