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. How to alphabetize in Excel Overall, there are 3 main ways to sort alphabetically in Excel: A-Z or Z-A button, the Sort feature, and filter. Below you will find the detailed guidance on each method. How to sort a column alphabetically The fastest way to sort alphabetically in Excel is this:.
In the Sort by Column dropdown, choose Department as the option to sort on. In the Order dropdown, choose “Custom List“. Click on NEW LIST, and press Add. The cursor will move to the Empty Box in the popup. Key in the list of departments here – one per line, in the SORT order you need. Then Click OK.
Select any cell in the column you want to sort. On the Data tab, in the Sort and Filter group, click either A-Z to sort ascending or Z-A to sort descending. The same buttons can also be accessed from Home tab Editing group Sort and Filter: Either way, Excel will alphabetize your list instantaneously. 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.
![Sort Sort](/uploads/1/2/5/6/125661389/638596692.jpg)
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.
Adding a filter to your table is easy:. Select one or several column headers. On the Home tab, in the Editing group, click Sort and Filter Filter. Small drop-down arrows will appear in each of the column headers. 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. As an example, let's add one more column to our table, and then arrange the entries alphabetically first by Region, and then by Name: To have it done, please perform the following steps:. Select the entire table you want to sort. 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. On the Data tab, in the Sort & Filter group, click the Sort. The Sort dialog box will show up with the first sorting level created for you automatically as Excel sees fit.
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.
Hi, I want to sort out nearly 100 columns and in all the columns rows number is differ. 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.
I tried.But, in some columns in between blank cells came. Kindly help me. 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. Dear Doug/Kathy, Sorry for the delayed reply. As you mentioned, I tried many times with the multiple column sorting.
But, it doesn't work. Assume that, I've data in 3 columns like 10 to 1 in column A & B (Remember they're disorganized).
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.
In this article I will show you how to sort Excel data by several columns, by column names in alphabetical order and by values in any row. Also, you will learn how to sort data in non-standard ways, when sorting alphabetically or numerically does not work. I believe everyone knows how to sort by column alphabetically or in ascending / descending order. All you need to do is click the A-Z or Z-A buttons residing on the Home tab in the Editing group and on the Data tab in the Sort & Filter group: However, the Excel Sort feature provides far more options and capabilities that are not so obvious but may come in extremely handy:.
Sort by several columns Now I'm going to show you how to sort Excel data by two or more columns. I will do this in Excel 2010 because I have this version installed on my computer. If you use another Excel version, you won't have any problems with following the examples because the sorting features are pretty much the same in Excel 2007 and Excel 2013. You may only notice some differences in color schemes and dialogs' layouts. Okay, let's go ahead. Click the Sort button on the Data tab or Custom Sort on the Home tab to open the Sort dialog.
Then click the Add Level button as many times as many columns you want to use for sorting:. From the ' Sort by' and ' Then by' dropdown lists, select the columns by which you want to sort your data. For example, you are planning your holiday and have a list of hotels provided by a travel agency. You want to sort them first by Region, then by Board basis and finally by Price, as shown in the screenshot:. Click OK and here you are:.
Firstly, the Region column is sorted first, in the alphabetic order. Secondly, the Board basis column is sorted, so that all-inclusive (AL) hotels are at the top of the list. Finally, the Price column is sorted, from smallest to largest. Sorting data by multiple columns in Excel is pretty easy, isn't it?
However, the Sort dialog has plenty more features. Further on in this article I will show you how to sort by row, not column, and how to re-arrange data in your worksheet alphabetically based on column names. Also, you will learn how to sort your Excel data in non-standard ways, when sorting in alphabetical or numerical order does not work. Sort in Excel by row and by column names I guess in 90% of cases when you are sorting data in Excel, you sort by values in one or several columns. However, sometimes we have non-trivial data sets and we do need to sort by row (horizontally), i.e. rearrange the order of columns from left to right based on column headers or values in a particular row.
For example, you have a list of photo cameras provided by a local seller or downloaded from the Internet. The list contains different features, specifications and prices like this: What you need is to sort the photo cameras by some parameters that matter the most for you.
As an example, let's sort them by model name first. Select the range of data you want to sort. If you want to re-arrange all the columns, you can simply select any cell within your range. We cannot do this for our data because Column A lists different features and we want it to stick in place. So, our selection starts with cell B1:.
Click the Sort button on the Data tab to open the Sort dialog. Notice the ' My data has headers' checkbox in the upper-right part of the dialog, you should uncheck it if your worksheet does not have headers. Since our sheet has headers, we leave the tick and click the Options button. In the opening Sort Options dialog under Orientation, choose Sort left to right, and click OK. Then select the row by which you want to sort. In our example, we select Row 1 that contains the photo camera names. Make sure you have ' Values' selected under Sort on and ' A to Z' under Order, then click OK.
The result of your sorting should look similar to this: I know that sorting by column names has very little practical sense in our case and we did it for demonstration purposes only so that you can get a feel of how it works. In a similar way, you can sort the list of cameras by size, or imaging sensor, or sensor type, or any other feature that is most critical for you. For instance, let's sort them by price for a start. What you do is go through steps 1 - 3 as described above and then, on step 4, instead of Row 2 you select Row 4 that lists retail prices. The result of sorting will look like this: Please note that it's not just one row that has been sorted. The entire columns were moved so that the data was not distorted.
In other words, what you see in the screenshot above is the list of photo cameras sorted from cheapest to most expensive. Hope now you've gained an insight into how sorting a row works in Excel. But what if we have data that does not sort well alphabetically or numerically? Sort data in custom order (using a custom list) If you want to sort your data in some custom order other than alphabetical, you can use the built-in Excel custom lists or create your own.
With built-in custom lists, you can sort by days of the week or months of the year. Microsoft Excel provides two types of such custom lists - with abbreviated and full names: Say, we have a list of weekly household chores and we want to sort them by due day or priority. You start with selecting the data you want to sort and then opening the Sort dialog exactly like we did when or ( Data tab Sort button). In the Sort by box, select the column you want to sort by, in our case it is the Day column since we want to sort our tasks by the days of the week. Then choose Custom List under Order as shown in the screenshot:.
In the Custom Lists dialog box, select the needed list. Since we have the abbreviated day names in the Day columns, we choose the corresponding custom list and click OK. Now we have our household tasks sorted by the day of the week. If you want to change something in your data, please keep in mind that new or modified data won't get sorted automatically.
You need to click the Reapply button on the Data tab, in the Sort & Filter group: Well, as you see sorting Excel data by custom list does not present any challenge either. The last thing that is left for us to do is to sort data by our own custom list. Sort data by your own custom list As you remember, we have one more column in the table, the Priority column. In order to sort your weekly chores from most important to less important, you proceed as follows.
Perform steps 1 and 2 described above, and when you have the Custom Lists dialog open, select the NEW LIST in the left-hand column under Custom Lists, and type the entries directly into the List entries box on the right. Remember to type your entries exactly in the same order you want them to be sorted, from top to bottom: Click Add and you will see that the newly created custom list is added to the existing custom lists, then click OK: And here come our household tasks, sorted by priority.
For long custom lists it may be faster and more convenient to import them from an existing workbook, as explained in this article. When you use custom lists for sorting, you are free to sort by multiple columns and use a different custom list in each case. The process is exactly the same as we have already discussed when.
And finally, we have our weekly household chores sorted with the utmost logic, first by the day of the week, and then by priority:) That's all for today, thank you for reading! See also. I have an Excel spreadsheet I created to keep track of data for my job. The first column of the spreadsheet captures individual files I am working on, and each other column captures some piece of information important to that file. The first row contains the column headers. What I would like to be able to do is to make it so that if one clicks on the column header, it's sorts the column data, while also rearranging the rows themselves so that the data in the column stays in sync with the data contained in the row that it was in. I hope that wasn't too wordy.
Any help would be appreciated! I have an Excel spreadsheet I created to keep track of data for my job. The first column of the spreadsheet captures individual files I am working on, and each other column captures some piece of information important to that file. The first row contains the column headers.
What I would like to be able to do is to make it so that if I sort the 1st column, it sorts the column data, while also rearranging the rows themselves so that the data in the column stays in sync with the data contained in the row that it was in. I hope that wasn't too wordy. Any help would be appreciated! How about sorting at the end of entering a new record?
Meaning, when I enter new information, moving from left to right using the tab key and pressing enter when finished with all the entries in that row. I have a spreadsheet with student information which is organized alphabetically by last name then by first (there are filters). Rather than sort the list again each time I make an edit (like change someone's last name when they get married)or add a new student, I’d like excel to automatically alphabetize the record(s) when I hit enter at the end of the entry. I learned how to do this a LONG time ago when working on my assoc. Degree but can’t remember how and haven’t been able to find exactly this in the help files or online. Hello, I require your help. I have converted PDF to excel having large data.
The problem I m facing is all the data is under one column only. For e.g Ac no 435667865 Client name xyz company Beneficiary: Alfa ltd 1 doc type. English letter 2 doc type. History letter 3 doc type. Geography letter I have reaarange the data in different columns like Ac no, Client name and Ben. How ever I am unable to link the doc types with the Ben name as they get mismatch on large numbers.please help how can I do that do all doc types matches with Beneficiaries.