How to Generate Random Numbers 1-20 in Excel – Step by Step Guide

Random numbers 1-20 are often needed in Excel for simulations, testing, assigning unique IDs, or simply creating sample datasets. A common requirement is to generate numbers between 1 and 20. Excel provides multiple built-in formulas and methods to achieve this quickly and reliably. In this guide, we will cover the formulas, common pitfalls, and advanced options to help you use random numbers effectively.

Key Takeaways

• Use =RANDBETWEEN(1,20) to generate a random integer between 1 and 20.
• RAND() creates a random decimal, which can be scaled to fit between 1 and 20.
• Random numbers recalculate every time the worksheet updates unless you copy and paste values.
• Dynamic arrays allow you to generate multiple random numbers at once in Excel 365.
• VBA can be used to create fixed random numbers that do not change on recalculation.

What is the RANDBETWEEN function

The RANDBETWEEN function is the easiest way to generate whole numbers within a specified range. For example, if you want numbers between 1 and 20, you can use:

=RANDBETWEEN(1,20)

This will return an integer between 1 and 20, including both limits.

How to Generate Random Numbers 1-20 in Excel

1. Using RANDBETWEEN:

=RANDBETWEEN(1,20)

Returns an integer between 1 and 20.

2. Using RAND with scaling:

=INT(RAND()*20)+1

This multiplies a decimal (0 to 1) by 20, converts to integer, and shifts it into the 1–20 range.

3. Generating Unique numbers 1-20 shuffled

This is a unique way to create a sequence of the numbers 1 to 20 in a random order:

=SORTBY(SEQUENCE(20),RANDARRAY(20))

Common mistakes and tips

Numbers keep changing: Random functions update with every calculation. Use Copy > Paste Values to fix them in place.

Decimals instead of integers: Using RAND() without INT() returns decimals. Always wrap with INT() to get whole numbers.

Out of range values: Check your formula carefully. Using RAND()*20 without +1 will produce numbers from 0–19, not 1–20.

Duplicates: Standard random functions may return duplicates. If you need unique values, use =SORTBY(SEQUENCE(20),RANDARRAY(20)).

Bonus tips and advanced scenarios

Generate unique random numbers 1–20:

=SORTBY(SEQUENCE(20),RANDARRAY(20))

This shuffles numbers 1–20 without duplicates.

Use VBA to generate static random numbers:

Sub RandomNumbers()
    Dim i As Integer
    For i = 1 To 20
        Cells(i, 1).Value = Int((20 - 1 + 1) * Rnd + 1)
    Next i
End Sub
  

This creates 20 random integers in column A that do not recalculate.

Random numbers for simulations: Combine random integers with lookup tables to simulate test scenarios or random sampling from a dataset.

Power Query option: If pulling in large data, Power Query can generate index columns and apply random order for sampling without volatile formulas.

Use cases

Simulations. Create random inputs for financial models or Monte Carlo analysis where repeated trials are needed.

Why it matters: Financial analysts and researchers often test the impact of uncertainty on their models. Random inputs let them run hundreds or thousands of scenarios without manually entering values.

How to do it: Use =RANDBETWEEN(1,20) or =RAND() scaled to your model variables. Repeat across many rows or columns to simulate multiple trials.

Practical add-on: Combine random numbers with statistical formulas such as =NORM.INV(RAND(), mean, stdev) for more realistic distributions.

Benefit: Produces realistic scenario testing that can highlight risks and ranges of outcomes instead of a single forecast.

Classroom activities. Teachers can assign students random numbers between 1 and 20 for group work or problem sets.

Why it matters: Random assignment keeps activities fair and avoids repetitive grouping patterns in class exercises.

How to do it: Enter student names in column A and apply =RANDBETWEEN(1,20) in column B. Sort by the random numbers to shuffle group order.

Practical add-on: Use =SORTBY(A2:A21,RANDARRAY(20)) in Excel 365 to instantly shuffle names.

Benefit: Ensures students collaborate with different peers across activities and makes assignments impartial.

Testing spreadsheets. Generate sample data quickly without relying on actual business numbers.

Why it matters: Developers and analysts often need test datasets to validate formulas, charts, or dashboards before applying them to real data.

How to do it: Insert =RANDBETWEEN(1,20) across a grid of cells to simulate sales figures, scores, or IDs.

Practical add-on: Wrap random formulas with =ROUND() or =TEXT() to mimic actual formats such as percentages or text codes.

Benefit: Speeds up testing while protecting sensitive information by avoiding use of actual company data.

Competitions. Use random numbers to assign raffle tickets or select winners fairly.

Why it matters: Random draws guarantee impartiality in prize distributions or event games.

How to do it: Assign each participant an ID, then generate random numbers between 1 and 20 to select winners.

Practical add-on: Use =INDEX(A2:A21,RANDBETWEEN(1,COUNTA(A2:A21))) to pick random names directly from a participant list.

Benefit: Simplifies prize draws while ensuring transparency and fairness in selection.

Scheduling. Randomly assign time slots, tasks, or order of execution for fairness.

Why it matters: When tasks or presentations must be scheduled, randomness avoids bias in ordering.

How to do it: Enter tasks in column A and apply =RANDBETWEEN(1,20) in column B. Sort by the random numbers to set order.

Practical add-on: In Excel 365, apply =SORTBY(A2:A21,RANDARRAY(20)) to shuffle schedules instantly.

Benefit: Provides a transparent, unbiased method for assigning order or distributing responsibilities.

FAQ

1. What is the difference between RAND and RANDBETWEEN?
RAND() generates a decimal between 0 and 1, while RANDBETWEEN(a,b) generates whole numbers between a and b.

2. Can random numbers be made permanent?
Yes. Use Copy > Paste Values or VBA to fix random numbers so they do not change when the sheet recalculates.

3. Can I generate random decimals between 1 and 20?
Yes. Use =RAND()*19+1 to return a random decimal between 1 and 20.

4. How do I avoid duplicate random numbers?
Use =SORTBY(SEQUENCE(20),RANDARRAY(20)) to generate all numbers 1–20 in random order without duplicates.

5. Does Excel recalculate random numbers automatically?
Yes. Every recalculation triggers new random values. Pressing F9 will refresh them.