Using an Excel Pivot Table to Assign Random Numbers

Spreadsheets and graphs on a desk

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:

  1. Bottom – The smallest random number that could be assigned
  2. 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:

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

About David Ringstrom, CPA

David Ringstrom

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.


Please login or register to join the discussion.

Sep 10th 2017 23:47

wow.. I think you helped solve an issue that was stressing me at my work place thanks so much for this article..thaaaaaanks David

Thanks (0)