Excel Can Be 'Rand'om Too
A few weeks ago I showed you a
neat
trick that you can use in Word to quickly fill up a document with sample
text.
Not to be outdone by its sibling, Excel also features a few ways to swiftly fill
up a worksheet with sample data. We use these functions extensively in
order to help test Excel, but people have reported finding them useful from time
to time for their own uses.
The simplest way to fill a cell with a random value is to select the cell and
type:
=rand()
Voila, the cell is populated with a random number between 0 and 1. From
now on, every time Excel recalculates the sheet, a different number between 0
and 1 will appear in this cell.
To fill an entire range of cells at once with random values, start by dragging
out the rectangle of cells you want to fill. Now, type:
=rand()
And then press CTRL+ENTER. Excel fills all of the selected cells with
different random numbers between 0 and 1.
Now, let's get a little fancier. Excel also includes a function
RANDBETWEEN which allows you to specify a lower bound and an upper bound for the
random integer Excel puts in a cell.
Try this: select a range of cells. Then type:
=randbetween(50, 700)
And then press CTRL+ENTER. Excel fills all of the selected cells with
different random numbers between 50 and 700. As with RAND, the numbers
change whenever Excel does recalc (you can force this by pressing F9.)
Note: If you try RANDBETWEEN but get an #NAME error instead of the random
numbers, you don't have the Analysis ToolPak running. Click Tools.Add-Ins
and check the box next to "Analysis ToolPak" to enable it. Then press F2
to cause Excel to regenerate all formulas.
Finally, if you want the random values to stay as they are (and not change every
time Excel does recalc), you need to convert the functions to values. One
easy way to do this:
- Select the range of cells you want to fill up
- Click Edit.Copy to copy the cells to the clipboard
- Click Edit.Paste Special
- Select "Values" in the dialog box, click OK.
Ta-da, the random numbers are now locked in place and won't change anymore.
(By the way, doing this same operation in Excel 12 takes 3 clicks instead of
6!)
So, as you can see, Excel has 'rand'om functionality of its own which you can
use to quickly fill up a spreadsheet with random numbers.
Warning: Not recommended for SEC disclosures or during financial audits.
