Sorting & Managing Data in Excel
Note: This Lesson Plan is for advanced students. There are also Excel Lesson Plans available for beginners that are much easier. We are using this as a sample Lesson Plan so that advanced users can see that there are Lesson Plans targeted to their needs.
Manage data with Excel
Excel is a versatile program that can help manage all sorts of information and data. Excel is particularly good at repetitive calculations and data evaluation. Data can be evaluated in multiple ways, but two of the most frequently used are sorting the data and graphing the data. Sorting is simply putting the rows containing your data in order, based on the contents of one or more columns. The order can be either ascending or descending. For example, if your key, the value of the contents of the column you are sorting on, is alphabetic, the order could be either A, B, C, … (ascending) or Z, Y, W. … (descending). You will learn about sorting below and about graphing in Lesson 7.
Sort your data
Frequently, after you have entered data into a table within Excel, you want to look at the data in a different order. For example, let’s say you have a table of personal addresses that is normally ordered (sorted) on the field (column) Last Name. You now want to rearrange the data so that you can see how many addresses you have in each state. In this case, your key will be the name of the state from the State column. Sorting is accomplished by selecting the range to be sorted and using the Data menu and Sort command. Follow the steps below to explore the Sort command.
To download the practice file look beneath the Excel 2007 menu on the left side of this web page. You will see Download Image in large black type. You can download the file to your computer or you can open it from the web page. To open the MyAddressBook.xls practice file simply left-click the link, and to download it follow the "download image" directions.
1. Open MyAddressBook.xls practice file folder. The workbook opens.
2. Click any cell in the data area to be sorted. This example uses cell E12 in the State column.
Note: You must select the data to be sorted before you issue the command to sort. When you select a cell within the data and click the Sort button, Excel usually guesses the correct range containing all your data, but you should check to ensure that the guess is correct. Be aware that blank cells, blank rows, and blank columns within your data table can all cause Excel to guess wrong. To be sure of the correct selection, you could manually select cell range A7:G50. Also note that Excel will need to know which column you want to use to reorder that data. You specify the column in the Sort dialog box.
3. Click the Data tab on the ribbon. Click the Sort button in the Sort & Filter group. The Sort dialog box opens (see Fig. 1).
4. Click the down arrow at the right of the Sort by list box . A list of all the column titles drops down.
5. Click State on the dropdown list .
6. Leave the Sort On selection at Values, the default .
7. Click A to Z to set the order that data will be sorted . (A to Z is the default and is likely already checked.)
8. Click OK. The selected data in the spreadsheet is sorted by State. Note that within the selected table, each entire row is reordered according to the sorted order of the column you picked to sort by.
Note: One of the frequent errors in sorting is to have Excel guess incorrectly the range that contains all your data. For example, if Excel did not include the Address column in its guess, the data in that column would not be reordered with the other rows. Your data would thus be scrambled and almost impossible to recover in the correct order.
Sort by more than one column
In the steps above, you sorted your data on one column (also called a key or level), State. Excel can sort on up to 64 keys or levels. If, for example, you wanted to have the addresses first sorted by State and then by City, so that each city within a state would be in alphabetical order, you would follow the steps below.
1. Select cell range A7:G50. (Alternatively, you could click any cell within your data and let Excel guess at the data range. It is better practice to select the data range explicitly.)
2. Click Sort on the Ribbon. The Sort dialog box opens.
3. Click the Add Level button . A row is added to the window to allow entry of another level to sort by. This level is labeled Then by. Each time you click the Add Level button, another Then by level is added. Notice the Delete Level and Copy Levels buttons at the top of the dialog box.
4. Click the down arrow to the right of the Sort by list box . Click State on the dropdown list.
5. Click the down arrow to the right of the first Then by list box . Click City on the dropdown list. Note that you will accept the defaults for the Sort On and Order input boxes in both the Sort by and Then by levels.
6. Click OK. Your spreadsheet is now sorted by state, then by city.
To add a third level of sorting, say by Address, follow the steps below.
1. Click Sort on the ribbon. The Sort dialog box opens.
2. Click the words Then by on the second level to select it.
3. Click the Add Level button . A third row is added allow entry of another level.
Note: If the newly added level is not added directly beneath the Then by City level, click the words Then by of the newly added level to select and use the up arrow or down arrow at the top of the dialog box to re-position it below the Then by City level.
4. Click the down arrow to the right of the second Then by list box. Click Address on the dropdown list.
5. Click OK. Your spreadsheet is now sorted by state, then by city, then by address. A portion of your worksheet is shown in Fig. 5.
Note that each state is grouped together. Within each group of states, the cities are grouped together, and within each city, the addresses are in order. But look at the five entries for Aiken. Virginia Ave. is first and Eighth Ave. comes after Laurens St. You intended that the addresses be in alphabetical order. What’s going on?
Excel has a defined order by which it sorts data. Numbers are sorted first, followed by punctuation, followed by text. To make things more difficult, the street numbers associated with the Address column are not numbers, they are text since they are in a cell that contains both text characters and numerical characters. This can create a problem since numbers that are text do not necessarily sort in numerical order. For example, if you sort 1, 8, 11, 23 as text, the result will be 1, 11, 23, 8. This is because for text, Excel sorts all the first characters, then all the second characters. The way to make these numbers sort in numerical order is to make them 01, 11, 23, and 08. For this reason, Excels assumes that a number beginning with zero is intended to be text. Mixing text and numbers occurs frequently enough that Excel will, in many circumstances, warn you with a message when you try to sort a range containing cells with both text and numbers. The point here is to always check your sorted results to ensure you got the result that you were expecting.
Sort data without a header row
Look at the Sort dialog box below. Notice the check box related to headers.
Headers refer to column labels or titles, such as those in row 7 of your worksheet. If you click this checkbox to remove the check mark, row 7 will sort with your data as if it were an address row rather than a column label row. Let’s see how this works:
1. Click the Sort button on the Ribbon.
2. Click the My data has headers checkbox to clear the check mark. The dialog box changes as shown below. The Sort By list box now contains column headings (E, D, and C) instead of column labels (State, City, Address).
3. Click OK. You are returned to the spreadsheet and it will be sorted by Column E first, then by Column D, then by Column C. Note that the column label row (the header row) sorted within the data and is now row 39 because you cleared the “My data has headers” checkbox.
4. Click the Undo button on the Quick Access Toolbar to return your sheet to its status before the sort above. While this moves the column labels back to the top of your data table (row 7), it does not restore the check mark to the Sort dialog box.
5. Click the Sort button on the Ribbon.
6. Click the My data has headers check box to restore the check mark.
Sort with the case sensitive option
There may be situations where you want the sort to differentiate between upper and lower case letters. If this situation occurs, you need to specify that the sort be case sensitive. First, you will sort on Last Name to examine the order of a sort without the case sensitive option selected. Then you will re-sort after selecting this option. Do the following:
1. Click the Sort button on the ribbon. The Sort dialog box opens.
2. Change the entry in the Sort by list box to Last Name and the entry in the first Then by list box to First Name.
3. Click the second Then by level to select it and click the Delete Level button at the top of the dialog box.
4. Click OK. You are returned to the spreadsheet.
5. Scroll down to the last names beginning with MC. Take note of the order of these names. The upper case MC is before the mixed case Mc in both names beginning with MC.
6. Re-open the Sort dialog box by repeating Step 1.
7. Click Options . The Sort Options dialog box opens.
8. Click the Case sensitive check box . A checkmark will be placed in the check box.
9. Click OK. You are returned to the Sort dialog box.
10. Click OK. You are returned to the spreadsheet.
11. Scroll down to the last names beginning with MC. The lower case Mc now sorts ahead of the upper case MC.
12. Go back to the Sort Options dialog box and uncheck the Case sensitive check box as you usually do not want sorting to be case sensitive.
I sincerely hope you benefited and enjoyed this Lesson Plan. Don't stop now. Continue your journey by clicking on another Free Lesson Plan on the right or click on the Enroll Now button below and become one of our enthusiastic LifeLong Learning students and take your choice from over 400 Lesson Plans.