قالب وردپرس درنا توس
Home / Tips and Tricks / Create random (fake) data sets in Microsoft Excel

Create random (fake) data sets in Microsoft Excel



The Microsoft Excel logo on a gray background

Creating random data to fill an Excel workbook is as easy as adding a few unknown formulas. These formulas are helpful in sharpening your Microsoft Excel skills because they give you fake data to practice with before risking mistakes with the real thing.

Use the formula bar

To start, we enter one of the few formulas in the formula bar. This is the window below the ribbon, found here.

Excel formula bar

From there, it’s all about adding the data you want and then cleaning it up a bit.

Add random numbers

To randomly add an integer, we use the function “RANDBETWEEN”. Here we can specify a string of random digits, in this case a number from one to 1,000, and then copy it to each cell in the column below.

Click to select the first cell you want to add your random number to.

Copy the following formula and paste it into the Excel formula bar. You can change the number in the brackets to suit your needs. This formula picks a random number between one and 1,000.

=RANDBETWEEN(1,1000)

Press “Enter” on the keyboard or click the “Green” arrow to apply the formula.

Excel random number formula

Hover over the cell in the lower right corner until the “+” icon appears. Click and drag it to the last cell in the column where you want to apply the formula.

Excel copy data plus

You can use the same formula for monetary values ​​with a simple adjustment. By default, RANDBETWEEN only returns whole numbers, but we can change that by using a slightly modified formula. Just change the dates in brackets to suit your needs. In this case, we will pick a random number between $ 1 and $ 1,000.

=RANDBETWEEN(1,1000)/100

Excel random number formula

Once done, you will need to clean up the data a bit. Start by right-clicking in the cell and selecting ‘Format Cells’.

Excel format cells

Then choose “Currency” under the “Category” menu and then select the second option under the “Negative Numbers” option. Press “Enter” on the keyboard to finish.

Excel format currency

Add dates

Excel’s built-in calendar treats each date as a number, with the number 1 being January 1, 1900. Finding the number for the date you want isn’t that easy, but we’ve got you covered.

Select your starting cell and copy and paste the following formula into Excel’s formula bar. You can adjust everything in brackets to suit your needs. Our sample is set to pick any date in 2020.

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

Press “Enter” on the keyboard or click the “Green” arrow to the left of the formula bar to apply the formula.

Excel date formula

You will notice that this doesn’t look like a date just yet. Which is good. As in the previous section, we will click the “+” sign in the lower right corner of the cell and drag it as far as necessary to add additional randomized data.

When you are done, highlight all the data in the column.

Excel highlight column

Right-click and select “Format Cells” from the menu.

Excel format cells

From here, choose the “Date” option, then choose the format you prefer from the available list. Press “OK” when you’re done (or “Enter” on the keyboard). Now all your random numbers should look like dates.

Excel data format

Add item data

Randomized data in Excel is not limited to numbers or dates only. With the “VLOOKUP” function we can create a list of products, name them and then extract it to create a random list in another column.

To start, we need to make a list of random things. In this example, we’re adding pets from an imaginary pet store, starting at cell B2 and ending at B11. You must number each product in the first column, starting at A2 and ending at A11, coinciding with the product on the right. Hamsters, for example, have the product number 10. The titles in cells A1 and B1 are not necessary, but the product numbers and names underneath are.

Excel columns and rows

Then we highlight the whole column, right click on it and select the option “Define name”.

Excel defines name

Under ‘Enter a name for the date range’ we add a name and then click the ‘OK’ button. We have now made our list to retrieve random data.

Excel defines name

Choose a starting cell and click to highlight it.

Excel highlight cell

Copy and paste the formula into the Formula Bar and then press “Enter” on the keyboard or click the “Green” arrow to apply it. You can change the values ​​(1.10) and name (“products”) to suit your needs:

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

Excel formula

Click and drag the “+” sign at the bottom right of the cell to copy the data to additional cells below (or to the side).

Excel copy data


Whether you’re learning pivot tables, experimenting with formatting, or learning how to create a diagram for your next presentation, this dummy data could turn out to be just what you need to get the job done.




Source link