How to Remove Formulas In Excel

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

Apr 07, 2022

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. Keeping the final result in a “stable” form is often necessary.

Working with Excel formulas allows 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, 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, removing the formula might be in your favor.

Generally, when creating reports, it’s always lovely 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 using the following methods. You can also apply this on multiple sheets by the 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 must 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 columns (c) and (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 the formula.

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

Step 1:

Select values or cells, which we select by cursor while the sheet holds 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 the paste values option. As shown below.

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

Warning:

Applying this method will lose the original format of your sheets. Therefore, you must 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.

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 an 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 on 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 correct key, hover the move guide over another column, and then return to the original.

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

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

The Wrap Up

In the above scenarios, removing formulas in excel and keeping your data or your values is easy.

You can now create reports quickly without

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

The grouped sheets you 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

Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons

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