How To Generate Random Numbers In Excel: RAND and RANDBETWEEN Explained

Random numbers can transform your spreadsheets from static to dynamic. I’ve used these functions in everything from testing models to running classroom simulations in Five Points.

The magic happens with just two functions: one gives you decimals between 0 and 1, while the other lets you pick any range you need. No add-ins, no complex setup—just pure Excel doing what it does best.

You’ll generate test data, create simulations, or add variability to your models in seconds. The best part? These numbers recalculate with every change, keeping your data fresh.

Works in every modern version, from Excel 2010 to Microsoft 365. Let’s make your sheets smarter.

What Youll Need

Instructions

Here is the method I use to generate random numbers efficiently in spreadsheets.

Launch Excel and create a new worksheet. Select the cell where you want the first random number to appear. I always start in cell A1 for simplicity, but any cell works as long as you’re consistent with your range references.

Clear any existing data in the cells you plan to use. Having a clean workspace ensures there are no conflicts with formulas or formatting. Verify the cell is empty before proceeding to the next step.

In the selected cell, type =RAND() and press Enter. This function generates a random decimal number between 0 and 1, recalculating every time the worksheet changes. Watch the cell update automatically as you edit other parts of the sheet.

To generate multiple random decimals, drag the fill handle (small square at the bottom-right corner of the cell) across the range you want to populate. Excel will copy the formula and generate a new random number in each cell. Confirm each cell shows a different value between 0 and 1.

For whole numbers within a specific range, use =RANDBETWEEN(bottom, top). For example, to generate a random integer between 1 and 100, enter =RANDBETWEEN(1,100) in a cell. The result will be an integer that updates with each change to the worksheet.

Drag the fill handle to populate additional cells. Each cell will display a different random integer within your specified range. Verify the numbers fall within 1 and 100 inclusive, with no decimals or errors.

Random numbers generated by RAND and RANDBETWEEN recalculate with every change. To make them static, select the cells containing your random numbers, right-click, and choose Copy. Then, right-click the same range and select Paste Special, followed by Values.

This replaces the formulas with their current results, freezing the numbers in place. Confirm the cells no longer change when you edit other parts of the worksheet. The values are now permanent.

Check that all generated numbers meet your requirements. For RAND, ensure values are between 0 and 1. For RANDBETWEEN, confirm they fall within your specified integer range. Sort the data or use conditional formatting to visually verify distribution.

If you need a specific number of unique random values, combine these functions with UNIQUE or SORT to filter and organize the results. This step ensures your data is both random and usable for your specific needs.

Tips and Common Mistakes

Small details make the difference between random numbers that keep changing and ones that stay put.

Worksheet Preparation: In Step 1, always clear the entire range you plan to use, not just individual cells. I learned this after hidden formatting caused my formulas to behave unpredictably. A completely clean slate prevents conflicts with existing data or conditional formatting rules.

Formula Behavior: Remember that both RAND and RANDBETWEEN recalculate with every change to the worksheet, as mentioned in Steps 2 and 3. If you need to preserve a snapshot of values, Step 4’s copy-paste values method is essential. Without this, your numbers will shift every time you edit any cell in the workbook.

Range Verification: When using RANDBETWEEN in Step 3, double-check that your bottom value is actually lower than your top value. I once accidentally entered =RANDBETWEEN(100,1) and wondered why I kept getting errors. The function requires the first argument to be the lower bound.

Distribution Check: In Step 5, after generating your numbers, use Excel’s MIN and MAX functions to verify your range. For RAND, you should see values approaching but never quite reaching 0 and 1. For RANDBETWEEN, confirm your actual minimum and maximum match your intended range.

Frequently Asked Questions

Generating random numbers in Excel is simple with RAND and RANDBETWEEN functions. 🎯 Use RAND for decimals between 0 and 1, or RANDBETWEEN to define your own range. Now, try applying these to your next project and explore more Excel functions to boost efficiency!