Using an Excel Pivot Table to Assign Random Numbers
Periodically you may have a need to assign random numbers to an employee list, such as for raffle drawings or drug testing. In this article I’ll show how you can use the RANDBETWEEN function together with an Excel pivot table to pull a randomized assortment of employees.
The RANDBETWEEN function was introduced in Excel 2007 and has two arguments:
- Bottom – The smallest random number that could be assigned
- Top – The largest random number that could be assigned
Thus a formula like =RANDBETWEEN(1,1000) would generate random numbers between 1 and 1,000. You may be familiar with the RAND function, which generates random numbers between 0 and 1. Thus historically Excel users would craft a formula such as =RAND()*1000 to generate random numbers.
Both RANDBETWEEN and RAND are known as volatile worksheet functions in Excel, which means that the result changes every time you make any change to an Excel worksheet. To see this first hand you can put together some sample data as shown in Figure 1:
- In a blank worksheet type the word Employee in cell A1.
- Type the word Random in cell B1.
- Type the word Employee1 in cell A2.
- Use the Fill Handle in cell A2 to drag the cell contents down to A21, which should result in a series of Employee1 through Employee20.
- Select cells B2 through B21, type =RANDBETWEEN(1,1000) and then press Ctrl-Enter to fill all cells at once.
- The numbers will change every time you press F9, which recalculates the worksheet, or when you edit any cell within the worksheet. To freeze the numbers press Ctrl-C.
- Right-click on cell B2.
- Click the Paste Values icon in Excel 2010 and later. In Excel 2007 choose Paste Special and then double-click Values.
- At this point the random numbers are permanently assigned. Due to their volatile nature the numbers may change one more time when you paste as values.
- Click the Undo command or press Ctrl-Z to restore the formulas so that you won’t have to recreate the formulas again.
Figure 1: The RANDBETWEEN function assigns random numbers within a spreadsheet.
Working with random numbers can feel like trying to hit a moving target, but we can use a pivot table in Excel to get a break from the action, as shown in Figure 2:
- Click any cell within employee list.
- Activate Excel’s Insert menu.
- Click the Table command.
- Click OK to convert the list into a table.
- Click Summarize with PivotTable on the Design menu.
- Click OK to create the pivot table.
- Click the Employee checkbox on the PivotTable field list.
- Click the Random checkbox on the PivotTable field list.
Figure 2: A pivot table enables you to freeze random numbers while still keeping the formulas intact.
Unlike worksheet formulas, the results within a pivot table only change when you refresh the pivot table. Thus, this allows us to “freeze” our random numbers until we wish to assign them again. Figure 3 shows how to pick a group of 5 employees from the list, as well as how to pick another random set of 5 at any time:
- Click the Filter button in the Row Labels field.
- Choose Value Filters.
- Chose Top 10.
- Set the number of employees you wish to view, such as 5.
- Click OK.
- The pivot table now shows the 5 employees that had the highest random numbers assigned.
- Right-click any cell within the pivot table.
- Choose Refresh.
- The pivot table shows a new random set of 5 employees.
Figure 3: The Top 10 filter within a pivot table will automatically select a random set of employees each time you refresh the pivot table.
Given that we used the Table feature with our list, going forward you can simply add new names to the bottom of the employee list. The Table feature will automatically copy the RANDBETWEEN formula down. When you right-click and refresh the pivot table any new employees will be included in the random pool.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.