This is a day to day trouble which is usually faced by all of us in our office. Say A Raw data is extracted for analysis and this data shouldnot be random. How to do it is the big question because if the said data is in small in quantity we can randomize it manually. But what if Data is of 100,000 in numbers. You will look for online tool like Random.org. But what if I say it is not required to go online finding a solution. Also pushing your data to any website may result in leakage of company confidential data. Now without sharing a data with just a trick of excel you can make the data random. And no one can ever question is it really authentic randomization as it is not done via manual intervention. In my example I am going to put in numbers between 1-100,000, and first they are in sequence. And I want to put them all in random order.
I am attaching the Excel work book so you can also work upon it simultaneously for better understanding.
Step 1.
We have the Raw data sequential numbers from 1 to 100,000. Below is the screenshot and attachment for download.How to Randomize Data Download the excel and open it so we can together work on this and understand better how to randomize a sequential data. Before we move ahead to the next step I am taking example of numbers 1 to 100,000 doesnot mean that this could be used only data with numbers. Data you want to randomize could be any text or any character which you can think of. This post would be helpful if you have to deal with sequential names of employees, ascending or descending order of Salary of Workers, or a mere Alphabet A to Z or anything which is in sequential manner and you want to randomize. I choose numbers as it is easy to connect to and better in understanding and work around. Now let’s move to the next step.
Step 2.
Now we put in a random function in C1 cell, put in =rand() and push enter. Drag it to C100000 which is next to last entry in your excel. C Column will be filled with one or the other number.
Step 3.
Now select column B and C and click on Data in Excel, click on sort in the popup window select continue with current selection.
Step 4.
Click on Sort button, Select Column C in Sort by column and press Ok.
Bingo!!!! your whole data is randomized. You must get the random data as one in How to Randomize Data. You can download it for reference and cross check.
I enjoyed a lot writing this post as I enjoy working on excel. For best Excel tips you can refer Chandoo. If you have any other offline means to solve the purpose please suggest me in comment section.
Tele-Columnist!