How to Show Value Instead of Formula in Excel (7 Methods)

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.

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.

Paste Values Option from Ribbon to Show Value Instead of Formula in Excel

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.

Paste Values Option from Ribbon to Show Value Instead of Formula in Excel

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 (3 Solutions)


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.

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.

Paste Special from Context Menu to Display Values without Excel formula

Step 2:

  • Select Values(V) from the Paste Values option.

Paste Special from Context Menu to Display Values without Excel formula

Look at the formula bar of Cell C11 now.

Only value is visible here instead of formula.

Read More: Excel Showing Formula Instead of Result (8 Reasons)


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.

Step 1:

  • First, go to Cell C11 where the formula exists.
  • Press Ctrl+C to copy.

Paste Special Shortcut to View Value Rather Excel Formula

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.

Paste Special Shortcut to View Value Rather Excel Formula

Look at the formula bar now.

The formula has been removed.

Read More: Convert Formula to Value Without Paste Special in Excel (5 Easy Methods)


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.

Step 1:

  • Go to Cell C11.
  • Press the F2 key. It transforms the cell into editable mode.

Functional Keys to Hide Formula and Show Values

Step 2:

  • Now, press the F9 key.

Functional Keys to Hide Formula and Show Values

Only value is visible now, instead of formula.

Read More: Show Formula in Excel Cells Instead of Value (6 Ways)


Similar Readings


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.

Step 1:

  • Go to Cell C11.
  • Move the cursor to the edge of the cell.

Right Button of Mouse to Display Just Value in Excel

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.

Right Button of Mouse to Display Just Value in Excel

Look at the dataset.

We can see that formula is removed at the formula bar.

Read More: [Fixed]: Excel Formula Not Showing Correct Result (8 Methods)


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.

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.

Remove Excel Formula from Multiple Sheets to View Values

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.

Remove Excel Formula from Multiple Sheets to View Values

Step 5:

  • Go to Cell C11 of the Dataste9.

The formula has been removed from the formula bar.

Read More: How to Remove Formula in Excel and Keep Values (5 Ways)


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.

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.

Apply Excel Power Query Feature to View Value Alternative of Formula

Step 2:

  • The power query window appears now. Choose Close & Load option.

Apply Excel Power Query Feature to View Value Alternative of Formula

 

A new sheet will create, where the formula is removed and the value is visible at Cell C11.

Read More: How to Return Value of Cell Not Formula in Excel (3 Easy Methods)


Conclusion

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.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo