Generate Sample Data In Excel

Let’s understand how we could generate some sample data to carry out some further analysis in excel.

We have certain fixed values which we want to fill in the column randomly.

Below is the data for credit card details and we want to fill the columns Gender, Region and City with the values highlighted in yellow.

We want to fill each columns from the values defined for that column in yellow.

Step 1

We will take the use of CHOOSE and RANDBETWEEN function in excel to generate the sample text.

Enter the formula =CHOOSE(RANDBETWEEN(1,2),$J$2,$J$3) in Gender column i.e.B2

RANDBETWEEN generate a random number between 1 and 2 which is used by the CHOOSE function to select the values from J2 or J3 i.e. CHOOSE selects the value from the list based on the position of the number in the specified list in the formula, if 1 is generated then J2 is selected (Male) and if 2 is generated J3 is selected (Female).

Enter the formula =CHOOSE(RANDBETWEEN(1,4),$K$2,$K$3,$K$4,$K$5) in Gender column i.e.C2

RANDBETWEEN generate a random number between 1 and 4 which is used by the CHOOSE function to select the values from K2, K3,K4 or K5 i.e. CHOOSE selects the value from the list based on the position of the number in the specified list in the formula, if 1 is generated then K2 is selected (East) and if 2 is generated K3 is selected (West) and so on.

Enter the formula =CHOOSE(RANDBETWEEN(1,4),$L2,$L$3,$L$4,$L$5) in Gender column i.e.D2

RANDBETWEEN generate a random number between 1 and 4 which is used by the CHOOSE function to select the values from L2, L3,L4 or L5 i.e. CHOOSE selects the value from the list based on the position of the number in the specified list in the formula, if 1 is generated then L2 is selected (City1) and if 2 is generated L3 is selected (City 2) and so on.

Step 2

Now copy the entered formula and paste the formula till where the data is available to fill the entire columns.

Step 3

Now copy the formula and paste them as values as the RANDBETWEEN function will generate a new number each time and your data will change at every event.