How to Sort Multiple Rows and Columns in Excel
※ Download: What is sorting in excel
The sort key is the data in the column or columns you want to sort; it is identified by the column heading or field name. The sorting tool allows you to sort by date, number, alphabetic order and more. The pop-up window has choices for Expand the selection choose this one! In this example, I have sorted the data for two columns.
And also, as I mentioned earlier discussion, I'm getting blank cells between rows. Make sure that you have the proper locale setting in Regional Settings or Regional and Language Options in Control Panel on your computer.
Sort data in a range or table - For example, in the screen shot below, hidden column E is blank, so columns at the left are not selected. So, you get handed an Excel spreadsheet with thousands of rows inside of it, and you realize the data is all out of order.
This tutorial will teach you a few quick and easy ways to put Excel in alphabetical order. It also provides solutions for non-trivial tasks, for example how to alphabetize by last name when the entries start with the first name. Alphabetizing in Excel is as easy as ABC. Whether you are sorting an entire worksheet or selected range, vertically a column or horizontally a row , ascending A to Z or descending Z to A , in most cases the task can be accomplished with a button click. In some situations, however, the built-in features may stumble, but you can still figure out a way to sort by alphabetical order with formulas. This tutorial will show you a few quick ways to alphabetize in Excel and teach how to foresee and prevent sorting problems. Below you will find the detailed guidance on each method. After you've done sorting and before you do anything else, take a close look at the results. If something looks wrong, click the Undo button to restore the original order. Alphabetize and keep rows together If your data set contains two or more columns, you can use the A-Z or Z-A button to put one of the columns in alphabetical order and Excel will automatically move the data in other columns, keeping the rows intact. As you can see in the sorted table on the right, the related information in each row is kept together: In some situations, mostly when just one or a few cells in the middle of your data set are selected, Excel is unsure which part of the data to sort and asks for your instructions. If you'd like to sort the entire table, leave the default Expand the selection option checked, and click Sort: Filter and alphabetize in Excel Another quick way to sort alphabetically in Excel is to. The beauty of this method is that it's one-time setup - once the auto filter is applied, the sort options for all columns are only a mouse click away. Click the drop-down arrow for the column you want to put in alphabetical order, and select Sort A to Z: The column is alphabetized straight away, and a small upward arrow on the filter button indicates the sorting order ascending : To reverse the order, select Sort Z to A from the filter drop-down menu. To remove the filter, simply click the Filter button again. How to put multiple columns in alphabetical order In case you want to alphabetize data in several columns, use the Excel Sort command, which gives more control over how your data is sorted. In most cases, you can select just one cell and Excel will pick the rest of your data automatically, but this is an error-prone approach, especially when there are some gaps blank cells within your data. In the Sort by dropdown box, select the column you want to alphabetize first, Region in our case. In the other two boxes, leave the default settings: Sort On - Cell values and Order - A to Z: Tip. If the first dropdown is showing column letters instead of headings, tick off the My data has headers box. In this example, the second level sorts the values in the Name column alphabetically from A to Z: Tip. If you are sorting by multiple columns with the same criteria, click Copy Level instead of Add Level. In this case, you will only have to choose a different column in the first box. Excel will sort your data in the specified order. As shown in the screenshot below, our table is arranged alphabetically exactly as it should: first by Region, and then by Name: How to sort rows alphabetically in Excel If your data is arranged horizontally, you may want to sort it alphabetically across rows. This can also be done by using the Excel Sort feature. If your table has row labels that should not be moved, be sure to leave them out. In the other two boxes, the default values will do just fine, so we keep them Cell Values in the Sort on box, and A to Z in the Order box , and click OK: As the result, the first row in our table is sorted in alphabetical order, and the rest of the data is rearranged accordingly, preserving all correlations between the entries: Problems with sorting alphabetically in Excel Excel sort features are amazing, but if you are working with an imperfectly structured data, things may go terribly wrong. Here are the two common issues. An easy fix is to eliminate the blanks and unhide all hidden areas before sorting. Or, select the entire table first, and then alphabetize. Unrecognizable column headers If your column headers are formatted differently from the rest of the data, Excel is smart enough to identify them and exclude from sorting. But if the header row has no special formatting, your column headers will most likely be treated as regular entries and end up somewhere in the middle of the sorted data. To prevent this from happening, select only the data rows, and then sort. When using the Sort dialog box, make sure the My data has headers checkbox is selected. How to sort alphabetically in Excel with formulas Microsoft Excel provides a variety of features to cope with many different tasks. Many, but not all. If you are facing a challenge for which there is no built-in solution, chances are it can be accomplished with a formula. It is also true for alphabetical sorting. Below, you will find a couple of examples when alphabetical order can only be done with formulas. How to alphabetize in Excel by last name Since there are a few common ways to write names in English, you may sometimes find yourself in a situation when the entries start with the first name while you need to alphabetize them by the last name: Excel's sort options cannot help in this case, so let's resort to formulas. For this, select all the formula cells E2:E10 and press Ctrl + C to copy them. Right-click the selected cells, click on Values under Paste Options, and press the Enter key: Good, you are almost there! The process may look a bit complex on paper, but trust me, it will take only a few minutes in your Excel. In fact, it will take even less time than reading this tutorial, let alone alphabetizing the names manually : How to alphabetize each row individually in Excel In one of the previous examples we discussed how to by using the Sort dialog box. In that example, we were dealing with a correlated set of data. But what if each row contains independent information? How do you alphabetize each row individually? In case you have a reasonable number of rows, you can sort them one by one performing. If you have hundreds or thousands of rows, that would be an enormous waste of time. Formulas can do the same thing much faster. As you do this, Excel will enclose the formula in curly braces. Do not try typing the braces manually, that won't work. The above formula works with a couple of caveats: your source data should not contain empty cells or duplicate values. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our worksheet. I thank you for reading and hope to see you on our blog next week! Ex, In one column, until row 10 have data in another column have data until 20. In this situation, how to sort out the data in all column in A to Z format. But, in some columns in between blank cells came. One more question, by reading this i understood that for sorting out multiple columns i have to use add level. But, when the column numbers are more means each time how can i go change the column number. If there is other way, i could save my time. Sivakumar: Can you sort each column separately or do all the columns have to be sorted as one big selection? If you can sort each column by itself you can avoid the blank row problem. If the data in each column has to stay with its respective row across the entire data set, you're doing it the only way it can be done. You have my sympathy. Sivakumar: When you sort each column individually, are all the blank cells together? Have you checked to be certain they are empty? They may contain spaces, breaks or other non-printing characters. I was thinking about your situation yesterday. I wonder if it is a situation where you just have to manually clean the data. It sounds as if the multiple column sorting won't give you the result you want now. As you mentioned, I tried many times with the multiple column sorting. But, it doesn't work. Column C has data. But, only 1 to 5 Disorganized. Now if I try to sort out the data by using multiple column method, it is not sorting out all the columns either largest to smallest or vice-versa. Is there any other way? And also, as I mentioned earlier discussion, I'm getting blank cells between rows. Since there is value or data in the blank cells.
Read on for a step-by-step guide on how to sort data in Excel spreadsheets, using simple to advanced techniques. Sorting requires the use of a and a sort order. I was thinking about your situation yesterday. In the Sort by dropdown box, select the column you want to alphabetize first, Region in our case. For what is sorting in excel, select all the formula cells E2:E10 and press Ctrl + C to copy them. This process is fairly simple. While this method works fine, I recommend using the sort dialog bo method. Note: when the data in one column is related to the data in the remaining columns of the table, you want to select Expand the selection. How to alphabetize in Excel by last name Since there are a few common ways to write names in English, you may sometimes find yourself in a situation when the entries start with the first name while you need to alphabetize them by the last name: Excel's sort options cannot help in this case, so let's resort to formulas. Note: For best results, the range of cells that you sort should have column headings.
