How to generate random data in Excel?

Generating random data in Excel to populate a workbook is as simple as adding a few little-known formulas to it. Prepare yourself for real projects by generating random data in Excel that can be dates, numbers, or more.

These formulas, which are almost unknown, will strengthen your Excel skills. You can practice with this random or fake data before making a mistake in the real world. Stay with Digiato to learn how to generate random data in Excel.

Random data generation in Excel with formula bar

To start one of these several formulas, we enter them in the formula bar. This section is located under the ribbon of this software. You can see its location in the image below:

From here on, we teach how to add data and also make changes to it.

Generating random numbers in Excel

To generate random numbers in Excel, we use the “RANDBETWEEN” function. Here we can determine a range of random numbers, which in this article is between 1 and 1000. After this, we copy these numbers in each cell in the column below.

Click on the first cell you want to add a random number. Copy the following formula and paste it in the formula bar. You can change the numbers in the parentheses to suit your needs. The following formula selects numbers from 1 to 1000:

(RANDBETWEEN(1,1000=

Press the Enter key on the keyboard and then click on the green arrow next to the formula bar to apply this formula.

Hold the mouse cursor in the lower right corner of the cell until the “+” icon appears. Click on this icon and drag it to the last cell you want in the column.

With a simple change, you can use the same formula for financial data. The RANDBETWEEN function returns only whole numbers by default, but we can change this with a modified formula. To do this, just change the data inside the parentheses, for example, for financial data, choose random numbers between 1 and 1000 dollars.

RANDBETWEEN(1,1000)/100=

After you’re done, you’ll need to make a few changes to the data. Start this task by right-clicking inside the cell and then select the “Format Cells” option.

In the window that opens, look for the “Currency” option in the categories. In this section, under the “Negative Numbers” section, select the second option and press the Enter key on the keyboard to finish the job.

Random date generation in Excel

For the next part of generating random data in Excel, we go to history. Excel’s internal calendar treats each date as a number, the first of which is January 1, 1900. Considering this, it is not so easy to find the date you need, but we will teach you how to do it.

Select the first cell you want and copy and paste the following formula into the formula bar. You can change all the information in the parentheses according to your needs. In this article we are looking for a random date in 2020.

((RANDBETWEEN(DATE(2020,1,1),DATE(2020,12,31=

Press Enter and then click the green arrow next to the formula bar to apply it.

The numbers that are placed inside the cell are still not similar to a date. Don’t worry because we will fix it later. Now, as in the previous section, click on the + icon on the right side of the lower corner of the cell and drag it as far as you want in a column.

After that, select all the data in this column.

Right click and select Format Cells in the menu.

In the new window, select the date category and then click on the desired format in the list opposite it. Finally, click on the OK option or press the Enter key on the keyboard. After this, all the random numbers in the cells will be similar to the date.

Generating diverse random data

Generating random data in Excel is not only limited to numbers and dates. Using the “VLOOKUP” feature, we can generate a list of products, name it, and then use it to generate a random list in another column.

To start we need to create a list of random data. In this article, we will generate a list of animals from a fictional shop in the second column from cells B2 to B11. For each of these animals, we must consider a number, which we do in the first column from cells A2 to A11. These are logic numbers with animals in their right column. For example, the hamster product number is 10. The names of cells A1 and B1 are not necessary.

Next, select the entire column, right-click on it, and then select the “Define Name” option.

Under the “Enter a name for the data range” section, add a name and then click OK. Now we have created a list that we can use to generate random data in Excel.

Select a cell and click on it to start working.

Enter the following formula in the formula bar and click on the green arrow or press the Enter button on the keyboard. You can change the numbers in parentheses or the name of “Products” according to your needs.

(VLOOKUP(RANDBETWEEN(1,10),products,2=

Hover over the + icon in the bottom right of a cell and drag it to copy the data to the bottom cells.

No matter what you use this random or fake data for, you can practice with it for your real work so you’re always ready.