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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
7 Methods to Show Value Instead of Formula in Excel
We will consider the following dataset to demonstrate how to show value instead of formula in Excel.
1. Use 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.
- 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.
- 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.
2. Use 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.
- Go to Cell C11 first. Press the right button of the mouse to avail of the Context Menu.
- Select Copy from the menu.
- Select Values(V) from the Paste Values option.
Look at the formula bar of Cell C11 now.
Only value is visible here instead of formula.
3. Use Paste Special Shortcut to Show Values Only
We will use the keyboard shortcut to avail the Paste Special tool and show values by removing the formula.
- First, go to Cell C11 where the formula exists.
- Press Ctrl+C to copy.
- 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. Use of F2 and F9 Functional Keys to Hide Formula and Show Values
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.
- Go to Cell C11.
- Press the F2 key. It transforms the cell into editable mode.
- Now, press the F9 key.
Only value is visible now, instead of formula.
- How to Show Formulas When Printing in Excel
- Excel Show Formula as Text in Another Cell (4 Easy Methods)
- How to Convert Text to Formula Using the INDIRECT Function in Excel
- Putting Result of a Formula in Another Cell in Excel (4 Common Cases)
- How to Convert Formulas to Values in Excel (8 Quick Methods)
5. Use of Right Button of Mouse to Display Just Value in Excel
We will use only the mouse to remove the formula in this method.
- Go to Cell C11.
- Move the cursor to the edge of the cell.
- 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 formula is removed at the formula bar.
6. Remove 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.
- Select multiple sheets using Ctrl or Shift For adjacent cells, use the Shift button. Otherwise, use the Ctrl button.
- Go to Cell C11 and press Ctrl+ C.
- The Paste Special window will appear. Choose Values and click on OK.
- 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.
- Go to Cell C11 of the Dataste9.
The formula has been removed from the formula bar.
7. Apply 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.
- 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.
- The power query window appears now. Choose Close & Load option.
A new sheet will create, where the formula is removed and the value is visible at Cell C11.
In this article, we explained how to show value instead of formula in Excel. We also showed how to identify cells with formula. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
- How to Convert Formula Result to Text String in Excel (7 Easy Ways)
- Excel VBA: Convert Formula to Value Automatically (2 Easy Methods)
- How to Show All Formulas in Excel (4 Easy & Quick Methods)
- Convert Formula to Value Automatically in Excel (6 Effective Ways)
- How to Stop Showing Formulas in Excel (2 Methods)
- VBA to Remove Formulas in Excel Keeping Values and Formatting