• ARTICLES
SEARCH

How-To Geek

How to Convert a Formula to a Static Value in Excel 2013

00_lead_image_cell_formula_converted_to_value

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.

01_copying_cells_with_formulas

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

02_pasting_values

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

03_selecting_paste_special

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

04_paste_special_dialog

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.

05_formula_converted_to_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.

06_selecting_part_of_formula

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

07_part_of_formula_converted

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.

Lori Kaufman is a freelance technical writer who likes to write geeky how-to articles to help make people's lives easier through the use of technology. She loves watching and reading mysteries and is an avid Doctor Who fan.

  • Published 05/30/13

Enter Your Email Here to Get Access for Free:

Go check your email!