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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Quick Ways to Remove Formula in Excel and Keep Values
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.
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.
Steps:
- Firstly, select the cells.
- Press Ctrl + C to copy.
- Then, click the Right-Click button on your mouse.
- Finally, select the Paste Values.
- Therefore, we will see in the following cells that the formulas are removed from the formula bar but the values remained.
Read More: VBA to Remove Formulas in Excel Keeping Values and Formatting
2. Use Home Tab Options
Using the Home tab is another simple approach to remove formulas; to do the same, follow the procedures below.
Step 1:
- Select the cell and press Ctrl + C to copy.
Step 2:
- After copying the cells, go to the Home tab and select the Paste.
- Then, select the first option from the Paste Values.
- Finally, you will see that formula is not shown in the formula bar.
Read More: How to Convert Formulas to Values in Excel (8 Quick Methods)
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.
Step 1:
- First of all, press Ctrl + C to copy the cells after selecting.
Step 2:
- For opening the dialog box, press Ctrl + Alt + V
- Select the Values
- Then, press Enter.
- As a consequence, you’ll receive values that are free of formulas.
Similar Readings
- How to Remove Hidden Formulas in Excel (5 Quick Methods)
- Remove Formula When Filtered in Excel (3 Ways)
- How to Remove Automatic Formula in Excel (5 Methods)
- Convert Formula to Value in Multiple Cells in Excel (5 Effective Ways)
4. Apply Drag to Remove Formula in Excel and Keep Values
Dragging is another option to remove the formulas with keeping the values. Follow the steps below to do so.
Step 1:
- Firstly, select the cells and
- Holding the Right-Click button drag to another cell.
Step 2:
- Drag back to the previous position and release the Right-Click.
- Click on the Copy Here as Values Only.
- Consequently, you will get your desired results.
Read More: Putting Result of a Formula in Another Cell in Excel (4 Common Cases)
5. Customize Quick Access Toolbar
To remove formulas, apply the Paste option in the Quick Access Toolbar. Follow the instructions below to add the Quick Access Toolbar.
Step 1:
- From above the Ribbon, click on the Quick Access Toolbar.
- Choose the More Commands.
Step 2:
- Choose the All Commands option to display all commands available.
Step 3:
- Scroll down to find the Paste
- Select and click the Add
- Then, press Enter.
Step 4:
- Go back to the data set and copy the cells.
Step 5:
- A new icon for the Paste option will appear and click the icon.
- Finally, select the Paste Values
- Therefore, you will get your final result as shown in the image below.
Read More: How to Return Value of Cell Not Formula in Excel (3 Easy Methods)
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.
Experts from the ExcelDemy Team will respond to your questions as soon as feasible.
Related Articles
- How to Stop Formula to Convert into Value Automatically in Excel
- Excel VBA: Convert Formula to Value Automatically (2 Easy Methods)
- How to Convert Formula Result to Text String in Excel (7 Easy Ways)
- Convert Formula to Value Without Paste Special in Excel (5 Easy Methods)
- How to Convert Formula to Value Automatically in Excel (6 Effective Ways)
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.