How to Freeze Random Selection in Excel

Often you need to generate random numbers or strings for specific purposes, especially in the case of statistical sampling. However, you have to freeze the random selection for further uses. In this instructive session, I’ll show you 3 methods based on the 2 scenarios on how to freeze random selection in Excel.


Download Practice Workbook


Freeze Random Selection in Excel

Let’s introduce today’s dataset where the Employee Information is given along with their ID No and Name. Besides, Random ID No and Random Name are displayed in columns E and F respectively.

How to Freeze Random Selection in Excel Dataset

So, you have to freeze either random numbers or random strings. Because these are continuously changing even if you click a single option in Excel. On the whole, you can utilize the following 3 methods to freeze random selection.

  • Utilizing the Paste Options
  • Dragging the selection holding right-clicking
  • Changing the calculation options from Automatic to Manual

Now, I’m going to show you the application of these methods based on 2 different scenarios. The first scenario deals with the methods to freeze random numbers and the rest scenario addresses the methods to freeze random strings.

Let’s explore the methods.


Scenario 1: Methods to Freeze Random Numeric Selection

Let’s generate the random numbers using the RANDBETWEEN function before going to the methods of freezing. The function returns integer numbers between two given numbers.

For example, if you want to get the integer numbers between 1 and 10000000, you can use the formula below.

=RANDBETWEEN(1,10000000)

Here, 1 is the bottom argument and 10000000 is the top argument.

How to Freeze Random Selection in Excel Generating Random Numbers

Now, you need to freeze the random numbers in the E5:E15 cell range.

Scenario 1


1. Utilizing Paste Options 

Luckily, you can freeze the random numbers utilizing the Paste Options feature in Excel. Please do the following task.

➜ Initially select the C5:C15 cell range and press CTRL + C to copy the random numbers.

➜ Later, go to the Context Menu by right-clicking (in the E5 cell) and choose the Values (V), one of the paste options (under the Paste Options feature).

How to Freeze Random Selection in Excel Utilizing Paste Opitons

Eventually, you’ll get the frozen random numbers in the E5:E15 cell range.

How to Freeze Random Selection in Excel Utilizing Paste Opitons

Read More: Random Selection Based on Criteria in Excel (3 Cases)


2. Dragging Numbers Holding Right-Click

Again, you can execute the same task by following this method.

➜ Select the random numbers first and then move your cursor over the border of the cell range.

➜ Then, you’ll see an icon of the move pointer as shown in the following image, and then drag the random numbers holding right-clicking (remember this, otherwise this method won’t work) to the E5:E15 cell range.

Dragging the Numbers Holding Right-Click

➜ Shortly, you’ll get some options after quitting right-clicking. Then, choose the Copy Here as Values Only option.

Dragging the Numbers Holding Right-Click

Eventually, you’ll get the frozen random numbers that won’t change.

Dragging the Numbers Holding Right-Click

Read More: Excel VBA: Random Selection from List (3 Examples)


3. Changing Calculation Options from Automatic to Manual

The random numbers are changing incessantly because the calculation option is set as Automatic. So, if you change the option to Manual, the random numbers won’t change further. Let’s do it.

➜ Just go to the Formulas tab > click the drop-down list of the Calculation Options > choose the Manual option.

How to Freeze Random Selection in Excel Changing Calculation Options

Now, if you copy (press CTRL + C) the random numbers and paste (press CTRL + V) them to another location, you’ll get the frozen random numbers. Certainly, the numbers won’t change.

How to Freeze Random Selection in Excel Changing Calculation Options

Read More: Random Selection from List with No Duplicates in Excel (5 Cases)


Scenario 2: Methods to Freeze Random Strings Selection

In this scenario, you’ll explore how to create random strings or texts and then the methods to freeze the random strings.

Let’s say, you want to generate a Random Name list in the E5:E15 cell range of the Name given in the C5:C15 cell range.

Just use the following formula in the E5 cell.

=INDEX($C$5:$C$15,RANDBETWEEN(1,COUNTA($C$5:$C$15)))

Here, C5:C15 is the cell range representing Name.

⧬ In the above formula, the COUNTA function counts the row number of the C5:C15 cell range. Subsequently, the RNADBETWEEN function generates random numbers from the 1 to the found the row number in the previous output. Lastly, the INDEX function returns the random name based on the random number generated by the RNADBETWEEN function.

Generating Random Strings

After pressing ENTER and using the Fill Handle tool, you’ll get the Random Name list.

Generating Random Strings

Now, come to the main topic! You have to freeze the random strings.

Scenario 2

Read More: How to Generate a Random String from a List in Excel (5 Suitable Ways)


1. Dragging Strings Holding Right-Click

As shown in Method 2 of Scenario 1, you can easily drag the strings to the D5:D15 cells holding right-click.

Dragging the Strings Holding Right-Click

Therefore, you’ll get the frozen random name after choosing the Copy Here as Values Only option.

Dragging the Strings Holding Right-Click

Read More: How to Select Random Sample in Excel (4 Methods)


2. Changing Calculation Options

Similarly, you may change the calculation option from Automatic to Manual as discussed in Method 3 of Scenario 1.

How to Freeze Random Selection in Excel Changing the Calculation Options in the case of Random Strings

After copy-pasting the random strings from column C to D, you’ll get the following output.

How to Freeze Random Selection in Excel Changing the Calculation Options in the case of Random Strings

Read More: How to Make a Random Selection from List Without Repetition in Excel


Conclusion

That’s the end of today’s session. Surely, I believe from now you can freeze random selection in Excel effectively. If you find this article fruitful, don’t forget to share it in your community. Anyway, if you have any queries or recommendations, please share them in the comments section below.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo