Download Article
Download Article
Excel is great for tables of data, but how can you manipulate and organize it so that it meets your needs? The Sort tool allows you to quickly sort columns by a variety of formats, or create your own custom sort for multiple columns and types of data. Use the Sort function to logically organize your data and make it easier to understand and use.
Steps
-
Select your data. You can either click and drag to select the column that you want to sort, or you can click one of the cells in the column to make it active and let Excel select the data automatically.
- All of your data in the column will need to be formatted in the same way in order to sort (i.e. text, numbers, dates).
-
Find the Sort buttons. These can be found in the Data tab in the "Sort & Filter" section. To do quick sorts, you will be using the "AZ↓" and "AZ↑" buttons.Advertisement
-
Sort your column by clicking the appropriate button. If you are sorting numbers, you can sort from lowest to highest ("AZ↓") or highest to lowest ("AZ↑"). If you are sorting text, you can sort in ascending alphanumeric order ("AZ↓") or descending alphanumeric order ("AZ↑"). If you are sorting dates or times, you can sort from earlier to later ("AZ↓") or later to earlier ("AZ↑").
- If there is another column of data next to the one you are sorting, you will be asked if you want to include that data in the sort. The sort will still be the column you originally selected, but associated columns of data will be sorted along with it.
-
Troubleshoot a column that won't sort. If you are running into errors when you try to sort, the most likely culprit is formatting problems with the data.
- If you are sorting numbers, make sure that all of the cells are formatted as numbers and not text. Numbers can be accidentally imported as text from certain accounting programs.
- If you are sorting text, errors can arise from leading spaces or bad formatting.
- If you are sorting dates or times, troubles usually stem from the formatting of your data. In order for Excel to sort successfully by date, you need to ensure that all of the data is stored as a date.
-
Select your data. Let's say you have a spreadsheet with a list of customer names as well as the city that they are located in. To make your life easier, you want to sort first alphabetically by city, and then sort each customer alphabetically within each city. Creating a custom sort can do just that.
-
Click the Sort button. This can be found in the "Sort & Filter" section of the Data tab. The Sort window will open, allowing you to create a custom sort with multiple criteria.
- If your columns have headers in the first cell, such as "City" and "Name", make sure to check the "My data has headers" box in the upper-left corner of the sort window.
-
Create your first rule. Click the "Sort by" menu to choose the column that you want. In this example, you would first be sorting by city, so select the appropriate column from the menu.
- Keep "Sort on" set to "Values".
- Set the order to either "A to Z" or "Z to A" depending on how you want to sort.
-
Create your second rule. Click the "Add Level" button. This will add a rule underneath the first one. Select the second column (The Name column in our example) and then select the sort order (for ease of reading, choose the same order as your first rule).
-
Click OK. Your list will be sorted according to your rules. You should see the cities listed alphabetically, and then the customer names sorted alphabetically within each city.
- This example is a simple one and only includes two columns. You can, however, make your sort as complex as you would like, and include many columns. [1] X Research source
-
Select your data. You can either click and drag to select the column that you want to sort, or you can click one of the cells in the column to make it active and let Excel select the data automatically.
-
Click the Sort button. The Sort button can be found in the Data tab in the "Sort & Filter" section. This will open the Sort window. If you have another column of data next to the one you are sorting, you will be asked if you want to include that data in the sort.
-
Select "Cell Color" or "Font Color" in the "Sort On" menu. This will allow you to choose the first color you want to sort by.
-
Select which color you want to sort first. In the "Order" column, you can use the drop down menu to choose which color you want to put first or last in the sort. You can only choose colors that are present in the column.
- There is no default order for color sorting. You will need to define the order yourself.
-
Add another color. You will need to add another rule for each color in the column you are sorting. Click the "Add Level" to add another rule to the sort. Choose the next color that you want to sort by, and then the order that you want to sort.
- Make sure that each order is the same for each rule. For example, if you are sorting from top to bottom, make sure the "Order" section for each rule says "On Top".
-
Click OK. Each rule will be applied one by one, and the column will be sorted by the colors you defined.
Community Q&A
-
QuestionHow can the sort function on Microsoft Word help me?Community AnswerLet's say you are a teacher and you have a database of students with their assignment grades. By sorting on assignment type and grade received, you can find the students who need more help. In other words, sorting allows one to focus attention where it is most needed.
-
QuestionI tried to sort by color, but the only thing in the Sort On dropdown is "no cell color". Why do I not have any colors to choose from?Community AnswerYou have to format your data's cells with colors first in order to sort by color.
-
QuestionHow do I store results of a sort into a new table while retaining the original table?Community AnswerCopy the table (and show results of your sort) into a new page.
wikiHow Video: How to Sort a List in Microsoft Excel
Tips
- Try sorting data in different ways; these new perspectives can illuminate different aspects of the information.Thanks
- If your spreadsheet has totals, averages or other summary information below the information you need to sort, be careful not to sort those as well.Thanks
Warnings
- Be extremely careful to select all of the columns you need when sorting.Thanks
References
About This Article
To sort your data alphabetically or numerically, start by highlighting the cells you want to sort. Then, click the "Data" tab at the top of Excel. In the "Sort & Filter" panel in the toolbar, click the AZ with a down-arrow to sort in alphabetical or numerical order, starting with the smallest values first, or AZ with an up-arrow to reverse that sorting method, or starting with the largest values first. You can also sort by font or cell color. With your data selected, click the Data tab, and then click "Sort" on the "Sort & Filter" panel. If your data has headers, check the box near the top-right corner. Select a column, and then choose how to sort it, such as by cell or font Color. Click the "Order" menu to choose a color to sort first, and select "On Top" from the drop-down menu. To add the next color in the sequence, click "Add Level," and select the same column and sorting method. Choose the color that should appear next and select "On Top" again. Keep adding rules for the column until you've placed all colors in order, and then click "OK" to sort. Just like adding rules to sort by color, you can add rules to sort columns by multiple criteria, such as both alphabetically and by cell color. In the Sort window, select a column, and then enter the sorting method and criteria. Click "Add Level" to add another rule, pick the same column, and then choose a different sorting method. Click "OK" when you're finished to save and sort.
