Alok Paul
While dealing with MS Excel in daily life, we sometimes prefer sending a file to another person letting him view the result only. So in this article, we will discuss 7 methods on how to show value instead of formula in Excel with proper illustrations and explanations.
We will consider the following dataset to demonstrate how to show value instead of formula in Excel.
1. Using the Paste Values Option from Ribbon to Show Value Instead of Formula in Excel
We will apply a quick option from the Ribbon in this section to display values instead of formulas in Excel.
Step 1:
- First, move the cursor to cell C11. We can see a formula in the formula bar.
- Now, choose Copy from the Clipboard or press Ctrl+C.
Cell C11 is copied. Now we will paste it into the same cell.
Step 2:
- Click Paste on the Clipboard group.
- Choose Values(V) from the Paste Values option.
Now, look at the formula bar.
The formula has been removed, only the value is showing now.
Read More: [Fixed!] Formula Result Showing 0 in Excel
2. Using Paste Special from Context Menu to Display Values Only
In this section, we will show the use of the Context Menu to display values. We get the context menu by pressing the right button of the mouse.
Step 1:
- Go to Cell C11 first. Press the right button of the mouse to avail of the Context Menu.
- Select Copy from the menu.
Step 2:
- Select Values(V) from the Paste Values option.
Look at the formula bar of Cell C11 now.
Only value is visible here instead of the formula.
Read More: Why Excel Shows Formulas Instead of Results
3. Applying Paste Special Shortcut in Excel to Show Values Only
We will use the keyboard shortcut to avail the Paste Special tool and show values by removing the formula.
Step 1:
- First, go to Cell C11 where the formula exists.
- Press Ctrl+C to copy.
Step 2:
- Now, press Alt+E+S to avail the Paste Special tool.
- Paste Special window will appear now. Choose Values from the Paste options.
- Then press OK.
Look at the formula bar now.
The formula has been removed.
4. Using F2 and F9 Functional Keys to Hide Formula and Show Values in Excel
It is the simplest method. We will use the function keys of the keyboard here. Those function keys will view the values and hide the formulas.
Step 1:
- Go to Cell C11.
- Press the F2 key. It transforms the cell into editable mode.
Step 2:
- Now, press the F9Â key.
Only the value is visible now, instead of the formula.
Read More: Show Formula in Excel Cells Instead of Value
Similar Readings
5. Using Right Button of the Mouse to Display Just Value in Excel
We will use only the mouse to remove the formula in this method.
Step 1:
- Go to Cell C11.
- Move the cursor to the edge of the cell.
Step 2:
- Now, press the right button of the mouse. Keep pressing the button.
- Move the cursor to the adjacent cell and move back to Cell C11. Leave the mouse.
- A menu will appear. Choose Copy Here as Values Only option from the menu.
Look at the dataset.
We can see that the formula is removed at the formula bar.
6. Removing Formula from Multiple Sheets to View Values Using the Paste Special Feature
In this section, we will discuss how to remove the formula from multiple sheets at a time and show values.
We have two sheets named Dataset8 and Dataset9. Both sheets contain formulas in the same cell which is Cell C11.
Step 1:
- Select multiple sheets using Ctrl or Shift For adjacent cells, use the Shift button. Otherwise, use the Ctrl button.
Step 2:
- Go to Cell C11 and press Ctrl+ C.
Step 3:
- The Paste Special window will appear. Choose Values and click on OK.
Step 4:
- Now, go to the Sheet Name field at the bottom of the sheet.
- Press the right button of the mouse. Choose Ungroup Sheets from the list.
Step 5:
- Go to Cell C11 of the Dataste9.
The formula has been removed from the formula bar.
7. Applying Excel Power Query Tool to Show Value Instead of Formula
We will use the Power Query feature of Excel to view values instead of formulas.
Step 1:
- Go to Cell C11, which contains a formula.
- Choose From Table/Range option from the Data tab.
- A new dialog box named Create Table. Choose the range from the dataset.
- Mark the box of My table has headers, then press OK.
Step 2:
- The power query window appears now. Choose the Close & Load option.
A new sheet will be created, where the formula is removed and the value is visible at Cell C11.
Read More: How to Display Cell Formulas in Excel
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we explained how to show value instead of formula in Excel. We also showed how to identify cells with a formula. I hope this will satisfy your needs. If you have any queries or suggestions, feel free to use the comment box.
