How to sort in Excel by row, column names and in custom order

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:

Sort buttons on the Home tab in Excel 2010Sort buttons on the Data tab in Excel 2010

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...

  1. Click the Sort button on the Data tab or Custom Sort on the Home tab to open the Sort dialog.
  2. Then click the Add Level button as many times as many columns you want to use for sorting:

    Click the Add Level button to add more columns to sort by

  3. 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:

    Select the columns by which you want to sort your data from the Sort by dropdown list.

  4. 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.

    Data sorted by 3 columns

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:

The data that we need to sort by column names

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.

  1. 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:

    Select the range of data you want to sort

  2. 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.

    Make sure the "My data has headers" checkbox is checked and click the Options button

  3. In the opening Sort Options dialog under Orientation, choose Sort left to right, and click OK.

    Choose Sort left to right, and click OK

  4. 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.

    Select a row you want to sort by

    The result of your sorting should look similar to this:

    Data sorted by column names

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:

The data sorted by a row

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:

Build-in Excel custom lists

Say, we have a list of weekly household chores and we want to sort them by due day or priority.

The list of household chores to be sorted by a custom list

  1. You start with selecting the data you want to sort and then opening the Sort dialog exactly like we did when sorting by multiple columns or by column names (Data tab > Sort button).
  2. 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:

    Select the column you want to sort by and choose Custom List under Order

  3. 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.

    Select the custom list by which you want to sort your data

    That's it! Now we have our household tasks sorted by the day of the week:

    The household tasks sorted by the day of the week

    Note. 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:

    Click the Reapply button to re-sort modified data using the same criteria

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:

The newly created custom list is added to the existing custom lists

And here come our household tasks, sorted by priority:

The household tasks sorted by priority

Tip. For long custom lists it may be faster and more convenient to import them from an existing workbook, as explained in this article Creating a custom list from an existing worksheet.

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 sorting by several columns.

Choose 2 different custom lists by which you want to sort your data

And finally, we have our weekly household chores sorted with the utmost logic, first by the day of the week, and then by priority :)

The household sorted by 2 custom lists - days of the week and priority

That's all for today, thank you for reading!

See also

23 Responses to "How to sort in Excel by row, column names and in custom order"

  1. Robb says:

    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!

    • Donna says:

      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!

    • jen says:

      hi, good day! i'm having trouble in sorting my employee masterlist. i'd like to sort it from a to z, the trouble is there were columns with employee's information. how can i sort it together with the employee's information/details. thank you.

  2. santosh kumar says:

    Thank you soo much for great help brother,, can i have your mail ID please,,

  3. Bruce says:

    where is the answer to this question i have the same issue

  4. Ellen says:

    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.

  5. Yunus says:

    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.

  6. Kate says:

    This is the problem I am having : a 3 column list of product dimensions that I need sorted from largest to smallest by data per row. Column A is Length, Column B is Width, and Column C is Height. The length of the product must be the largest measurement, width next, height smallest. I cannot seem to find a way to sort this on a multiple row scale.

  7. Ahmed says:

    Hello,
    i would to create custom list consist of more than 40 item , while the list entry accommodate only for 29 item. i'm using Excel 2010.

  8. Raju says:

    How to arrange excel data EBCDIC Order (-,/,=,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,0,1,2,3,4,5,6,7,8,9)

    Ex: Item Number from EBCDIC:
    ----------------------------
    AAA
    A-A
    A-9
    123
    123-
    123A

  9. Rajan Kumar says:

    How to Put formula in excel to display date wise all data Like this by searching name?

    Searching: Rajan

    Sr. Date Product Qty Amt
    1 1-4-16 ABC 1 24
    2 5-4-16 RNR 2 35
    3 6-4-16 RNR 1 20

    Total: below

  10. niraj thorat says:

    how can i identify below data of column title wise in excel 2010

  11. Sharon Wagner says:

    I have created a list four columns wide. Just so the fit on one page. I want to alphabetically sort them a-z. how do I do that?

  12. Ed Maga says:

    I have a column of data using 12 rows. I want to create a formula that will grab the value from each row and populate the next column in the first row across so that I have 12 columns of data to represent the 12 rows of data I have

  13. nadege says:

    how to sort in excel by alphabetical order and the first column contains names and the second contain many values which marshes with the names so that it cant mixes values

  14. Sabah says:

    Hello
    I have this problem in excel, I want to separate several information that put in same column to be in another column> the below is illustration to what I have and what I need:

    HLRSN 1
    IMSI 418707435563810
    MDN 9647435563810
    A_KEY 76300596352E22D1
    MEID A1000044DD9A42
    K4SN 3
    SSDSTATUS SsdSsdUpdateEnd

    HLRSN 1
    IMSI 418707435705904
    MDN 9647435705904
    A_KEY 00D401B274A00A8A
    ESNUIMID 7C4C170F
    K4SN NONE
    SSDSTATUS SsdDefineSubscriber

    the above information are put in two columns , so I want to separate each 7 rows in different column so the result should be like this

    HLRSN 1 HLRSN 1
    IMSI 4.18707E+14 IMSI 4.18707E+14
    MDN 9.64744E+12 MDN 9.64744E+12
    A_KEY 00D401B274A00A8A A_KEY 76300596352E22D1
    ESNUIMID 7C4C170F MEID A1000044DD9A42
    K4SN NONE K4SN 3
    SSDSTATUS SsdDefineSubscriber SSDSTATUS SsdSsdUpdateEnd

    with thanks

  15. Sivakumar says:

    Hi,

    How to sort out more than one column from smallest to largest number at the same time

  16. Mari Margiotta says:

    I wanted to sort a name column by last name but the name column is already filled in with first and last name. Is there any way to make that distinction? Please help! :)

    Thank you

  17. Paul says:

    I have data in Column A & B like below sample 1. And I want to make a list of all payment date under specific ID like sample 2. Pls advice how can I do it.

    Sample: 1
    ID PAYMENT DT
    100 01.12.18
    200 01.12.18
    300 02.12.18
    100 02.12.18
    200 03.12.18
    300 05.12.18

    Required shorting using formula
    ID Payment Dt
    100 01.12.18 02.12.18
    200 01.12.18 03.12.18
    300 02.12.18 05.12.18

  18. Evelyn says:

    I have a list of over 200 paint colors. The colors are in columns A,B,C, & D. I would like to sort all (not each) of these columns in alphabetical order with A1 being Apple Red & D40 being Zinc. So basically, the columns are "wrapped". Can I sort these four columns?

  19. yadav bhandari says:

    hello,
    I have a problem, how to sort the data being zumbled ordered in the column of a sheet 1, in order to which is in specific order in the the column of sheet 2.

  20. yadav bhandari says:

    I have a problem, how to sort data in the column of sheet 1 as to the data given in the column of the sheet 2 .

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard