Buy Now

How To Remove Formula in Excel (While Keeping the Data)

copy paste exceltips formulas keyboard shortcuts Apr 07, 2022
Make sure that your spreadsheets are always optimized for speed and efficiency by removing formulas in Excel. Learn how in this blogpost.

Microsoft Excel provides the ability to perform various calculations, including analytical ones.

If you want to calculate without complexity or catastrophic errors, a feature lets you create your Excel formulas or use the program's built-in functions. It is often necessary though to keep the final result in a "stable" form.

Working with Excel formulas allow you to facilitate and automate various calculations.

If you change the values ​​in the linked cells, the total values will change, which is unnecessary in some cases, and detrimental in most.

In addition, be aware that when you move the copied cells with formulas to another place, you may lose the values ​​you already have.

The goal of removing formulas in Excel may be to reduce the file size or if (for some reason) you want to hide how the work got done so you don't want other people to see how the calculations are done in the worksheet. Removing formulas from your workbook also frees up usable computer processing power which is always nice whenever you're working with spreadsheets that are probably too large for Excel to handle in the first place. In these cases, it might be in your favor to remove the formula.

Generally, when you're creating reports, it's always nice to keep only the values if the only use-case for it is to read the sheet at face value.

For example, in these cases, you may need to remove the formulas from the worksheet and keep only the calculated results in the cells. See Delete or remove formulas in excel

Let's see how you can remove formulas in your spreadsheet, keeping only the final value in the workbook by using the following methods. You can also apply this on multiple sheets by these grouping feature below:

Remove the Formulas in Excel and Keep the Values to Get Only the Final Value.

This is probably the simplest way of removing formulas in Excel. All you have to know is how to copy and paste values in cells.

You will find in this workbook an example that we apply; this sheet shows the total amounts generated by sellers in several different regions and the previous balance already held by the sellers.

In the sheet;

Column (A) is the name of the seller,

Column (B) is the seller's region,

Column (C) is the sales amounts that sellers get,

Column (D) represents the previous balance already with sellers, and Column (E) is the total value (the sales + the previous balance,)

i.e. its value represents the sum of column (c) and column (d).

Copy and Paste Values via Paste Options

If you left-click inside the cell, you will see the formula used in the file, which we want to remove and keep only the final value. See the screenshot below to remove formula.

By this method, you can copy the data without a formula to another area using the paste options in the sheets. #remove formula.

Step1:

Select values or cells, which we select by cursor while sheet hold the left mouse button.

Step 2:

On the Home tab, select copy icon at the top of the page on the ribbon in the Clipboard group, or use the keyboard shortcut and press ctrl c.

You will see the dotted frame around the selected cells, as shown below on the red arrow.

Step 3:

Select the cell that will be the upper-left cell of the inserted table. Then, press the right click on the mouse.

Then, the dialog box will appear. In the Paste Options block, Click paste values option. As shown below.

After completing this method, the range is inserted, but only as values ​​without all the formula in the sheets, effectively removing your formulas.

Warning:

Applying this method will lose the original format of your sheets. Therefore, you will have to format the table in multiple sheets and save it manually. See How to Remove Table Formatting in Excel

Remove Formulas in Excel via Paste Special - Values Option

In this method, you can keep the values only and remove the formulas in excel by following these same steps:

Step One:

Select all cells in the column you want to delete the formulas from.

Step Two:

Press ctrl c to copy or press the right click of the mouse and select copy.

You will see the dotted frame around the highlighted cells, as shown below—# excel formula.

 

Step Three:

In this step, you can use two methods to complete the step on all the sheets as follows:

First method:

you can press the right-click and select the paste special dialog box. See the screenshot below.

Second Method:

On the Home tab, click the Paste icon at the top of the page on the ribbon in the Clipboard. As shown. #remove formula.

Step Four:

On the Home tab, select Paste Special to paste values of all the cells, or you can use the keyboard shortcut and press ctrl alt v.

The Paste Special dialog box will appear.

Step Five:

Select only "Values" to remove the formula.

Step Six:

Click OK to save this process, and remove formulas in excel spreadsheet.

Now, if you press into the cells, you will find all the formula in excel has been removed. #remove formulas in excel.

And as you see in the screenshot below, removing formulas will find the value only in the bar. remove the formula in excel

Removing Formulas in Excel in Two Seconds

Similar to the method stated above, there is a technique that uses the same principle, but what makes it better is that it shortens the process by the speed of light!

Go ahead and select the column which you want to remove formulas from. Since we're at the topic of speed, you can use the keyboard shortcut Ctrl + Shift + Down after selecting the first cell in the column.

After that, point your cursor on the border of the range until you see the move cursor. Click and hold your right mouse button or the right key and hover the move guide over another column then back again to the original.

Once you do that, a popup menu will appear which shows your paste options. You can go ahead and select Copy Here as Values Only and it will remove the formulas while keeping the data.

Do note that this method is fast, and all things that are done fast can be prone to errors, so make sure to double check the selected cells so all the cells that you will remove formulas from is correct.

The Wrap Up

In the above scenarios, it is easy to remove formulas in excel and keep your data or your values only.

You can now create reports quickly without

You can apply the steps on this single sheet or other sheets to remove formula.

The grouped sheets that you will generate will benefit from improving your Excel skills. You can also use the ready-to-use templates from Simple Sheets.

It will enable you to make your work faster, more accessible, and more productive. See The Benefits of Excel Templates

 

 

 

 

 

 

81% of companies across the use Microsoft Excel. 

Mastering Excel can increase your earnings up to 4x over the course of your career!

Bottom Line: It pays to master Excel. Take our world-class Excel University and take your spreadsheet skills to the next level.

Buy Now

Get 5 FREE Excel Templates and Trainings

What email do you want us to send your access to?

We hate SPAM. We will never sell your information, for any reason.