Sort and Filter Data in Excel Tutorial

Free Online Microsoft Excel Tutorial
Excel 2010 -
Working with Data

* Sorting Data Alphabetically or Numerically
*
Sorting Data by More Than One Criterion
*
Apply and Use a Data Filter
*
Advanced Filters
* Removing a Filter

Data in Excel can run into the hundreds and even thousands of lines. You need tools that allow you to organize your data according to specific criteria, for example, alphabetically sort surnames or apply specific filters to a worksheet to only display data that meets specific filter criteria. The sort and filter tools in Excel allow you to organize and manipulate your data quickly and easily.

* Sorting Data Alphabetically or Numerically

Data in Excel can run into the hundreds and even thousands of lines. You need tools that allow you to organize your data according to specific criteria, for example, alphabetically sort surnames or apply specific filters to a worksheet to only display data that meets specific filter criteria. The sort and filter tools in Excel allow you to organize and manipulate your data quickly and easily.

To sort your data alphabetically:

1. Decide which column in your worksheet contains the data according to which you want to sort the worksheet.

2. Select a cell in the column that contains the data according to which you want to sort, by clicking in it.

3. Click on the Data tab on the Ribbon.

4. In the Sort & Filter group, click on the Sort A-Z or Sort Z-A buttons (circled in yellow in the screenshot below). These buttons will automatically change to be Sort Smallest to Largest and Sort Largest to Smallest when you select data that contains numerical values; and Sort Oldest to Newest and Sort Newest to Oldest, when you select data that contains date or time values.

You can sort data as many times as you like by repeating the process detailed above.

* Sorting Data by More Than One Criteria

We humans are complicated beings: we rarely categorize elements by one level of criteria only. In the same way, you may need to organize your data in a worksheet by a variety of criterion that affects the order in which data appears. For example, you may wish to sort data alphabetically according to surname and then in date order according to birthday. You will not be able to do this by using the normal Sort buttons as these will treat each individual sort separately and therefore reorganize your data according to the last criteria selected, instead of applying two or more sort criterion to the data. In Excel, we call the different sort criterion and the order in which they need to be applied, levels.

Study the data in the screenshot below:

To sort this data in ascending order according to Class 1 and then the students’ Surname:

1. Click anywhere in the data area of the worksheet.

2. Click on the Sort button in the Sort & Filter group (located under the Data tab on the Ribbon).

4. Next, to ensure the data is also sorted according to Surname, click on Add Level.

5. In the second level, ensure:

the Then by box is set to Surname;

the Sort On box is set to Values; and

the Order box is set to A-Z.

6. Press OK to accept the sort level criteria.

You can employ multiple levels by which to sort data depending on the type of data and the specific organizational needs that pertains to it.

* Apply and Use a Data Filter

Where the Sort function allows you to reorganize your data into a specific order, the Filter function allows you to view only specific data depending on specific criteria that you provide. Imagine you have a data sheet containing students taking different classes, but you only wanted a list of the students taking a specific class - the Filter function allows you to obtain this data within seconds.

To apply a Filter:

1. Position your cursor anywhere in the data you wish to apply the Filter to.

2. Click on the Data tab on the Ribbon.

3. In the Sort & Filter group, click on the Filter button.

4. Click the dropdown Filter arrow (circled in yellow in the screenshot below) in the column containing the data you wish to filter according to. This will open the Filter options (see below):

5. You will note the available filters listed in the second half of the Filter options list. In the screenshot above, the available filters are Lane and Smith, which are the two values contained in this column. Depending on the column you select that contains your filters, the data in the Filter options list will populate available filters list. Tick or untick the boxes for the data you want to display in the filtered list. For example, if we wanted to display a list of only the students that have Smith as a Counsellor, we would untick all the other boxes leaving only the Smith box ticked.

6. Press OK to accept the Filter.

Your data will now be filtered showing only the data that meets the criteria you selected.

You can filter data according to multiple filters. Click on the Filter dropdown arrow of other columns to filter the data even further, according to the available options.

Remember, filters depend on the data you have entered. If you don’t have a column containing country names, for example, you cannot filter according to this. Also keep in mind that if you enter data incorrectly, for example, misspelling a word, this may be excluded when you apply a Filter.

* Advanced Filters

If Filters have not blown your socks off yet, wait until you get to this next section. The advanced Filter options allow you to filter by just about any criteria you can imagine making filters extremely powerful tools for data analysis and management.

To use the Advanced Filters:

1. Position your cursor anywhere in the data you wish to apply the Filter to.

2. Click on the Data tab on the Ribbon and in the Sort & Filter group, click on the Filter button.

4. Click the drop down Filter arrow in the column containing the data you wish to filter according to. This will open the Filter options.

5. Depending on the type of data the column contains, one of the following will appear as an option in the Filter list:

* Text Filters: filter according to data that contains or does not contain certain words or letters, matches a specific word, begins with certain letters, etc.

* Number Filter: filter according to numbers that equals, does not equal, is greater than, less than, falls between, is in the Top 10, above or below average, etc.

* Date Filters: filter according to dates that are equal to, falls before, after or between specific dates, tomorrow, yesterday, today, next week, this month, next year, etc.

* Color Filters: filter according the cell or font color.

6. Click on the Filter and then select the appropriate option from the menu list. For example, the screenshot below demonstrates the options available (circled in yellow) when you filter a column containing numerical values:

Experiment with using different Filters to analyze, sort and reorganize your data. Remember practice makes perfect!

* Removing a Filter

The great thing about Filters is that they are dynamic: in other words, filters do not permanently affect your data. Removing a specific Filter will redisplay the data that was hidden as a result of the Filter, and switching the Filter function off, will remove all filtering from the data displaying all the data.

To remove a specific Filter:

1. You can identify whether a Filter has been applied to a column, by noting the way the Filter dropdown arrow appears:

2. To remove an individual Filter, click on the icon and select: Clear filter from [COLUMN NAME].

3. To remove all Filters applied to a worksheet, click on the Filter button in the Sort & Filter group. This will redisplay all the data on the worksheet.

Experiment with the different Filters available and make notes of specific filters you think will come in useful to your work, study or personal use of Excel.