How to Create Random (Fake) Datasets in Microsoft Excel

Microsoft Excel logo on a gray background

Creating random data to fill an Excel workbook is as easy as adding a few little-known formulas. These formulas are useful for honing your Microsoft Excel skills as they give you fake data to practice before you risk mistakes with reality.

Use the formula bar

To get started, we’re going to enter one of the few formulas into the formula bar. This is the window under the ribbon, found here.

Excel Formula Bar

From there it’s a case of adding the data you want and then doing a little cleanup.

Adding random numbers

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

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

Copy the following formula and paste it into the Excel formula bar. You can change the number in parentheses according to your needs. This formula selects a random number between one and 1000.

= RANDBETWEEN (1.1000)

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

Excel Random Number Formula

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

Excel copies data plus

You can use the same formula for currency values ​​with a simple adjustment. By default, RANDBETWEEN only returns whole numbers, but we can change that by using a slightly modified formula. Simply change the dates in parentheses to suit your needs. In this case, we choose 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 select “Format Cells”.

Cells in Excel format

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

Currency in Excel format

Adding dates

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

Select your starting cell, then copy and paste the following formula into Excel’s formula bar. You can change anything in parentheses to meet your needs. Our sample is ready to choose a random 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’ll notice that it doesn’t look like a date yet. Its good. Just like in the previous section, we’ll click on the “+” sign at the bottom right of the cell and drag it as far as needed to add additional random data.

When done, highlight all the data in the column.

Excel Highlight Column

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

Cells in Excel format

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

Excel data format

Adding Item Data

Random data in Excel isn’t just about numbers or dates. Using the “VLOOKUP” function, we can create a list of products, name it, and then extract from it to create a random list in another column.

To get started, we’ll need to create a list of random items. In this example, we will add animals from an imaginary pet store starting at cell B2 and ending at B11. You will need to number each product in the first column, starting with A2 and ending with A11, coinciding with the product on the right. Hamsters, for example, have the product number 10. The A1 and B1 cell headers are not needed, although the product numbers and names below are.

Excel columns and rows

Next, we’re going to highlight the entire column, right-click on it, and select the “Set Name” option.

Excel set name

Under “Enter a name for the date range” we will add a name and then click on the “OK” button. We have now created our list from which to extract random data.

Excel set name

Choose a starting cell and click to highlight it.

Excel highlight cell

Copy and paste the formula into the formula bar, then press “Enter” on the keyboard or click the “green” arrow to apply it. You can modify the values ​​(1,10) and the name (“products”) according to 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).

Copy Excel data

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.