Knowledge base / Excel

RAND in Excel: number ranges, shuffling and preserving results

I explain when Excel draws new values, how to choose a range and how to shuffle whole records. See why ordinary rounding changes the chances of the results.

Kajo RudzińskiPublished Updated 5 min read

A mechanical randomizer distributing golden cubes among equal compartments

You enter =RAND(), receive a number, change something in the sheet and the result may change. This is expected: Excel generates another value when the worksheet recalculates. Before using RAND, I decide which range I need and whether the generated set must later remain fixed.

RAND is useful for practice datasets, simple simulations and shuffling lists. Its boundaries and the method used to produce integers deserve attention. A small shortcut can change which outcomes are possible in the first place.

Syntax and exact boundaries

RAND has no arguments, but the empty parentheses are required:

=RAND()

Microsoft’s documentation specifies an evenly distributed real number greater than or equal to 0 and less than 1. Mathematical interval notation is [0,1). The old Polish page also used (0,1), incorrectly excluding zero.

I do not provide a single expected output for a random formula. Values such as 0.2 or 0.75 can illustrate a calculation, but they are synthetic assumptions. Your workbook need not draw either value during the exercise.

English examples use English names and commas between arguments; Polish examples use localized names and semicolons. Decimal and argument separators depend on regional settings. If a pasted formula is rejected, inspect Excel’s argument tooltip.

Drawing a value from another range

For a value from 10 inclusive to 50 exclusive, scale and shift the draw:

=RAND()*(50-10)+10

First multiply the random value by the interval’s width, 40, then add 10. With an assumed RAND result of 0.25, the calculation gives 20: 0.25 times 40 is 10, and adding 10 produces 20.

The lower boundary remains included and the upper boundary excluded. If the task needs integers with both endpoints included, I use a separate rule rather than expect scaling alone to provide them. I write the permitted outcomes in words first.

Integers: RANDBETWEEN or INT

For integers from 1 to 10, the clearest option is:

=RANDBETWEEN(1,10)

RANDBETWEEN includes both boundaries. This example can return any integer from 1 through 10. The RAND-based version is:

=INT(RAND()*10)+1

RAND times 10 lies between 0 inclusive and 10 exclusive. INT turns it into an integer from 0 through 9. Adding 1 shifts the possible outcomes to 1 through 10.

Synthetic assumptions make the steps visible:

Assumed RAND value Multiplied by 10 After INT and adding 1
0 0 1
0.25 2.5 3
0.95 9.5 10

This explains boundaries without promising a particular sequence. For more on explicit lower and upper bounds, see the RANDBETWEEN entry.

Why ordinary rounding creates a different draw

I remove the invalid function name from the old Polish page. Replacing it with ROUND would not repair its proposed draw from 1 to 10. Rounding RAND times 10 to zero decimal places can also produce 0, and the extreme outcomes do not have the same chances as middle outcomes.

You can understand the issue without running a simulation. Across input values from 0 to 10, an output of 0 collects the segment from 0 to 0.5. An output of 1 collects the segment from 0.5 to 1.5, twice as wide. The upper extreme of 10 also receives a shorter segment. Treatment of individual rounding boundaries does not change those different interval lengths.

When I need evenly distributed consecutive integer outcomes, I use RANDBETWEEN or the appropriate INT partition. Ordinary rounding can be an intentional transformation, but it needs its own justification rather than being presented as an interchangeable method.

Shuffling complete records

In older Excel, add a RAND helper column, fill it down and sort the entire data range by that column. The word “entire” matters. An identifier and its description must remain together after sorting.

For illustration, assume three synthetic keys: 0.7 for record A, 0.2 for B and 0.5 for C. Sorting ascending gives B, C, A. The random key is a helper, not a replacement identifier.

Microsoft 365, Excel 2021 and Excel 2024 offer RANDARRAY and SORTBY. If A2:B6 holds exactly five records, enter this outside the source range:

=SORTBY(A2:B6,RANDARRAY(5))

The new result covers five rows and both columns. The random array supplies one sort key per record. Leave enough empty space for the full result. The number 5 describes this specific source range; it will not automatically follow a sixth added record.

Shuffling preserves each source row rather than drawing the same row repeatedly. Existing source duplicates remain duplicates. I also do not promise unique random keys: equal keys do not uniquely specify the order among tied rows.

Complete trays carrying paired shapes connected by paths to a different ordering

Several generated values at once

RANDARRAY lets you specify dimensions, boundaries and the type of numbers:

=RANDARRAY(5,1,1,10,TRUE)

This requests five rows, one column and integers from 1 to 10. TRUE selects whole numbers. It is not sampling without replacement: values can repeat. Five generated numbers need not be five distinct values.

For a reordered existing list with every element retained, I shuffle rows. For separate numeric draws from a range, I generate numbers. These tasks require different interpretations of the output.

Preserving a draw

RAND draws again when the sheet recalculates. A helper column holding the same formula does not freeze anything. Changing calculation mode does not create a durable record either: a later recalculation can still change the values.

To preserve the result, copy the completed range and choose Paste Values. Microsoft’s paste options guide distinguishes a formula result from the formula itself. For an array shuffle, copy the full output to another area as values, keeping the source and formula separately if useful.

For practice, create five sample records, shuffle them and preserve the result. Check that each identifier still has its correct description. Then recalculate: the formula version may change order, while the value copy should remain fixed. Do not expect a specific next arrangement, or assume every new arrangement must differ from the previous one.

Key takeaways

Can RAND return 0 or 1?

Microsoft specifies a result greater than or equal to 0 and less than 1. Zero belongs to the interval and one does not.

How do I draw an integer from 1 to 10?

Use =RANDBETWEEN(1,10). A RAND-based equivalent is =INT(RAND()*10)+1. Ordinary rounding does not provide the same boundaries and chances.

How do I preserve the generated results?

Copy the result range and paste it as values. Pasting formulas normally preserves their ability to draw new values when Excel recalculates.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.