OmniDocHow to fix the value so that it does not change after generating random numbers in Excel?
Random numbers generated using RAND or RANDBETWEEN will change each time the calculation is recalculated. This article explains clearly which operations will trigger recalculation, and gives the steps and verification methods to convert the formula results into fixed values.
Key points of this article
- RAND and RANDBETWEEN are volatile functions. Editing any cell or pressing F9 will recalculate the value, so the number looks like it keeps changing.
- If you want to fix the result, the direct way is to copy it and use Paste Special to keep only the numerical value and replace the formula with the currently displayed number.
- After pasting as a value, the edit bar should display numbers instead of formulas; if you press F9 and the value remains unchanged, and the value is the same after closing and reopening, it will be considered successful.
- Save a backup with the formula before fixing. The fixed numbers cannot be updated automatically. You can only rely on the backup when you need to regenerate.
Many people use RAND or RANDBETWEEN to generate a batch of random numbers for drawing lots, grouping, or simulating data. After finishing the form, I found that as long as any cell was changed, the numbers would change as a whole; after saving, closing and opening, the numbers would change again. This is not because there is a problem with the file, but that the two functions themselves revalue each time they are recalculated.
This article is written for people who need to determine the random results and then continue processing the table. After reading this, you'll be able to identify which operations trigger recalculations, know how to convert formula results into fixed values, and use a few checks to confirm that the numbers have "settled." The following method is a general approach and is suitable for common desktop office software table operations.
How to fix the value after Excel generates random numbers? One sentence conclusion
Directly convert the formula results into ordinary numerical values, that is, copy the random number area and use Paste Special to retain only the numerical values. After conversion, the number no longer relies on a formula, and subsequent edits and recalculations will not change it. The table below can help you determine which situation you fall into.
| your situation | Recommended practices | reason |
|---|---|---|
| I am satisfied with the random results and just want to keep this group | After copying, use Paste Special to retain only the numerical value. | The formula is replaced with a number and will not be recalculated later. |
| Also want to keep the generation rules and occasionally need new results | Keep the formula and manually recalculate it by pressing F9 if necessary. | The formula is still there and can be re-extracted at any time, but the results may change with each edit. |
| There are many cells in the table that reference random numbers. | First fix the area where the random number is located, and then modify the formula that references it. | After the source is fixed, the calculation results that rely on it will be stable. |
| The file is to be sent to others, and the other party should not change the result. | Save another copy after fixing it to the value, and keep the original with formulas separately. | The receiver sees a fixed number and the original can still be traced |
Before you start: Confirm these points first
First confirm that the random number does come from a random function. RAND and RANDBETWEEN will take new values each time they are recalculated. If the numbers are entered manually or calculated from a formula that does not contain random elements, they will not change on their own and the problem should be looked up elsewhere.
Confirm which area you want to fix. If the selection also contains ordinary formulas, pasting them as values will convert them together, and calculations that originally need to be updated with the data will be invalid. Before taking action, check the scope of the selection clearly.
Back up the version with formulas first. After pasting as a value, the formula can no longer be restored automatically. Once the file is saved and overwritten, the original random rules cannot be retrieved. It is recommended to save a copy first or keep a column of formulas in another worksheet.
View current calculation mode. If the table is set to manual calculation, the number will not change immediately when editing the cell, but it will still be recalculated by pressing F9. The judgment method is very simple: first modify a cell that has nothing to do with the random number and see if the random number changes accordingly. If it changes, it means automatic calculation is in progress, and pressing F9 will also change; if it does not change, it means manual calculation is turned on, but pressing F9 will still cause recalculation.
Pay attention to how you paste it. Ordinary paste will bring over the formula as it is. Only when special paste only retains the numerical value, will the result be written as a number. Many people mistakenly believe that the two have the same effect, and the results are still changing.
Steps to fix random numbers to numerical values
- Select the cell range containing random formulas, such as A2 to A101.
- Press Ctrl+C to copy the area.
- Keep the selection unchanged, right-click and find the Paste Special command, or press Ctrl+Alt+V to open the Paste Special dialog box.
- Select the option to paste only numerical values in the dialog box and click OK. This step replaces the original formula with the currently displayed number.
- Click on any original random number cell to view the edit bar. If a number is displayed instead of a formula like =RAND() or =RANDBETWEEN(...), the conversion was successful.
- Press F9 once and observe whether the value remains unchanged.
- Save the file, close it and reopen it, and confirm that the values are the same as before closing.
Check each item after completion
After fixing, don’t rush to continue processing the data. Use the following steps to confirm that the results are stable.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Edit column content | Click on the random number cell to see whether it is a number or a formula in the edit bar | It is still a formula, indicating that the conversion was not successful. Re-select the area and paste it as a value. |
| Value after pressing F9 | Compare whether the number changes after pressing F9 | The value has changed, indicating that some cells have been left out in the selection, and find out the areas that still contain the formula. |
| Content outside the constituency | Check whether the adjacent data, title and formula have been mistakenly changed. | Misconversion can be undone immediately by pressing Ctrl+Z, and then re-select. |
| A formula that quotes random numbers. | Find formulas that refer to these numbers, such as summation, ranking, search, etc. | They will be recalculated based on fixed values and the results should be stable; if an exception occurs, check whether the reference range is correct |
| The value after closing and reopening | Save, close, reopen, compare numbers | Most of the inconsistencies are not saved, redo and save |
# which method is suitable for you?
- only one draw or grouping is needed : paste directly to the value, and then do the follow-up arrangement after the result is settled, which is convenient. - need to simulate repeatedly and record the results each time : keep the formula. After generating a group with F9 each time, copy the results to another worksheet and save them group by group. - the form should be given to others to use *: save as a new file after being fixed as a value, and the original version with formula is reserved for yourself only.
# frequently asked questions (FAQ)
1. What do I do if I just want to fix a cell?
Double-click the cell to enter the editing state, press F9, and then press enter, and the formula will become the currently displayed value. This method only applies to a single cell, and selective pasting is still recommended when the range is large.
2. Why did I use the paste method that only keeps the numbers, but the numbers have changed?
The common reason is that there is still an unconverted formula in the selection, or ordinary paste is used when pasting. Check the edit bar to make sure that each random number cell is a number, and then press F9 to verify.
3. What is the reason why the Excel random number changes every time I open a file?
The file contains functions such as RAND or RANDBETWEEN, which are usually recalculated when opened, so the number changes. After pasting these cells as values, opening the file will not recalculate.
4. Can you fix the random number and update it at any time?
You cannot do both at the same time. When the formula is fixed to a value, the formula is gone, and the new result needs to be regenerated. If you need to update, you can save the version with the formula in another worksheet or backup file, copy a copy and re-paste it as a value if necessary.
# Summary
Excel generates a random number after how to fix the value, the key is to convert the formula results to ordinary values: copy the area, paste selectively to retain only the value, and then use the edit bar, F9 and reopen the file to confirm. Backing up the version with the formula before conversion can not only fix the results, but also retain the possibility of re-extraction at any time.
