A quick little project.
I just happened to visit a Web site that used another Web site to generate random numbers for contest giveaways. To me, an Excel user, that seemed like a silly place to go to get a random number when you could easily generate one on the fly within Excel.
So I whipped up a tiny Excel spreadsheet to do the job. You just put the minimum value in one box and the maximum value in the other and Excel generates a random number between the two for you. To generate additional random numbers in the range, just press Command-= (on a Mac) or Control-= in Windows. The worksheet is protected so you can’t accidentally delete the formula that does the magic.
Although I created the worksheet in Excel 2004 for Mac OS, it can be opened in Excel 2003 and Excel 2007 for Windows, too.
Want a copy? Download it here.
On July 8, 2008 at 1:55 am, 
On July 6, 2008 at 8:18 am, 
On July 5, 2008 at 4:14 pm, 
On July 1, 2008 at 5:05 pm, 
On June 24, 2008 at 6:34 am, 
On June 21, 2008 at 9:44 am, 
On June 12, 2008 at 12:19 am, 
Loading ...
21 responses so far ↓
1 Peggy // Jun 28, 2007 at 10:50 am
Hi,
Is it me, or is the link for the xls file not working?
Thx
2 Maria Langer // Jun 28, 2007 at 10:58 am
Nope, it’s not you. It’s me! I put in the wrong domain name. It’s fixed now — I just tested it.
Sorry about the problem, but THANKS VERY MUCH for letting me know!
3 Peggy // Jun 28, 2007 at 12:05 pm
Maria,
You are welcome, got the file.
FYI:
A friend wanted me to test it on Win98/Excel 2000. Works fine there too!
Thanks
4 jamie // Jul 11, 2007 at 9:55 am
maria dont spose u know how to split a four digit number like 2365 into 4 different numbers 2 3 6 5 in all different cells in excel without having to do it manually just wandering please tell me if you know a way thanks
5 Maria Langer // Jul 11, 2007 at 10:21 am
Good question, Jamie. It is possible. I wrote it up for you here: http://www.marialanger.com/2007/07/11/excel-splitting-digits-example/
6 David French // Aug 30, 2007 at 11:05 pm
Neat and smart!
For Windows users just press F9 to recalculate/generate new number
7 belladonna // Sep 13, 2007 at 12:28 pm
I need to generate a random sample from a list of 1500 numbers. I knew exactly how to do that in my OLD excel, but I’m still figuring out Office 07. Where is the data analyzer tool kit?
8 Anne // Oct 14, 2007 at 10:50 pm
Is it possible to generate either sequential numbers or random numbers in a cell within excel each time the document is opened?
9 Maria // Oct 15, 2007 at 5:51 am
If I’m not mistaken, Excel automatically recalculates all formulas when you open a worksheet. That would generate a new random number if you used a random number formula as discussed here.
I don’t know what you mean by sequential numbers. Do you mean the next number in a series begun in another worksheet? If so, I’m not sure how you would do that.
10 Mike Reimer // Mar 2, 2008 at 7:27 pm
Maria - You are a genius! Your programme does just what I want. Thanks
Mike Reimer
11 Greg L // Mar 31, 2008 at 5:10 pm
Maria:
Your random number generator is super!!! Let me ask a simple (dumb) question. If I had a min range of 1 and a max range of 355 and needed to select say 10 (or “x”) numbers to audit, can a string of number be generated and listed in a cell like 80, 121, 351, 1 etc., or 10 cells populated with the selected 10?
12 Maria Langer // Mar 31, 2008 at 6:00 pm
Greg, all you need to do is use the same random number generator in multiple cells. Each cell will produce a random number. There’s a slight chance that numbers will be repeated, depending on the range and number of random numbers you create. You could get around that by generating a few extra just in case.
I got my start as an auditor, too. Fortunately, I didn’t have to generate my own random numbers. In those days, we didn’t have computers!
13 Greg L // Apr 1, 2008 at 12:13 pm
Great idea… I tried to copy & paste the B5 thru B8 into C5 thru C8 and could not without the password that protects the formula. Greg
14 Maria Langer // Apr 1, 2008 at 1:07 pm
I don’t recall putting a password on it. That was pretty dumb of me. I can’t figure out what the password is!
You can see the formula in the formula bar when you select Cell D8. You can use this information to recreate the worksheet as you need to.
It’s only the formula in cell B8 that you’d need to duplicate. Make sure all duplicates reference the correct min and max cells. Or simply include values instead of cell references in your worksheet.
15 Elijah Chandler // Apr 24, 2008 at 6:32 pm
Love the Random Generator, but I have a request. Could you instruct me how to randomly select and display the text from a cell on another sheet? Any help would be greatly appreciated!
16 Maria Langer // May 1, 2008 at 6:13 am
Ellijah, this sounds like an interesting project. Give me a better idea of what would be on the other sheet. Would it be a sheet full of words or phrases in cells? Would all the content appear in one column? I’m trying to envision what that other sheet looks like so I can come up with a workable formula.
17 Elijah Chandler // May 1, 2008 at 8:07 am
This project is for a 48 hour film festival my video production company is trying to get off the ground here in Madison, IN.
On sheet 1 there is a grid that has 4 headings: Prop, Line of Dialogue, Character, and Genre. There is also a team number (or name) running down the side. On sheet 2 is a duplicate table (minus the team number/name) that has all of the props, lines, etc. that we’ve come up with. What I’d like to do is have sheet 1 randomly select a prop from sheet 2 and repeat for the number of teams we have (say…12). I’d also like it to do this for the other 3 categories, and I’d like it to not repeat a selection that it’s made. If you’d like I could e-mail you an example I made manually. Thanks for taking an interest in this little experiment!
18 Maria Langer // May 1, 2008 at 9:23 am
Sounds like an interesting use of Excel.
But it’s the “not repeat” part that makes it difficult. If you tell it not to repeat, it’s not really random anymore. That means each of the formulas would have to be different because it would have to account for each value already used and discard it.
Not sure if I can come up with the right formulas, but I’ll put my subconscious to work on it. I’ll probably figure it out the next time I’m in the shower. No promises, though.
19 Leigh Thredgold // May 2, 2008 at 9:16 am
hi i have a similar idea to the one above except perhaps a little simpler, i love this random number generator but i have a knockout draw to run and would like something that selcts from a rangeof number 1-100 but then after it makes a random selection it discards that value and the next value will only select from the remaining numbers avaliable until i get to the final number who will be the winner. for example if the first number drawn is 16 then the next number drawn would be any number 1-100 other than 16 and so on.
hope you are able to help if nt that is ok would just make my life alot easier, cheers
20 Dale // Jul 5, 2008 at 4:14 pm
is there any way to change the number values to names in order to randomly generate 2 teams of 8 players out of 16 names?
21 Maria Langer // Jul 6, 2008 at 8:18 am
Dale, you could create a lookup table with names corresponding to numbers. Then you ‘d have to use a lookup formula to spit out the names. Remember that if you simply repeated the formula for each team member, you’d have repeated names (and numbers, of course). There’s no easy way around that.
Leave a Comment