Archives for October 2008

Generate Random Numbers for Excel Spreadsheets

When I create the examples for this podcast I frequently use the =RANDBETWEEN() function to generate random numbers. Then, I usually copy the formulas and use Paste Special to convert them into values.

Recently, I watched Bill Jelen. Mr. Excel demonstrate an absolutely amazing Excel Trick – use your Right-mouse button to move a selected block of cells over the the right. And then, without releasing the mouse button, move the block of cells back into place. A menu pops up and asks if you want to “Copy here as Values.” Try it! It is amazing! What a time-saver!

Here are the steps to follow in the Excel Video Lesson:

  1. Use the =RANDBETWEEN() Function to generate random numbers in your Excel spreadsheet. The function takes two arguments – a Top Number and a Bottom Number (both of your choosing.)
  2. The RANDBETWEEN Function is part of the Analysis ToolPak Add-IN. Activate it by choosing Tools – Add-Ins- Analysis ToolPak.
  3. Select the block of cells before you write the formula and then use Ctrl+Enter to populate all of the cells in your selected range with the formula.
  4. To convert the formulas to values, use Copy, Paste Special Values.
  5. OR… try this incredible trick (courtesy of Bill Jelen, Mr. Excel: Select the block of cells. With the RIGHT-Mouse Button, move the Border of the cell range over the the right and then back into place. Then, when you release the mouse a menu pops up. Choose, “Copy as Values Here.” It is amazining!

Find the Video Lesson that you want. Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.