Computer Concepts in Action

Unit 5: Spreadsheets

Use Multiple Criteria Sorts in Excel

In Excel you can sort data by specific column or by multiple columns. Using this feature you can quickly and easily alphabetize names or put numbers in order.

Once the data on your worksheet is complete, you may want to change the way the worksheet looks. Formatting can give a worksheet a distinct style or make data easier to read. You can use the AutoFormat tool to apply pre-existing formats to a worksheet.

To open the data file in Step 1, click on the link. If you are not able to download the file, ask your teacher for help.

1. Start Microsoft Excel, then open your Allowance file. (Note: If you did not complete Enrichment Activity 5.1, use the Allowance (17.0K) data file for this activity.)
2. Click cell B4. Drag the pointer to cell I9 and release (see Figure 1).

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0078612357/271281/EA5_2_01.JPG','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (103.0K)</a>
Figure 1 Select cells B4 through I9.

3. Click the Data menu, and then click Sort. The Sort box opens (see Figure 2).
4. Use the Sort by arrow to choose Column D.
5. Click Descending in the Sort by section.
6. Use the Then by arrow to choose Column E.
7. Click Descending in the Then by section (see Figure 2).

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0078612357/271281/EA5_2_02.JPG','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (18.0K)</a>
Figure 2 The Sort box lets you sort multiple columns in Excel.

8. Click OK. Deselect the cells by clicking outside the area.
9. The cells have been rearranged to arrange the monthly allowance from highest to lowest. Then, for each allowance amount, the largest amount spent on fast food is displayed first (see Figure 3).

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0078612357/271281/EA5_2_03.JPG','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (103.0K)</a>
Figure 3 The final worksheet

10. Click the Format menu, then choose AutoFormat.
11. Scroll through the AutoFormat options and choose the List 2 format (or a format of your choice).
12. Click OK. Your worksheet will look like Figure 4.

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::EA5_2_04::/sites/dl/free/0078612357/271281/EA5_2_04.JPG','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif">EA5_2_04 (334.0K)</a>EA5_2_04

13. Save and close your Allowance file.
14. Exit Excel.