ExcelInsider Team
When we work with Excel, we often want to get the final value that is shown in a cell rather than the formula used to generate it. That is because if we reference the cell for the calculation in other cells, then that calculation might get some error because we are referencing the cell formula and not the value (by default).
We can solve this problem by using the displayed value (or return value) instead of the underlying formula that is being used to get the value. This is mostly used when we are sharing data, preparing reports, exporting data etc for use in other systems.
To return value of cell not formula in excel, follow these steps:
➤ Select the cell or range containing formulas.
➤ Copy the selection.
➤ Right-click → Paste Special → Choose “Values”.
In this article, we will walk through different methods to return just the value from a cell not formula. We will use methods like the combination of Copy and Paste Special, Replace Formula with F9 key, and Set Precision as Displayed.
What Does "Returning a Value in Excel" Means?
In Excel, “returning a value” means extracting the final result that appears in a cell rather than the formula behind it.
For example, A cell contains =A1+A2 and displays “200”,
Here, returning the value means getting 200, not the formula.
This is very important when working with exported files, automation, or data presentation. Otherwise errors can occur because the formula and display value sometimes give different results if we are referencing it on another cell.
Applying Copy with Paste Special Combination to Return Value of Cell
This method lets us replace a formula with its current result. It effectively turns a dynamic calculation into a fixed value. We usually use this when we need to preserve the result of a formula without allowing it to change if source data changes. It is also very useful in reporting or archiving datasets where returning the value of the cell and not the formula is necessary.
Steps:
➤ Open your Excel worksheet containing formulas you want to convert. You should see both the formula and the calculated result in your cells. In our dataset, the commission column (C2:C11) contains formulas like =B2*10%.
➤ Click and drag to select the range C2 to C11 that contains the commission formulas.
➤ Right click and choose Copy, or press Ctrl + C on your keyboard.
➤ Right-click again on the same selected range. Under the Paste Options, choose Paste Special, then select Values. You can also select the value option directly like in the picture. And then click OK.
Alternatively, you can use the keyboard shortcut: Ctrl + Alt + V → V → Enter .
➤ All the cell now show just the static value (e.g., 120), not the formula (=B2*10%).
Note:
This action is permanent; once the formula is replaced, it cannot be restored unless you undo immediately (Ctrl + Z) or have a backup.
Using Replace Formula and F9 Shortcut
Here, we will replace the formula with its current calculated value. For that we will use the F9 key while editing the cell. It is useful when you want to preserve the result of a formula but remove the underlying dynamic calculation. This is good for financial summaries, invoice generation, or reports where finalized figures should remain unchanged.
Steps:
➤ Open the worksheet with formulas you want to convert. In our invoice table, column D (Total) contains formulas like =B2*C2, where Quantity is in column B and Unit Price in column C.
➤ Select a formula cell to convert. Click on cell D2 that contains =B2*C2.
➤ Enter edit mode by pressing F2 . This lets you to edit the formula directly inside the cell.
➤ Use your mouse or keyboard to highlight the entire formula (=B2*C2) or just the part you want to replace.
➤ Press the F9 key. Excel will immediately calculate and display the result of the selected formula (e.g., 10 * 2.5 = 25).
➤ Press Enter to confirm the change. The cell now contains only the static value (e.g., 25) instead of the formula.
➤ Apply the same steps to other cells (D3 to D11) to replace formulas with their results using a fill handle.
Note:
➥ This method permanently removes the formula.
➥ The F9 method only works in edit mode (F2).
➥ You cannot undo this action after pressing Enter unless you immediately use Ctrl + Z .
Disable Show Formulas Mode to Return Value of Cell Instead of Formula
This method uses Excel’s built-in functionality to switch between showing formulas and displaying their values. We usually use it for reviewing or troubleshooting formulas in large datasets. When working with spreadsheets containing many formulas, this helps verify calculations or check for formula errors. If you have this mode enabled then turning off will do the work.
Steps:
➤ Open your Excel worksheet with formulas you want to check.
➤ Go to the Formulas tab in the Ribbon.
➤ Click on Show Formulas in the Formula Auditing group. This will toggle formula view on/off.
➤ Once toggled off, Excel will display the final value of each formula instead of the formula itself.
Note:
➥ Toggling does not change the underlying data — it only switches the display mode.
➥ Works across entire sheet.
Frequently Asked Questions (FAQs)
How do I get the value of a cell in Excel and not the formula?
Use Copy → Paste Special → Values to replace formulas with their results.
How do I get Excel to show the result instead of the formula?
Check if the formula is preceded by a ‘ (apostrophe) or if the cell is formatted as “Text.” Then change to “General” or “Number” and press Enter..
Concluding Words
To return only the value of a cell without the formula in Excel, the Paste Special → Values method is the most direct and reliable. It’s easy to use for cleaning up spreadsheets, exporting clean data, or locking in final results only and not the formulas. Other methods include using the F9 key shortcut, Show Formula Option or default referencing behavior. You can use them too in suitable situations.
