In Today’s #FormulaFriday #Excel tip in my 2019 series let’s look at how to sort your data set in random order. We will do this using the RAND Function. This is a really handy tip if you have a lot of records and you want to randomly take a few to analyze for quality control. For example, I have about 1000 records in my current data set I want to random sort in Excel. A sample can be seen below.
We can use the RAND Function and an extra column to randomize the data records.
First, we need to enter the RAND function in our empty column to the right of the data set. You can add a column within your data set, but for this method there really is no reason to do so.
The RAND Function.
RAND is a mathematical function which generates random numbers between 0 and 1. There is a companion called RANDBETWEEN if we want to generate random numbers between two amounts. We simply want to use RAND. If you type =RAND, left parenthesis, that’s all you need to do, press enter. It simply enters a number between 0 and 1. A random number. Easy as that.
Just double click the cell or copy it down the column to the end of your data set. So, give the column header a name (in the case I just used Random Sort). All you need to do now is simply click within the data anywhere and on the data tab, AZ or ZA. It makes no difference, we simply click it and the data will be randomly sorted.
You can then delete the extra sorting column if you want to. Easy as that. You can randomly spot check your data for quality control. How do you take random samples in your Excel data set?. Please share in the comments below.
What Next? Want More Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.