Jaa

Excel Can Be 'Rand'om Too

  • Artikkeli

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.

Comments

  • Anonymous
    October 14, 2005
    The best bit in this post is your little revelation that Paste Special is only 3 clicks in Office 12.

    That's great news!
  • Anonymous
    October 14, 2005
    Guessing from Excel screenshots I've seen, you click the Copy button on the clipboard ribbon, then on the arrow under the huge paste button to finally click an soon-to-be-listed option called Paste Values. Am I right?
  • Anonymous
    October 14, 2005
    On the 3 clicks: Copy, Paste (which is a split button), Values.

    Mario wins the prize.
  • Anonymous
    October 14, 2005
    (By the way, doing this same operation in Excel 12 takes 3 clicks instead of 6!)

    No, it's two clicks on Excel 9-10-11. One on copy (or Ctrl+C), and one click on the custom Paste Special--Values button on my custom toolbar. I also have Paste Special--Formulas, Paste Special--Formats, and Paste Special--Values/Transposed. This is the kind of flexibility we're concerned about retaining as we move forward.

    (Don't get me wrong, I'm a big fan already.)
  • Anonymous
    October 14, 2005
    > Excel fills all of the selected cells with
    > different random numbers between 0 and 1.

    If they're guaranteed different, they ain't random! :-)
  • Anonymous
    October 15, 2005
    In a previous post, you explained how Office 12 strived to avoid hiding commands 'under rocks'. Clicking Control-Enter to enter an array formula is way more obscure than that. It comes out of thin air, with no way to discover (other than reading books). Will array formulae be improved in Excel 12?
  • Anonymous
    October 15, 2005
    It could be 4 clicks -- if you are on a different ribbon than the one that contains the Clipboard chunk, you'd have to click the appopriate tab to get to that.
  • Anonymous
    October 15, 2005
    The clipboard ribbon is probably one of the ever present ones.
  • Anonymous
    October 17, 2005
    Clipboard is a chunk on the Sheet tab of the ribbon. It's ever present, but not always in front (so PatriotB makes a good point).