Excel 2013 & 2016: File Management—Sorting

Updated 2013.08.07

Objectives:

·        Learn how to import text files into Excel.

·        Learn the meaning of the terms field, record, and file.

·        Learn how to assign names to ranges (tables) and columns.

·        Learn how to sort data using one or more key fields.

Sorting

One of the most common applications of Excel is not to do lots of calculations (spreadsheet stuff), but to do sorting and filtering (database, or "list" stuff).

 

Sorting: putting a list in order based on a specific field (column) called a key.

 

Filtering: selecting records from a list based on whether they match some filtering rule (criterion).

 

Example:

Open the Advising.txt file. This is a tab-delimited text file. This means that the columns of data are separated by tab characters, and when the file gets imported into Excel, every time Excel sees a tab character, it will put the data following the tab into a new column.

We need to import this file into Excel.

 

After importing the advising file into Excel, note: Each row represents a record and each column (or cell – you should be able to tell from the context whether the speaker is referring to a single cell or a column of cells) represents a field.

 

Field: the smallest unit of meaningful data (e.g. First Name or Zip Code). In Excel, a field is represented by a single cell. The yellow cell below is an example of a field.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image001.jpg

 

Sometimes, the term “field” refers to an entire column, for example the Major field:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image002.jpg

 

Record: a collection of related fields. In Excel, all of the fields making up a record are on the same row. The yellow cells below make up a single record.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image003.jpg

 

File: a collection of related records. In Excel, a file is usually stored on a single worksheet. Sometimes the word table is used instead of file.

 

Field names are usually given in the top row of the file. In the "Advising" file, field names are "First Name", "Last Name", "Major", "Advisor", "Credits", and 'ID Number". Note that column headings should be in a single cell. For example, do not put "Last" in row 1 and "Name" in row 2 when the field name is "Last Name".

Sorting

Ascending Sort:

·       Numbers: When using numbers, an ascending sort puts the data in increasing order.

·       Text: When using character string data, an ascending sort will put the data in alphabetical order.

·       Dates: When using date data, an ascending sort will put the data in chronological order (earliest events at the top of the list, most recent at the bottom).

Descending Sort:

·       Numbers: When using numbers, a descending sort puts the data in decreasing order.

·       Text: When using character string data, a descending sort puts the data in reverse alphabetical order.

·       Dates: When using date data, a descending sort puts the data in reverse chronological order (most recent events at the top of the list, earliest events at the bottom).

Example: Sort a list in ascending order after defining your data as a table:

·       Click on the down-arrow https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image004.jpg at the top of the column you wish to sort. Below I have clicked on the arrow next to the word "Major".

·       Select "Sort A to Z" from the drop-down menu.

·       Excel will sort the data using that column as a key.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image005.jpg

Example: Sort a list without defining your data as a table:

·       Place the cursor in the column you wish to use as a sort key. In the example below, we want to put the records in alphabetical order based on the major, so the cursor is in the Major field.

·       Click on the Data tab.

·       In the Sort & Filter group, click on the Sort A to Z https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image006.jpg button.

·       Excel will sort the data using that column as a key.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image007.jpg

Example: Sort a list in descending order after defining your data as a table:

·       Click on the down-arrow https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image004.jpg at the top of the column you wish to sort. Below I have clicked on the arrow next to the word "Major".

·       Select "Sort Z to A" from the drop-down menu.

·       Excel will sort the data using that column as a key.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image008.jpg

Example: Sort a list in descending order without defining your data as a table:

·       Place the cursor in the column you wish to use as a sort key. In the example below, we want to put the records in alphabetical order based on the major, so the cursor is in the Major field.

·       Click on the Data tab.

·       In the Sort & Filter group, click on the "Sort Z to A" https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image009.jpghttps://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image010.jpg button.

·       Excel will sort the data using that column as a key.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image011.jpg

 

Warning! Never make a selection before clicking on the sort button. If you do, the following dialog box will appear:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image012.jpg

If you choose Continue with the current selection, only the data that are selected will be sorted! The correct answer to the question posed by the Sort Warning dialog box is almost always Expand the selection. If you choose the wrong answer here, this can make a mess of your data in a hurry. However, if you click the Undo button right away, the mess can be un-done. Note that Excel only allows you to undo a sort for about a minute. If you wait too long, your data are permanently scrambled! If no range is selected at the time the sort button is clicked, Excel will try to guess the data range that you wish to sort. If the cursor is in the list of records that you wish to sort, Excel will probably guess correctly. However, if the cursor is outside of the list of records you wish to sort, you will have to tell Excel where the data are by highlighting the appropriate range.

Sorting with multiple keys

Sort the data based on last name. Note that there are several students who have the same last name. When two people have the same last name, they should be further sorted using their first name as a secondary key. However, the buttons on the toolbar only allow us to sort using a single key – the column that the cursor is in at the time the sort button is clicked. If we want to use secondary keys, we must use the "Data" | "Sort" command.

Example: Sort a list using multiple keys:

·       Place the cursor somewhere in the data.

·       Click on the Data tab.

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

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image013.jpg

·       Click on the drop-down box in the Column area and select your first key.

·       For each additional key field:

·       If you have another key, click on the Add Level button at the top left of the dialog box.

·       Click on the drop-down box in the Column area and select your next key.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/008-A-Lists-Sorting_files/image014.jpg

·       If there are header rows (field names), turn on the My data has headers check box.

·       Click on OK.

·       Excel will sort your data. In the example above, students will be sorted by Major first. For students with the same major, they will be sorted by Advisor. For students with both the same major and the same advisor, they will be sorted by Credits with the students with the largest number of credits appearing at the top of the group.