Abstract
Transcript
As you start to work with more formulas in Excel, you'll find you often want to replace formulas with the values they generate. One common situation is that your formula has calculated a result, and you want to stop Excel from calculating a new result.
Let's take a look.
To illustrate converting formulas to values, let's look at an example that uses the RANDBETWEEN function to assign a list of people randomly to four different groups. RANDBETWEEN takes two arguments: the first argument is the bottom value, and the second argument is the top value.
In this case, We'll use 1 and 4, because we want four groups. Once I copy the formula down, each person in the list has a group number between 1 and 4.
But notice how RANDBETWEEN recalculates whenever we change anything in the worksheet. With each change, we get a new set of random numbers. In most cases, one set of random numbers is plenty, so we need a way to stop Excel from calculating a new result. The simplest way is to replace the formulas in each cell with the values that have already been calculated.
Remember that you could use the keyboard shortcut F9 to replace a formula with a value in a single cell. Just edit the cell, put the cursor in the formula, then press F9. Excel will replace the formula with the result of its calculation and you can press Enter to update the cell.
But we have a lot of formulas here, so the fastest way to replace formulas in bulk is to use Paste Special.
First, select all the cells that contain formulas, and copy the selection. Next, open the Paste Special dialog and select Values. When you click OK, Excel will overwrite the formulas with their values.
Now when we check the cells, we can see that the formulas are gone.
Use this approach whenever you want to replace live formulas with static results.
