Contents
When you open an Excel sheet and change some of the data or formulas on the sheet, Excel automatically recalculates all those formulas. This may take some time if the document is large and contains many formulas. However, if some of the cells contain formulas whose values will never change, you can easily convert them to constants. In this lesson, we will show you an easy way to replace both the whole and part of a formula with its value.
Keep in mind that if you convert a formula to a calculated value in the same cell, you won’t be able to go back to the formula later. Therefore, it is recommended to save a backup copy of the Excel document before converting formulas. You can also copy and paste formulas into other cells while keeping the original formulas. Also, if your Excel workbook is configured to manually calculate formulas, don’t forget to update them by pressing the F9.
How to replace the whole formula with a constant
- Select the cells that contain formulas that you want to replace with calculated values. You can select an adjacent range of cells, or select cells individually using the key Ctrl.
- On the Advanced tab Home (Home) press command Copy (Copy), which is located in the group clipboard (Clipboard) to copy the selected cells. Or use the keyboard shortcut Ctrl + C.
- Open command dropdown menu paste (Insert) in a group clipboard (Clipboard) and select Values (Values) in the section Paste Values (Insert values).
- You can also select paste Special (Paste Special) at the bottom of the drop-down menu.
- In the dialog box paste Special (Paste special) select item Values (Values). It also offers other options for pasting copied formulas.
- Now selected cells contain static values instead of formulas.
Remember, if you pasted values into the same cells, the original formulas are no longer there.
If you need to convert formulas in one or more cells, you can use the following method: first, double-click on the cell, then press the F9.
How to replace part of a formula with a constant
In the case where only part of the formula changes and the rest remains constant, you can convert the formula part to a static value. For this:
- Click the cell containing the formula and select the part of the formula that you want to convert to a constant. Then press F9.
When selecting part of a formula, make sure you include the entire operation in the selection. The part of the formula that you are converting must evaluate to a static value.
- The selected part of the formula is converted to a constant. Click Enterto save the result.
Converting formulas to a static value is intended to speed up the processing of large spreadsheets containing many formulas, or to hide formulas underlying a shared Excel document.