Sorting a Spreadsheet

Everyone knows that spreadsheets are good at handling numbers. However they are also good for tabulating data. One use might be to track data points over different time periods, for example.

Let us assume for a minute that we have a spreadsheet containing a daily expense amount. Using Excel's built in functions we can quickly find the average or sum of these numbers, but what if we wanted to find out how many days we spent over $100? One easy way is to let Excel sort the data for us.

Using Excel's capability for data handling, we can have Excel do all the work, and maintain the data relationships. In other words, Excel is smart enough to keep each row's data together when sorting.

To experiment, open a blank spreadsheet. In cells A1 and B1 enter column1 and column2. In cells A2-A5 enter a, b, c, and d, and in cells B2-B5 enter 1, 2, 3, and 1. Now select these ten cells, and click the Data/Sort... menu item. Excel recognizes your column headers, and prompts you for how you wish to sort the data. Note also how you can sort on multiple columns at once.

To limit the data displayed, you can force Excel to hide certain rows. For instance, click Data/Filter/AutoFilter. Excel creates pulldowns for each column. In Column2, select Custom... where you can pick is greater than or equal to 2. The rows containing "a" and "d" disappear. Use the pulldown again to select All to display all rows.

Excel does save the current state the spreadsheet when saving the file, so if you wanted the original sort order or filters, be sure to save the file as a different name, or copy the data to a different tab in the workbook before manipulating it.

August 2003

Send this article to a friend!
Subscribe to The ITS Connection