Mynda's Excel Dashboard Webinar

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
May 19, 2016
 

Greetings from The Excel Addict
Hi fellow Excel Addict,

After several weeks of not-so-spring-like weather, it looks like we are finally going to be getting some really nice weather for this long weekend...and I'm going to be away :(

I am going to Halifax to run in the 10K race at the Bluenose Marathon and, thankfully, the weather forecast there is looking even better.

In today's 'Excel in Minutes' tutorial, I'm going to show you 'How to Generate Random Numbers and Lists'. Even if you think this isn't something you would ever need to do, please read it anyway and download and follow along with my practice file. Just by playing with this, chances are that you will think of something new that you can use this for.


If you missed my 'Excel in Seconds' newsletter on Tuesday, I explained to you why 
'My &%$#@ Tab Key is Scrolling Me One Screen Horizontally!" and how I fixed itYou can read all about that here.

Wishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)


If you missed my last newsletter, you can click here to view it online.
 

 
TheExcelAddict.com Quote of the Day

"Happiness is not having what you want, but wanting what you have."

-- Hyman Schachtel --


If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

How to Generate Random Numbers and Lists

If you need to generate a list of random numbers, Excel has a couple of functions to help you.

To generate random numbers within a range of numbers, Excel's RANDBETWEEN function makes it easy.

You simply enter the formula
=RANDBETWEEN(low_number, high_number) in your cells using the lowest value and the highest value in your desired range of numbers.

Generate Random Numbers Within A Range in Microsoft Excel 2007 2010 2013 2016 365

There are a couple of things you'll notice about the RANDBETWEEN function:
a) Each time the sheet calculates, the random numbers change
b) The same number may be generated more than once.


FOLLOW ALONG USING MY PRACTICE FILE

How to Prevent the Numbers from Changing

To prevent the numbers from changing each time the worksheet calculates...

Randbetween Convert To Values in Microsoft Excel 2007 2010 2013 2016 3651) Highlight your list and move your mouse to the edge of the selected range until the mouse pointer changes to a 4-headed arrow;

2) Holding the right mouse button, drag the cells a little to the right, BUT DON'T RELEASE THE MOUSE BUTTON. Still holding down the mouse button, drag back to the original location and release the button;

3) Click the 'Copy Here as Values Only' option from the menu that pops up and the formulas will be replaced with fixed values.

Alternatively, you can highlight your list, right click Copy, right-click Paste Values.

Paste As Values in Microsoft Excel 2007 2010 2013 2016 365

Unique Random Numbers

If you require your list to have unique numbers, there are several possible solutions.

Here are a couple that I use because they are easy to remember...

Use RANDBETWEEN function, then Remove Duplicates

1) Select a range of cells down a column large enough to accommodate the number of values you need plus some extra to account for any duplicates that will need to be deleted. For example, we need 10 unique random numbers between 1 and 20, so we will select 14 cells;

2) Type
=RANDBETWEEN(1, 20) but don't press ENTER. Instead, hold down the CTRL key and press ENTER. This will fill the formula down the cells you have selected;

Random Numbers With Duplicates in Microsoft Excel 2007 2010 2013 2016 365

3) Since the RANDBETWEEN function updates every time the worksheet recalculates, you will need to convert these formulas to fixed values. To do this, highlight the cells, right-click Copy, right-click Paste Values (or you can use the right-mouse-button-drag-copy-as-values trick explained above);

Paste As Values in Microsoft Excel 2007 2010 2013 2016 365
4) With the cells still highlighted, from the Data tab click Remove Duplicates and click OK. You will be left with a list of unique numbers;

Remove Duplicate Random Numbers in Microsoft Excel 2007 2010 2013 2016 365
5) Finally, delete any extra numbers you don't need.

Remaining Unique Random Numbers in Microsoft Excel 2007 2010 2013 2016 365

Sort Unique Numbers using the RAND function


Here is another easy method for generating unique random numbers.

1) Enter the entire range of numbers from which you want to choose the random numbers in one column (e.g. 1 to 20);

2) In the next column, enter =RAND() and copy it down. The RAND function generates a value between 0 and less than 1. Note that the RAND function generates a new number every time data changes anywhere in the worksheet or if you press F9 to recalculate;

3) Next, sort the RAND column A to Z (Data , Sort, Ascending) and the order of your original list of numbers will be randomized. You will notice that after the sort operation, the RAND functions recalculated, however 
the data was sorted based on the original RAND values;

4) Your original numbers have now been sorted randomly.

Random Sort Numbers Using Rand Function in Microsoft Excel 2007 2010 2013 2016 365

Randomly Sort a List of Names

To randomly sort a list of names, or any other list for that matter, you can use the following simple method.

1) Select a range of cells in a column adjacent to your list;

2) Type =RAND() but don't press ENTER yet;

3) Instead, hold down the CTRL key then press ENTER. This will enter the RAND function into all of the cells you have selected;

4) Next, select a single cell in the RAND column;

5) From the Data tab click the A/Z sort command. Excel will sort your entire data table based on the values in the RAND column
You will notice that after the sort operation, the RAND functions recalculated however the data was sorted based on the original RAND values;

6) Now you can delete the RAND values.

Randomize List Of Names in Microsoft Excel 2007 2010 2013 2016 365

FOLLOW ALONG USING MY PRACTICE FILE

What ways do you use random numbers and lists in Excel? Let me know.


Mynda's Excel Dashboard Webinar
Thanks for supporting this newsletter and website

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630