Microsoft Excel has many helpful commands that can be used to manage large files. Microsoft Access is specifically designed to handle large databases, but Excel can be a simpler option for data that needs to be manipulated for reporting purposes.
Sorting of data is easy, but there are steps that need to be followed to make sure that the data remains correct, and tricks to make collating the data fast and accurate.
Sorting using the Data Sort Command
In order to use the sort command, Excel needs at least 2 rows of data in at least 1 column. The best use of the command is for many rows of data, where manually sorting is difficult.
Excel will make a determination of what data should be sorted based on where the cursor is. Make sure that all columns are included in the sorting area before beginning the sorting process. If there is a question on what data should be sorted, highlight the appropriate rows and columns.
Follow these steps:
- From the toolbar, select Data, and then Sort.
- A box pops up, offering up to three sorting options.
- Choose the first option (“sort by”) and select the column that you want to sort by, and whether the sort should be ascending or descending. Excel will default to the column where the cursor is located and ascending order. Using ascending will sort from A to Z and from 1 to 10, and descending will sort the opposite (Z to A and 10 to 1.) If the column includes both letters and numbers, in ascending mode, numbers will precede letters. Blank spaces will follow letters.
- The final selection in the box is whether the data has a header row, or title. Titles would normally be excluded from the sort, and Excel will make an assumption on whether the first line is a header row. Change the selection as needed.
- Clicking “OK” will execute the sort.
- Review the results. If there is a concern that the sort was performed improperly, click “Back” icon and start again. Re-sorting from a bad result will corrupt the original data, and make it impossible to assure that the data in the rows match correctly.
An Example of Multiple Sorting
Using Data Sort provides the ability to sort the data on 3 different columns in one action. For example, a spreadsheet could have 3 columns for football players, one each for name, team and position.
Data Sort can arrange the order by team, then position and finally by name in alphabetical order by selecting the columns in that order in the sort box.
Sorting Using the Sort Icon
The 2 sort icons have AZ or ZA with an arrow next to them, and are located on the standard toolbar. The buttons have the same function as Data Sort. They execute the sort faster, but are more limited in what they can do.
Things to be careful about when using the sort icon:
- Excel will assume that the sort column is where the cursor is located, and all data contiguous (attached) to that cell will be sorted, both rows and columns.
- If there is a blank column (visible or hidden) in the area to be sorted, the rows will not sort properly.
- If there is a column without a header contiguous to the data, Excel will sort the header row into the data, in error. Make sure all the columns have titles.
- If there is a column with a total that is contiguous to the data, Excel will sort the sum into the data, in error. Make sure there is a row separatiing the data from the total.
Data Sort is a valuable Excel command, allowing the user to manage large files easily, and get the most out of this Microsoft Office tool.
