If you have data that you don’t plan to change, here are the steps for replacing a formula or just part of it with its calculation in Excel.
When you edit any data in an Excel workbook, any Excel formulas that use that data will immediately update. This is because Excel constantly refreshes your formulas, checking for changes that may impact its calculations.
However, as your workbook grows in size, Excel needs more time and memory to work through the calculations. Rather than see Excel slow down and become unusable, you can replace any formulas you’re using with their results. This will speed up Excel for other tasks and improve overall system performance and memory usage.
You can replace the formula entirely or replace part of it, allowing you to mix data that changes with static data. If you want to replace a formula with the result in Excel, here’s what you’ll need to do.
How to Replace a Formula With the Result in Excel
Open your workbook and sheet in Microsoft Excel. Then, follow these steps to use copy and paste to replace your formula:
- Select the cell containing the formula you want to replace with the result.
- Use the Copy action by right-clicking and choosing Copy or clicking Copy in the Clipboard section of the Home tab.
- With the cell still selected, use the Paste Special action. You can right-click and choose Paste Special or click Paste > Paste Special in the Clipboard section of the Home tab.
- In the Paste Special window, select Values.
- Click OK.
At this point, select the cell—you should only see the result of the formula. You can confirm this by looking at the Formula Bar at the top of the sheet.
Time-Saving Tip: You can also convert your formula to its static result using the F9 key. Select the cell, place your cursor in the Formula Bar within the formula, then hit F9.
For other cool tricks like this, check out our list of these essential Microsoft Excel tips.
How to Replace Part of a Formula With the Result in Excel
If you have a formula using more than one function, you may want to replace part of that formula with the result. For instance, maybe you’re using SUM for two cell ranges, but one of those cell ranges has data that never changes.
This allows you to replace this cell range with a static value.
To replace part of a formula with static data:
- Select the cell containing the formula.
- Go up to the Formula Bar and select the part of the formula you want to replace with the static value.
- Be sure to select all of that part of the formula, including the arguments and parentheses.
- Press F9. You should see the selected section of the formula immediately change to show the result.
- Press Enter to apply the change.
Afterward, select the cell. You should see the remaining part of the formula untouched in the Formula Bar, with one section replaced with a static value.
Using Static Values for Formulas in Excel
By replacing your formulas with their static results, you can reduce the time Excel needs to open and recalculate your workbook. If your formulas include cell references containing data you don’t expect to change, the steps above should help you save system memory.