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.