How To Remove Formula In Excel (With Examples)

How To Remove Formula In Excel (With Examples)

4 mins read36 Views Comment
Rashmi
Rashmi Karan
Manager - Content
Updated on Dec 30, 2024 20:30 IST

MS Excel formulas are essential for executing various calculations on our data. However, after achieving the required results, replacing a formula with the actual value it calculates is often helpful. This is why you must know how to remove formula in Excel. Doing this can enhance the performance of a workbook, especially when it contains many intricate formulas.

How to Remove Formula in Excel

To learn more about MS Excel, read our blog, What is MS Excel?

Additionally, there are situations when we need to eliminate Excel formulas while retaining their output, mainly when itโ€™s crucial to eliminate cell references and formulas. For example, You have a formula applied in one entire column, and it adds data from other columns. If you delete one reference column, the data vanishes and throws the following error โ€“

So, you must use the formula in your columns and remove it later so you donโ€™t suffer from data loss. In this blog, you will learn how to remove formulas in Excel.

There are two primary and simplest methods to remove a formula in Excel โ€“

Copy and Paste Values

  1. Click on the cell with the formula you want to remove. Drag to select the range to remove formulas from a range of cells.
  1. Right-click and select the specific range.
  2. Select โ€œCopy,โ€ or press Ctrl+C on your keyboard.
  1. Right-click on the selected cell(s) again.
  2. Hover over the โ€œPaste Specialโ€ option.
  3. Click on โ€œValuesโ€ from the list. This action pastes the calculated result back into the same cell(s) without the formula.

You can now see that the formula has been removed while keeping the values intact.

Explore MS Excel courses to sharpen your data analysis and management skills.

Recommended online courses

Best-suited MS Excel courses for you

Learn MS Excel with these high-rated online courses

โ‚น12 K
2 months
โ€“ / โ€“
3 months
โ‚น5.5 K
18 hours
โ‚น60.55 K
10 months
โ‚น3 K
10 hours
โ‚น3.2 K
28 hours
โ‚น1 L
6 months
โ€“ / โ€“
45 hours
โ‚น1.34 L
300 hours

Using Drag and Drop with the Right Mouse Button

  1. Select the cell(s) containing the formulas. 
  1. Place your cursor at the edge of the selection. Drag the selection to an empty spot, preferably the next cell or column, while holding the right mouse button.
  2. While you release the mouse button, a context menu will appear. Choose โ€œCopy Here as Values Only.โ€ 

The values will appear in the selected column without any formula or formatting.

  1. Drag the values back to the original location using the same method if needed.

We will add the missing currency value since we were working on financial data.

Now that you have eliminated the formulas and you have the values, you can safely delete all the data to which they refer. 

Why Remove Excel Formulas?

There are several reasons why you should remove formulas from your Excel spreadsheets while keeping the calculated values:

Performance Improvements

with increasing data inputs, the spreadsheets get more complex and can slow down. By removing unnecessary formulas, you can speed up your workbook. This is especially helpful for large datasets or when sharing files with others who donโ€™t need to see the formulas.

Data Integrity

Keeping data accurate is essential. Formulas mainly depend on other cells, which might change or be deleted. Thus, if a cell that a formula has referred to is removed or altered, it may cause errors and data loss. Also, changing formulas into static values prevents your data from unintended change.

Simplification of data presentation

Sharing spreadsheets with people who do not know how to use Excel is helpful because the data can be shared without showing formulas. This way, the spreadsheet becomes easy to read and understand, allowing others to focus on the results instead of the calculations.

Protection of Sensitive Information

Sometimes, the formula may indicate sensitive information or proprietary calculations when shared. You may remove these formulas and replace them with their results so that confidential information is safeguarded while the insight it provides remains essential.

Avoiding Mistakes

Formulas are prone to mistakes if one is not careful. When you remove these formulas and are confident that the computed values are correct, you will avoid accidental changes that would ruin your analysis or lead you to a wrong conclusion.

How To Lock Cells In Excel?

Conclusion

Removing formulas in Excel is simple and can be done in several ways. The best method for you will depend on your specific needs. If you want to remove the formula and keep the value, replacing it with a value is the quickest and easiest way. However, if you need to move the cellโ€™s contents to another location, moving the content and pasting again as a value is the better option.


FAQs

Why would I want to remove a formula in Excel?

You should remove a formula in Excel for many reasons. For example, you might want to: Clean up your worksheet and make it easier to read and understand; Remove sensitive information from a worksheet you share with others; Protect your formulas from being changed or deleted; Improve the performance of your worksheet by reducing the number of calculations that need to be performed.

How can I protect my formulas from being changed or deleted?

You can protect your formulas from being changed or deleted by locking the cells that contain the formulas. To do this, select the cells containing the formulas and click the Lock Cell button on the Home tab.

Can I remove a formula without keeping the value?

Yes, you can remove a formula without keeping the value. To do this, follow one of the methods above and then delete the cell's contents.

How can I improve the performance of my worksheet by reducing the number of calculations that need to be performed?

You can improve the performance of your worksheet by reducing the number of calculations that need to be performed by removing any unnecessary formulas. You can also use functions such as SUMIF and COUNTIF to reduce the number of calculations required.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio