The Excel workbook is included with our video training.

Abstract 

This video shows how you can replace formulas with the results they return-- a useful way to stop Excel from calculating new results.

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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.