Microsoft Excel 77-727 – 3.3 Filter and Sort a Table

1. 3.3.1 Filter Records

Worksheets are often used to store large amounts of data. The term filter is used to describe the process by which you can extract data from a large amount of information by specifying criteria which will allow you to pinpoint exactly the data you wish to see, rather than seeing all of the available data. Filtering does not change the content of your worksheet, only what you see of the worksheet. No information is deleted, only the view of the data is changed. We’ve already seen that when we create tables in Excel that each column gets a Pull Nam menu which gives access to the Auto filter icons. This is activated by default for tables. You can still filter data which has not been formatted as a table by activating the filter option from the Data tab and the Filter option in the Sort and Filter group this button toggles the filter pull down on and off.

In its simplest form, the Auto filter finds and displays the roles where the value in the selected column meets the criteria that you specify. All other roles are hidden from view until you change the filter criteria or you turn the Auto filter off. Nothing is deleted, so you don’t need to worry when the data seems to disappear. You’ll see number, text or date filters displayed in this pull down menu, depending on whether the data in the selected cells are text, numeric or dates in nature. Let’s look at each type. We look at one of the numeric volumes, so I select August as an example. Ignore the Sort options for the moment as we’ll see those in the next lecture.

So first of all, you’ll always see a list which contains one instance of every value in the column in question and they will all be selected by default. Now I can turn off the Select All default and instead look for a particular value by just clicking on the one that I want straight away. You can see that I now only see records which meet the criteria specified. Also notice that the header and the column which I used for the filter has this small funnel icon indicating that the data has been filtered and has been filtered on this column. In the Sort and Filter group you can see that the Clear command has been activated.

This will clear your selection and restore all of the available data to the screen. So that’s a very straightforward way to filter the data. The number of filters options will be available on every column which has numeric data and provides a way to specify more complex criteria for the filter. Each one of these will invoke the custom Autofilter dialog box so that you can specify what it is you want to filter out. For example, if I select the between option, I can now put in the values I want to search for in the dialog. I can also specify more billion operators and be very specific about the numbers I want to filter on. So say I wanted to find records where the value was greater than 1000 but less than 4000. Then I select the and criteria and use the appropriate operators greater than 1000 and less than 4000.

Click OK and I can see the result. The top ten auto filter actually allows you to select any number of roles, not just ten. And finally you can select the number of rows or percentage of rows. For example, 10% or 20 rows will result in only two rows being displayed. If the data in the column are text based data, then the commands will be different and you’ll have the text filters instead of the number filters options. In these cases, Excel will look for whatever snippet of text you specify. So for example, if I specify A or as a snippet and specify contains as the operator, then we get our CFA. As a result, if I specify on then I get two results because that snippet of text is in both of these airport names. If the data are dates, then we get a date filter option instead, which gives us a very large number of options to select from. You can also filter a result that has already been filtered. So say I want to see all records where passenger numbers in May exceeded 5000. Then I can easily find that information like so, but I also want to see those records where the August numbers exceeded 9000 and I can filter on the August volume also. Now you can see that the filter icon is shown in both volumes indicating that filters have been placed on.

2. 3.3.2 and 3.3.3 Sort Data

Sorting means organizing your data so that you can easily find the information you’re looking for. For example, in our list of passenger numbers, you might want to see the data so that the highest total appears at the top, and then the rest are listed in decreasing order. Excel makes it very easy by providing a Sort option on each of the columns drop down menus in the table. So all I have to do is select the column I want to sort on, in this case the total column, and you can see that I can sort from largest to smallest. This Autofilter option is available on each column. However, it’s limited because you can only use it to sort on one column, irrespective of whether your data is in a table or not. The Ribbon has two ready to use buttons to enable sorting. On the Home tab, in the editing group, click Sort and Filter and you can see that we have the options to sort A to Z to Sort in Ascending sequence, or sort Z to A to Sort and Descending sequence.

We also have these options on the Data tab and in the Sort and Filter group. The opportunity to see will depend on the type of data you’re sorting. If the data is text, the Ribbon menu will display Sort A to Z and sort Z to A. If the data contains numbers, the ribbon menu will display sort smallest to largest and sort largest to smallest. If the data contains date values, the ribbon menu will display sort oldest to newest and sort newest to oldest.

As an example of sorting using text, we can select the Airport Name column and you can see that the Sort from Z to A reverses what is there already, which had been sorted from A to Z. In many cases you need to sort by more than one column to handle situations with multiple rows with the same value. For example, you may have a customer list with several people having the same last name. In this situation, you want to sort the list using two columns, one containing the customer’s last name and the other containing their first name.

 If we expand our example dates for Hempus Airlines, you can see that we’ve added information and a new column indicating the arrival airport as well as the departure airport. I want to sort these data on two levels. The first sort I want is on the departure airport, and I want the information sorted alphabetically from A to Z. Then I want to sort the information based on the arrival airport, again alphabetically from A to Z. So when I do that, I should see a sorted list of the departure airport, starting with Araceife.

And then for each of the departure airports, I should see the arrival airports listed in alphabetical order. To do this, we go into the Data tab and click on the Sort button. This now provides a dialog box where you can specify multiple columns for the sort order. So I want the first or primary sort level to be Departure Airport, so I can select this from the list of available headings. These will always reflect the headings that you’ve used in your table. There are options to choose from here, but Values is the default and most used one. Then we can specify the order which we want as A to Z.

Next, we can use this button to add a level of sorting. You can see that the text says then by. So this is the next level. From the primary one you can also delete and copy levels. So in this second level I select the arrival airport and set the sort to be from A to Z. Let’s see the result. So we have Araceife listed first. Then within the records for Araceife, you can see that the arrival airports are listed alphabetically. From A to Z. We have Brussels, Dublin and Rome. As you look down the list, you’ll see the same pattern. The move up, move down buttons allows you to change the selected sort level higher or lower in the sorting sequence.

The Options button displays a dialog box that allows you to sort by columns or rows and to choose whether to treat the data as case sensitive or not. If you turn the case sensitive option on, then uppercase letters are treated as different characters than lowercase letters. For example, the letter A is not the same as the letter A. The My Data checkbox tells Excel whether to treat the first row or column of cells as labels or titles for the data below or to the right.

If you have headers and you don’t check this box, excel will treat the header row as those were the same type of data as all of the other information and will include it in the sorting process. Excel gives you the opportunity to select up to 64 columns arose at sort levels. It’s unlikely that you’re ever going to need that. It’s important to note that although the example I’ve shown you has been based on a table, you can sort data in any range of cells, even if they’re not constructed as a table. You don’t have to define the data data as a table in order to use the commands in the Data tab and on the Home tab in the editing group, sort and Filter options.

3. 3.3.4 Remove Duplicate Records

Cell has the ability to find and remove duplicate data roles from a cell range. This is often a useful feature for worksheets with a lot of data, and you want to ensure that only unique values are present in the data set. If you select any cell in the table, you can go to the Table Tools and Design Options, one of which is the Remove Duplicates option. You can select all columns or only select selected columns to be used for comparing the data. To illustrate this, I just select the Departure airport name and select the Remove Duplicates command. Now you can see that Excel has removed every duplicate occurrence of an airport name in the Departure column. If you look closely, the reason why Belfast seems to be there twice is that I’ve misspelled it. You can also see this option in the the Data tab. Remove duplicates option. When you’ve executed the command, Excel will tell you the number of duplicates removed and the number of unique values remain.

4. Exercises Domain 3.3

We’ll open the Helpless Airlines flight details workbook. Now we’re going to select a one to e 99, go into the data option and to the sort options, the data sort. Now we have the option to sort by particular heading, in this case destination on the values and we want the descending from A Z to now we’re going to add a level to that. We’re going to sort you using the flight number. In the seniors we select the flight number and the sending is smallest to largest. That’s fine. Press okay, that’s it. Then we clear the filters. We go into data and sorry, go into filters. We go to filter by Brussels. So we go into the destination. We can click all of these off, click Brussels on and there is our one record for Brussels.

Nobody wants to go to Brussels. Clear the filter. Next we’re going to filter Desert so that we’re looking at the numbers of passengers. So we have a number filter and we select between so between 75 greater than 75 and less than 150. Press OK. And you can see the records displayed. Next we’re going to put another filter to show records which meets criteria set of . 4, the one we just did, but which also have the Departure Airport name, which is a text filter containing so contains the words or the text on. Press OK and once more it’s filtered. Clear all those filters now. Now we’re going to show those records where the number of passengers is above average.

This is a number filter. So down to above average, that’s fine. So you can see we get text filters and number filters. It depends on the data that we have. We can also filter by cell color so we can go filter by color and we can select the color that we want to filter on that’s clear. It all filtered again. Now we’re going to sort on the Departure Airport field. So we got to sort Departure Airport ascending so that’s from A to Z, that’s very straightforward.

Now we’re going to select a subset of data so it’s a small set, all the bell fast records and we have to make sure we say my data has headers is unchecked and we can then select column A, B or C, et cetera. Now we don’t do that sort and remove Duplicates. So click on remove duplicates and we can select what we want to move the duplicates of, in this case Departure Airport. It tells you how many it’s actually taken away. We’ll undo that and then we remove duplicates based on the party airport and destination value. So you can have multiple selections on this.

So we’ve got two of them. Press OK and again, it tells you how many duplicates to where and what’s going to be removed. We don’t do that. Now we filter the database, only the destination where the text contains city. So this is a text filter. It’s a text filter on the destination field. And because it’s text filter, we can say contains. It contains and put in the word or the text city. There you go. Panama City, Panama City, Mexico City, et cetera. Clear the filter and that’s it. Finished.