If you’re working with a new template, adding numbers will get you an idea of how the template will look when it’s in use. Similarly, you will definitely need numbers to test a newly created formula.
However, manually entering numbers into your grid can be quite bothersome. To automate the process, Excel has two library functions to help you generate random numbers. You can choose between the RAND() and RANDBETWEEN() functions depending on if you wish to generate a random integer value or, a number between a specific range.
Use RAND() Function
The RAND function returns a random integer number between 0 and 1. The function does not require you to enter any arguments. Simply select the cell you wish to add your random number then enter =RAND().
To generate more numbers, simply drag the fill handle from the bottom-right corner of the cell with your formula. Drop the handle where you wish to extend the formula till. If the value changes, it’s because you’ve set your calculation settings to Automatic.
Excel refreshes its formulas every time a new formula is entered. As RAND() is a formula as well, the formula is refreshed, triggering Excel to generate a new value each time.
Use RANDBETWEEN() Function
If you want to generate a random number from a specific range, the RANDBETWEEN() function is perfect for you. Unlike RAND(), you will have to enter two arguments for the RANDBETWEEN() function. Once you open the parenthesis, enter your lower value then enter your upper limit. This function will also only return whole numbers, not integers.
For example, if we’re looking to generate a random number between 50 and 100, our formula will look something like this:
=RANDBETWEEN(50,100)
Similar to the RAND() function, if you use Flash Fill to fill the adjacent cells, the values might refresh so you need to consider that if you’re using the value to test a formula.
Change Generated Values
If you’re not satisfied with the generated values, you can refresh your Excel workbook to get a new number. However, for this to work, you need to make sure your calculations are set to Automatic. Simply, from the Formula tab, navigate to Calculation options > Automatic.
After you’ve set the calculation options to Automatic, on your keyboard, use the F9 key to refresh your worksheet. You can refresh your tab until you get the number you desire.
Prevent Excel From Changing Random Values
The volatile nature of these formulas can be a problem if you’re using these values to test a formula. To lock the data, you will have to copy your range and use the Paste Value option from the Paste Options. What this will do is paste only the value and not the formula, keeping your data constant.
Select your data range and copy it (Ctrl + C). Right-click on the same location and hit the V key on your keyboard. This will paste only the value and not the formula from your copied data.