Transforming Excel Formulas Into Constant Values: A Step-by-Step Guide (Excel 2013)

When you open an Excel worksheet or change any entries or formulas in the worksheet, Excel automatically recalculates all the formulas in that worksheet by default. This can take a while if your worksheet is large and contains many formulas.

However, if some cells contain formulas whose values will never change, you can easily convert these formulas to static values, thus speeding up the recalculation of the spreadsheet. We will show you an easy method for changing an entire formula to a static value and also a method for changing part of a formula to a static value.

NOTE: Keep in mind that if you convert a formula to a static value in the same cell, you cannot go back to the formula. So, you may want to make a backup of your worksheet before converting formulas. However, you can also copy formulas and paste them as values into different cells, preserving your original formulas. Also, if your worksheet is set to calculate formulas manually , make sure to update your formulas by pressing F9 before copying the values.

Select the cells that contain formulas you want to convert to static values. You can either drag across the cell range, if they’re contiguous, or press Ctrl when selecting cells if they are not contiguous. Click Copy in the Clipboard section of the Home tab or press Ctrl + C to copy the selected cells.

Click the Paste button in the Clipboard section of the Home tab and click the Values button in the Paste Values section.

You can also select Paste Special at the bottom of the drop-down Paste menu.

On the Paste Special dialog box, select Values in the Paste section. This dialog box also provides other options for pasting the copied formulas.

The selected cells now contain the results of the formulas as static values.

Remember, if you pasted over the same cells, the original formulas are no longer available.

NOTE: If you only need to convert one cell (or a few rather than many) formulas, you can double-click in a cell containing a formula and press F9 to convert that formula to a static value.

If the result of a part of a formula will not change, but the results from rest of the formula will vary, you can convert part of the formula to a static value while preserving the rest of the formula. To do this, click in the cell with the formula and select the part of the formula you want to convert to a static value and press F9.

NOTE: When selecting part of a formula, be sure that you include the entire operand in your selection. The part of the formula you are converting must be able to be calculated to a static value.

The selected part of the formula is converted to a static value. Press Enter to accept the static result as part of the formula.

Converting formulas to static values can be useful for speeding up large spreadsheets containing a lot of formulas, or for hiding the underlying formulas you used if you need to share the spreadsheet with someone.

Also read: