Comments on: Excel SORT function - automatically sort with formula

The Sort functionality has been around for a long time. But with the introduction of dynamic arrays in Excel 365, there appeared an amazingly simple way to sort in Excel with formulas. The beauty of this method is that the results update automatically when the source data changes. Continue reading →

Table of contents

Comments page 2. Total comments: 60

  1. NAME CASTE
    A SC
    B OBC
    C ST
    A ST
    B SC
    D ST
    A OBC
    B ST
    D SC

    how can possible ?

    firstly SC -> name wise
    then ST > name wise
    then OBC -> name wise

  2. This a great article and has been invaluable in helping me today. Thank you!

  3. I was trying to make something else work and I hope you could help me with. I have a list that I want to sort but I do not want the two sets of columns as in your first example (set one A2:B8 and set two D2:E8) . I just would like to have one set of columns where the inputs and outputs show dynamically. i.e., as I enter a row of information that row will move to its corresponding row order.

    Thank you

  4. Hi dear....
    Just wanna say Bravo.....

    Regards

  5. Hello,

    I can see a huge potential in the SORT function and thank you for explaining it so clearly. I was trying to make something else work and I hope you could help me with. I have a list that I want to sort but I do not want the two sets of columns as in your first example (set one A2:B8 and set two D2:E8) . I just would like to have one set of columns where the inputs and outputs show dynamically. i.e., as I enter a row of information that row will move to its corresponding row order.

    Please let me know if it makes sense.

    Thank you,

    Esteban

  6. fromula for automatically sort a data for values only

  7. You should be able to right-click on a column letter and select "sort" among other column options, but the idiots who created this junk had no common sense.

  8. Hi,
    I was getting the same issue as Simon - have since realised I'm using Excel 2016 version. I can't upgrade as I'm using this for work purposes so this is the excel version the business uses. Do you have any suggestions on how to sort data if this function "=SORT" is not available? Frustrating really as this function is exactly what I need! I need data that will auto sort as soon as new data is added. Therefore the Sort button is not useful - and I want to avoid using macros if I can.

  9. I‘m trying to sort a few cells in a row by using

    =sort(C2:F2,1,-1,ture)

    "That function isn't valid" came out after the formula input.

    1. Hi Simon,

      This may happen if you are trying to use the formula in any version other than Excel 365. Please note that all new dynamic array functions including SORT are only available with Microsoft 365 subscriptions.

      Also, please notice that "true" is mistyped in the last argument.

  10. It's great learning new ways to use the dynamic array formulas, thanks for this article.

    I do have a question. Is there a way when using SORT and multiple columns (i.e. feeding an array constant to the sort columns) to have the columns being sorted on?

    Using your example from the multi-level sort section, hypothetically if I had someone enter numbers in cells H1 and I1, could I have those numbers feed into the creation of an array constant to change which columns are used?

    e.g. instead of "=SORT(A2:C13, {1,3}, {1,-1})" something that would work on the lines of "=SORT(A2:C13, {H1,I1}, {1,-1})"
    I've tried ways of making that happen, but none are successful. Do you know if it can be done?

    Hopefully that makes sense?

    1. Hi Jonathan,

      Yes, that makes perfect sense :)

      Assuming the numbers are entered in adjacent cells like in your example, you can supply them as a normal range:

      =SORT(A2:C13, H1:I1, {1,-1})

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)