How to Convert a Formula to a Static Value in Excel - - Windows Tips and Tricks with Geek

Friday, February 11, 2022

How to Convert a Formula to a Static Value in Excel


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.

No comments:

Post a Comment