? asked in Computers & InternetSoftware · 8 years ago

# How to generate 1 to 10 in Excel without repeating?

I have 10 cells in Excel that I wanted randomly number between 1 & 10.

Therefore each cell will feature a number from 1 to 10 inclusive.

Ideally looking NOT to use macro or any other cells.

Relevance
• 8 years ago

Here is one, not too elegant, method to do as you wish using a single formula. However, you will have to adjust your options to enable the use of circular references. This formula assumes the non-repeating numbers 1-10 will be returned in cells A1:A10.

For Excel 2003, and prior:

Go to Tools > Options and select the Calculation tab. Check the 'Iteration' checkbox. Click 'OK'.

For Excel 2007/2010:

Go to File > Options and select the Formulas menu item.

Place a check mark in 'Enable iterative calculation' checkbox. Click 'OK'.

In cell A1, enter the formula:

=IF(COUNTIF(\$A\$1:\$A\$10,A1)>1, RANDBETWEEN(1,10),A1)

Fill the formula down through cell A10.

Note: This formula will not recalculate when the Calculate event occurs, i.e. pressing F9. To create a new list of numbers in A1:A10, simply select cell A1 and drag the formula down through A10 again. Like I said, not too elegant. However, it does avoid VBA and other cells.

• Greg G
Lv 7
8 years ago

Do you care if the numbers repeat? If not, you can simply use RANDBETWEEN:

=RANDBETWEEN(1, 10)

copy that down from A1 to A10.

If you want each number to be unique, use this in A2:

=LARGE(ROW(\$1:\$10) * NOT(COUNTIF(\$A\$1:A1, ROW(\$1:\$10))), RANDBETWEEN(1, 11-ROW(A1)))

Confirm with CTRL + SHIFT + ENTER, not just enter since this is an array formula.

Copy down to A11.

• sak
Lv 4
4 years ago

#a million became into humorous, in spite of the undeniable fact that it did no longer truly stick to the subject count. i presumed #10 confirmed extra lyricism and that i enjoyed "incomplete like a play without narration". there have been some truly good similes in that one. #10 gets my vote.

• 8 years ago

If you want random, then you have to allow for repetition, otherwise, the last cell will be determined and not random.