Hi, this is Wayne again with a topic “Create Random Student Picker or Raffle Picker in Excel”.
In this excel video, i’m going to show you how to use the rand between function in excel to create a random student picker, or it could also be used as a raffle picker to select a winner out of a group of names. So here i have a spreadsheet with a list of students, and each student name also has a number, and then here i have a space where i’d like to be able to have a random student name appear when i press a button. Let’S make it happen. So to do this, i’m just going to click on cell e3 type equals randbetween. The randbetween function returns a random number between any two numbers that you specify so i’ll put in a left parenthesis. Next, i need the bottom number comma, then the top number, so the bottom number or lowest number in this number set is one and i’ll just click here on b3 i could have typed the number one if i wanted to but i’ll just put b3 comma and Then the top number or highest possible number that i want excel to choose between is 12, so i’ll click on b14. In this case, and then back up here in my formula, i should probably put in the right parenthesis you don’t actually have to but i’ll tap enter and the formula has randomly produced.
The number one so alex is the student that i guess i should call upon. For this next question that i’m thinking of asking and then i can just continue to use this random name picker to help me be more fair about whom i ask questions of in my classroom now. What do i do to reset the random name? Picker i’d like to have a button that i can press to make another name selection. Fortunately, there’s a key on the keyboard already set up to do this.
It’S going to work great. All i have to do is press the f9 key and what it does is it resets all of the formulas in the spreadsheet, and so every time i press f9, the formula runs again and randomly picks a number between 1 and 12 in this case. Okay. So this is working great this time. Elaine is the name picked now to make this a little more fun. I could make it so that the name that’s selected gets highlighted once it’s picked now to make this work, i’m going to use conditional formatting here on the home, tab home ribbon in the styles group. It says conditional formatting and that’s what i’ll use, but first, i’m just going to click on aleks here on cell c3, i’ll go up to conditional formatting. I want to highlight the cell c3 based on this rule and which type of rule.
Well, i’m going to go down here to more rules and i’ll select use a formula to determine which cells to format and i’m going to scoot this over. So you can see this a little better, i’m just going to click down here, underneath where it says, format, values where this formula is true and i’ll click and i’ll just select b3 equals e3, so in other words, did excel randomly pick the same number as alex Number one: if so, what do i want the formatting to look like? I can click format and set that up the way i want it to be formatted, i’m just going to focus on the fill color i’ll. Have it fill in green and i could make other changes if i wanted to, but i’m happy with that so i’ll click, ok, click, ok again and now, if i press f9 and it selects number one alex is highlighted in green. I know i’m supposed to call on alex now, of course.
At this point, i need to repeat that same process for each of the other students, but let me show you a shortcut that can help you do that: a little faster, i’m just going to click up here on conditional formatting, manage sales rules and what we have Here is a list of conditional formatting rules that are in this cell c5. But if i change this drop-down to this worksheet, it will show all of the conditional formatting rules in the whole spreadsheet. So this is the one that i just created. That’S making alex turn green when his number is picked i’ll, select that and then just duplicate the rule and then i’ll double click on it to change it up instead of b3. I want b4, but i still want it to reference e3. I don’t want that to change click, ok, but notice.
I also need to change what cell it applies to. If i don’t change that, instead of john’s name lighting up, it would be alex’s name. So i’m going to change this to a 4 click apply. Then click. Ok, let’s tap f9 a few times until i get the number 2 and it worked jumping back into the conditional formatting manage rules button again, switching from current selection to this worksheet. I could then select my most recent rule, duplicate it and repeat the process again for susie in this case, so i’ll change, four to five i’ll leave, three, the same click, ok and then i’ll change this to c. Five click apply.
Click! Ok, give me a minute to repeat those steps for the other students and then i’ll resume the video okay, i’ve added a conditional formatting rule for each of the students. I’Ll click apply, click, ok and my random student generator or, like i said it could be a random raffle winner, selector or just about anything that needs to be randomized. It should be ready to go. I can just press f9 and the student who’s selected.
The student name will be lit up in green thanks for watching this video. I hope you found it to be helpful. If you did please like follow and subscribe, and when you do, please click the bell. So you’ll be notified when i post another video, if you’d like to support my channel, consider clicking the thanks button below the video or you can support me through my patreon account, or by buying channel merch and you’ll, find some information about those options. In the description below the video .