HOW TO RANDOMIZE THE DATA IN GOOGLE SHEETS ?
RANDOMIZING can emerge as a requirement in GOOGLE SHEETS if we are going to create a question paper, or any puzzle application or anything where the initial state of the data is not sorted.
As we know sorting is putting up a number of things in a particular fashion as per the selected criteria.
Sorting in GOOGLE SHEETS is one of the very basic and frequently used operation. There can be a requirement to reverse the sorting or change the order of the data.
Randomizing is exactly opposite of Sorting.
RANDOMIZING THE DATA IN GOOGLE SHEETS MEANS TO SCRAMBLE THE DATA AND PUT IT RANDOMLY OR THE WAY WHERE WE DON’T SEE ANY CORRELATION IN THE DATA.
For example, if have some data which is sorted on a specific criteria, we can randomize it to remove that criteria without ruining the integrity of the data. By randomizing we just mean to scramble the pieces of information.
WHAT IS RANDOMIZING THE DATA ?
RANDOMIZING the data is simply scrambling it so that no sorting or fashion can be found in the data.
WHEN DO WE NEED TO RANDOMIZE THE DATA ?
Randomizing the data can be in a need in the following cases such as
- Scrambling the questions for quizzing purpose.
- Any application which needs to randomize the data.
- To create many random copies of the same data . [ Such as different series of questions ] and more…
DIFFERENT WAYS TO RANDOMIZE THE DATA
There can be different ways to randomize the data in google sheets.
We’ll discuss two of them here so that we can get the things going.
The concept is pretty simple.
We need to make the use of RANDOM NUMBERS CREATING FUNCTION in Google Sheets.
If we don’t make use of the functions , we ‘ll need to do it manually which is going to be very taxing if we are going to deal with a data comprising of hundreds of rows.
Before discussing the ways, let us take an example to try our ways on.
We’ll take an example of the sales data of a company for 21 week.
The data is sorted by the week number.
We’ll use the ways to scramble this data randomly.
The data is shown below.
WAY 1: RANDOMIZE THE DATA USING RAND() AND SORT UTILITY
So, after we have seen the data, its time to randomize the data.
Follow the steps to randomize the data
STEP 1: CREATE AN ADDITIONAL COLUMN
Create a new column at the first position or last position as you wish. For our example, we have created a HELPER COLUMN at the last position i.e. in COLUMN H.
STEP 2: ENTER THE RANDOM NUMBERS
Use the RAND() FUNCTIONS at the first cell of the column i.e. H4 in the helper column.
It’ll generate a random number between 0 and 1 in decimals. [ We don’t need to bother about the decimals as we’ll get rid of the helper column. ]
Drag down the RAND FUNCTION through the column till the last row to fill the column with random values.
After our helper column is ready SELECT THE HELPER COLUMN, PRESS CTRL+C, RIGHT CLICK AND CHOOSE PASTE SPECIAL > VALUES ONLY.
We did this to freeze the random values otherwise whenever there is any event on the sheet, every time rand function will generate a new set.
We can keep this functionality if we need intentionally i.e. to randomize our data with every event.
STEP 3: SORT THE DATA
Go to DATA MENU > SORT RANGE.
SORT RANGE dialog box will open.
Select SORT BY as HELPER COLUMN i.e. COLUMN H [ The column containing the random numbers].
Select the option either A to Z or Z to A. There is no difference as we just need to randomize.
The data will be sorted as per the helper column which has random numbers .
Our data will be scrambled or randomized.
The output is shown below.
We can remove the helper column if don’t need further.
WAY 2 : RANDOMIZE THE DATA USING RANDARRAY AND SORT FUNCTIONS
We already learnt one way to randomize the data .
Let us learn one more way to randomize the data using the Randarray function and SORT function.
FOLLOW THE STEPS TO RANDOMIZE THE DATA
STEP 1: CREATE A HELPER COLUMN
We need a helper column in this way too.
Create a helper column at first position or last just for our help to randomize the data.
A sample is shown below in the picture.
STEP 2 : CREATE RANDOM NUMBERS IN THE HELPER COLUMN
We’ll use the RANDARRAY FUNCTION to create the random numbers this time.
Enter the RANDARRAY FUNCTION as = RANDARRAY(NUMBER OF ROWS, 1 ).
It’ll create a complete list of random numbers directly and no need to drag the function as this is an array formula.
After we enter the formula, the complete column will be filled with random numbers.
We have 21 rows in our data so we used the function to generate 21 number of random numbers.
STEP 3 : CREATE NEW TABLE WITH RANDOMIZED DATA
Copy the headers at some other place where we want to create the new randomized data.
The headers are shown below in the picture. [ The headers should be copied including the header column.
We are ready to use our last function for the random output.
In the first cell under the WEEK NUMBER use the function as =SORT( COMPLETE DATA , COLUMN FOR SORTING, ASCENDING OR DESCENDING ).
We used the function as =sort(D37:H57,5,TRUE)
After entering the formula, press enter.
The output will be as shown below.
The data has been scrambled.
The helper column can be deleted if you don’t need it further.
If you want the data to be stable, select the random column, Copy it by control+C, Right Click and choose PASTE SPECIAL.
Choose PASTE VALUES ONLY.
If you don’t do this, the random values will change with every event.