fbpx
Search
Close this search box.

Sorting a list of items in random order in excel – using formulas

Share

Facebook
Twitter
LinkedIn

In shuffling a list of items in excel I have described the technique of using random numbers generated by RAND() to sort a list of items. The technique had one disadvantage though, every time you need to reshuffle the list you have to press F9 to recalculate the rand() and then go to menu > data > sort and sort the data again based on the new random numbers.

shuffling-list-of-items-excel-formulasHere is a better technique that needs one key stroke to reshuffle the list of items (sorting the list in random order every time you press the key F9):

  • Insert 2 columns to the left of the list of items you want to shuffle
  • In the first column fill a series of numbers starting with 1
  • In the next column fill RAND() formula
  • Now, next to the list of items you want to sort in random order, we will use both VLOOKUP() and SMALL() excel formulas to fetch items in random order. See the formula below:

    sort a list of items in excel in random order using formulas

    The SMALL() excel spreadsheet formula is used to sort a list of numbers and fetch nth smallest number in a given list.

  • When you want to reshuffle the order, just hit F9

More sorting: Sort text / tables from left to right along columns

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

12 Responses to “Sorting a list of items in random order in excel – using formulas”

  1. JR says:

    Have you tried the =Sort() function in Google spreadsheets?
    I know, believe me, how much fun it is to use all sorts of cool combinations of functions to achieve these little tasks - but Sort() doesn't take away that fun - it just adds another tool to the box ... and then check out Unique() and Filter().... Can't wait to see what you do with those 😉

  2. Chandoo says:

    @JR ... Yeah, the SORT(), UNIQUE() and FILTER() are pretty powerful functions and I use them to speed up work whenever I can. I wish MS would add these as basic functions in the spreadsheet and use continue() mechanism to handle arrays instead of those complex array formulas.

  3. shilpa says:

    I Have the list of codes which are of 9 digits each and values of that codes, now I wish to make the sum of all those values against codes having "1" or "2" as its 3rd digit out of nine. please help me in this regard. will using if formula help?

  4. Chandoo says:

    @Shilpa... you can use sumif() formula to do this.

    First next to the codes insert a blank column and fill it with the formula to fetch the 3rd digit of the code. you can use mid(code,3,1) to do this.

    Then you can use sumif() on this column and values column to conditionally sum values when the code is 1 and 2.

    Read more on sumif() here: http://chandoo.org/wp/2008/06/09/what-the-if-learn-6-cool-things-you-can-do-with-excel-if-functions/

  5. [...] If you would like to randomize the suitcase - value assignments, just set “Play game?” to “No” and excel shuffles the values for you. (How to shuffle a list of values in excel using formulas?) [...]

  6. CharlesA says:

    Thanks! This is exactly what I needed. Love this site.

  7. Kuldeep says:

    Awesome..

  8. Peter says:

    This is perfect, in conjunction with the Solver add-in for generating the shortest path (points), for generating random to/from tickets in the game “Ticket to Ride.”

  9. Sreedhar says:

    Dear Chandoo garu,

    i want to make
    1.an S-Curve in excel
    2.Distributions-Linear,Frontloaded,Backloaded,Trapezoidal,Triangular,...

    all these comes for Project management areas,kindly help how to automate in excel.

  10. suman gupta says:

    respected sir, i am facing difficulty in sorting my data as per my need
    please guide me,

    DATA REQUIRED SORTING
    ABC SYP 100ML ABC SYP 60ML
    ABC SYP 150ML ABC SYP 100ML
    ABC SYP 60ML ABC SYP 150ML
    BAC SYP 60ML BAC SYP 60ML
    BAC SYP 100ML BAC SYP 100ML
    BAC SYP 150ML BAC SYP 150ML
    CAB SYP 500ML CAB SYP 100ML
    CAB SYP 200ML CAB SYP 200ML
    CAB SYP 100ML CAB SYP 500ML
    Z TAB 100MG Z TAB 50MG
    Z TAB 50MG Z TAB 75MG
    Z TAB 75MG Z TAB 100MG

Leave a Reply