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.
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.
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.
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
Once done, you will need to clean up the data a bit. Start by right-clicking in the cell and select “Format Cells”.
Then choose “Currency” from the “Category” menu, then select the second option under the “Negative Numbers” option. Press “Enter” on the keyboard to finish.
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.
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.
Right click and select “Format Cells” from the menu.
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.
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.
Next, we’re going to highlight the entire column, right-click on it, and select the “Set Name” option.
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.
Choose a starting cell and click to highlight it.
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)
Click and drag the “+” sign at the bottom right of the cell to copy the data to additional cells below (or to the side).
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. .