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

Get FREE Advanced Excel Exercises with Solutions!

If you don’t require the value to be tied to any other cells and only want to show the value, or if the formula contains confidential data that you don’t want to share, removing formulae and keeping the data in Excel may be important. In this tutorial, we’ll demonstrate to you how to remove formula in Excel and keep values with some quick examples.


How to Remove Formula in Excel and Keep Values: 5 Quick Ways

We’ve shown a data set for calculating the yearly change in revenue percentage (%) in the screenshot below. However, we do not wish to reveal the reference formula we used. So, we’ll show you 5 simple techniques to eliminate the formulae while keeping the value in the section below.

 Remove Formula in Excel and Keep Values


1. Right-Click to Remove Formula in Excel and Keep Values

To begin, just remove formulas using your mouse; to do so, follow the instructions below.

Right-Click to Remove Formula in Excel and Keep Values

  • Firstly, select the cells >> Press Ctrl + C to copy >> Click the Right-Click button on your mouse.
  • Finally, select the Paste Values.

Right-Click to Remove Formula in Excel and Keep Values

  • Therefore, we will see in the following cells that the formulas are removed from the formula bar but the values remain.

Right-Click to Remove Formula in Excel and Keep Values


2. Use Home Tab Options

Using the Home tab is another simple approach to removing formulas; to do the same, follow the procedures below.

  • Select the cell and press Ctrl + C to copy.

Right-Click to Remove Formula in Excel and Keep Values

  • After copying the cells, go to the Home tab >> select Paste.
  • Then, select the first option from Paste Values.

Right-Click to Remove Formula in Excel and Keep Values

  • Finally, you will see that the formula is not shown in the formula bar.

Right-Click to Remove Formula in Excel and Keep Values

Read More: How to Remove Formulas from Entire Excel Workbook


3. Apply Keyboard Shortcut in Excel

You can also apply the keyboard shortcut to remove the formulas. To achieve the same thing, follow the steps below.

  • First of all, press  Ctrl + C to copy the cells after selecting.

Apply Keyboard Shortcut to Remove Formula in Excel and Keep Values

  • To open the dialog box, press  Ctrl + Alt + keys >> Select Values >> Press Enter.

Apply Keyboard Shortcut to Remove Formula in Excel and Keep Values

As a consequence, you’ll receive values that are free of formulas.

Apply Keyboard Shortcut to Remove Formula in Excel and Keep Values


4. Apply Drag to Remove Formula in Excel and Keep Values

Dragging is another option to remove the formulas while keeping the values. Follow the steps below to do so.

  • Firstly, select the cells >> Holding the Right-Click button and drag to another cell.

Apply Drag to Remove Formula in Excel and Keep Values

  • Drag back to the previous position and release the Right-Click >> Click on the Copy Here as Values Only.

Apply Drag to Remove Formula in Excel and Keep Values

Consequently, you will get your desired results.

Apply Drag to Remove Formula in Excel and Keep Values

Read More: How to Remove Automatic Formula in Excel


5. Customize Quick Access Toolbar

To remove formulas, apply the Paste option in the Quick Access Toolbar. Follow the instructions below:

  • From above the Ribbon, click on the Quick Access Toolbar >> Choose the More Commands.

Customize Quick Access Toolbar

  • Choose the All Commands option to display all commands available.

Customize Quick Access Toolbar

  • Scroll down to find the Paste option >> Select and click the Add >> Press Enter.

Customize Quick Access Toolbar

  • Go back to the data set and copy the cells.

Customize Quick Access Toolbar

  • A new icon for the Paste option will appear and click the icon >> Select Paste Values.

Customize Quick Access Toolbar

Therefore, you will get your final result as shown in the image below.

Customize Quick Access Toolbar


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

To sum up, I hope this post has demonstrated how to remove formulas while keeping the values. Examine the practice book and put what you’ve learned into practice. We are willing to pay for programs like these because of your support.

If you have any questions, please do not hesitate to contact us. Please let me know what you think in the comments section below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

2 Comments
  1. What if “copy here as values only” is greyed out?

    • Greetings Emma,
      Let’s us know some info at first.
      1. Can you access the other methods to paste values only?
      2. Can you replace data in a non-blank cell?
      We have gone through every possible solution but couldn’t match the issue exactly. If you kindly share your excel file with us, we will send it back with the solution.
      Email: [email protected]
      Our dedicated ExcelDemy Team is always there to help.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo