Excel is great for tables and data, but how can you use and structure it so that it meets your needs? The Sort tool lets you quickly sort columns with various formats, or you can create your own formats for multiple columns and data types. Use the Sort function to organize your data and make it easier to understand and use.
Step
Part 1 of 3: Sorting Alphabetically or By Number
Step 1. Select your data
You can click and drag (click and drag) to select the column you want to sort by, or you can click a cell in the column to make that cell active and let Excel sort the data automatically.
All data in a column needs to be arranged by type to be sorted (example: text, numbers, dates)
Step 2. Look for the Sort button
This button is located on the Data tab in the Sort & Filter section. To sort quickly, use the "AZ↓" and "AZ↑" buttons.
Step 3. Sort your columns by clicking the appropriate button
When you sort numbers, you can sort them from lowest to highest ("AZ↓") or from highest to lowest ("AZ↑"). When you sort text, you can sort it alphabetically ("AZ↓") or in reverse alphabetical order ("AZ↑"). When you sort dates or times, you can sort them from earliest to latest ("AZ↓") or from earliest to earliest ("AZ↑").
If there is another column containing data besides the column you are sorting, a question window will appear asking if you want to enter the data for sorting. The result of the order is still based on the column you selected, but the data next to that column will follow the order according to the data in the selected column
Step 4. Problem with column that won't sort
If you encounter an error message when you try to sort, the most common cause is a format problem in the data.
- If you're sorting numbers, make sure all cells are formatted as numbers and not text. Figures can be accidentally 'imported' or read as text from certain accounting programs or applications.
- If you sort text, errors can arise from the presence of spaces or incorrect data formatting.
- If you sort by date or time, the problem usually arises from the format of the data. In order for Excel to sort by date, you must ensure that all of your data is formatted as dates.
Part 2 of 3: Sort by Multiple Criteria
Step 1. Select your data
Say you have a spreadsheet with a list of customer names and their city locations. Then you want to sort the cities alphabetically, then sort each customer alphabetically as well within each city. You can do this by creating a Custom Sort.
Step 2. Click the Sort button
It's located in the "Sort & Filter" section of the Data tab. A window will appear, allowing you to create a specific sequence with various criteria.
If your column has headers in the first cell, for example “City” and “Name”, make sure to check the My data has headers box in the upper left corner of the Sort window that appears earlier
Step 3. Make your first settings
Click the Sort by menu to select the column you want. In this example, you will first sort the city, so select the appropriate column from the menu.
- On the "Sort on" menu select "Values".
- Arrange the order "A to Z" or "Z to A" to your liking.
Step 4. Make the second setting
Click the Add Levels button. This will add one more setting below the first setting. Select the second column (Column Name in this example), then adjust the settings (for easier reading, choose the same settings menu as in the first setting).
Step 5. Click OK
Your list will be sorted by the settings you made earlier. You can see cities sorted alphabetically, and customer names sorted alphabetically within each city.
This example is a simple example and only includes two columns. However, you can create complex sequences and include multiple columns if you wish
Part 3 of 3: Sort by Cell Color or Letter
Step 1. Select your data
You can click and drag (click and drag) to select the column you want to sort by, or you can click a cell in the column to make that cell active and let Excel sort the data automatically.
Step 2. Click the Sort button
It's located on the Data tab in the "Sort & Filter" section. The Sort window will appear. If you have another column containing data next to the column you selected, you will be asked if you want to enter that data.
Step 3. Select "Cell Color" or "Font Color" on the "Sort On" menu
This will allow you to select the first color you want to sort.
Step 4. Choose which color you want to sort first
In the Order menu, you can use the 'drop down' menu to select which color you want to put first or last in the order. You can only choose the color that is in the column.
There is no 'default' or default setting for sorting colors. You have to make the arrangements yourself
Step 5. Add another color
You will need to add other settings for each color you sort. Click Add Level to add settings. Select the next color that you want to sort by, then set the arrangement orders in the order you want to make them.
Make sure each setup command is the same for each setting. For example, if you're sorting from top to bottom, make sure in the Order menu for each setting you select On Top
Step 6. Click OK
Each setting will take effect individually, and the columns will be sorted according to the color you have set.
Tips
- Try sorting the data in a different way; Different points of view can show different aspects of the information presented.
- If your spreadsheet has sums, averages or other recapitulation information below the information you want to sort, be careful not to sort them too.
