6 Ways to Remove a Formula from an Excel Cell

The spreadsheet Excel has a huge number of functions that allow you to work effectively with large amounts of information and a variety of calculations. It often happens that the user needs to delete the formula with which the result was calculated, and leave the total in the cell. The article will discuss several methods for removing formulas from Excel spreadsheet cells.

Deleting formulas

The spreadsheet does not have an integrated formula deletion tool. This action can be implemented by other methods. Let’s analyze each in more detail.

Method 1: Copy Values ​​Using Paste Options

The first option is the fastest and easiest. The method allows you to copy the content of the sector and move it to an alternative location, only without formulas. Detailed instructions are as follows:

1. We make a selection of a cell or a range of cells, which we will copy in the future.

2. We press RMB on an arbitrary element of the selected area. A small context menu appears, where you should select the “Copy” item. An alternative copying option is to use the key combination “Ctrl + C”. The third option for copying values ​​is to use the “Copy” button located on the toolbar of the “Home” section.

3. Select the cell in which we want to paste the previously copied information, right-click on it. The familiar context menu opens. We find the “Paste Options” block and click on the “Values” element, which looks like an icon with the image of the sequence of numbers “123”.

4. Ready! The copied information without formulas was transferred to the new selected area.

Method 2: Use Paste Special

There is a “Paste Special” that helps you copy information and paste it into cells while maintaining the original formatting. The inserted information will not contain formulas. Detailed instructions are as follows:

1. We select the range that we want to paste in a specific place and copy it using any method convenient for you.

2. We move to the cell from which we want to start pasting the copied data, right-click on it. A small context menu has opened. We find the element “Paste Special” and click on the arrow icon located to the right of this element. In the additional menu that appears, click on the inscription “Values ​​and source formatting”.

3. Done, task successfully completed!

Method 3: Delete Formulas in the Source Table

Next, let’s talk about how to delete formulas in the original table. Detailed instructions are as follows:

1. We copy a range of cells in any available way. For example, using the key combination “Ctrl + C”.

2. As in the previously discussed method, we paste while maintaining the original formatting into another sector of the worksheet. Without removing the selection, we copy the data again.

3. We move to the sector located in the upper left corner, press RMB. The familiar context menu appears, in which you should select the “Values” element.

4. The filling of cells without formulas was copied to the original place. Now you can delete the rest of the tables that we needed for the copy procedure. Select the duplicates of the table with LMB and click on the selection area with RMB. A context menu appears, in which you should click on the “Delete” element.

5. A small window “Delete cells” was displayed on the screen. Here you can choose what to remove. We put an item near the inscription “Line” and click “OK”. In our example, there are no cells with data on the right side of the selection, so the option “Cells, shifted to the left” is also suitable.

6. Duplicate tables are completely removed from the worksheet. We have implemented the replacement of formulas with specific indicators in the original table.

Method 4: Remove formulas without copying to another location

Some users of the Excel spreadsheet may not be satisfied with the previous method, since it involves a large number of manipulations in which you can get confused. There is another variation of deleting formulas from the original table, but it requires care on the part of the user, since all actions will be carried out in the table itself. It is important to do everything carefully so as not to accidentally remove the necessary values ​​or not to “break” the data structure. Detailed instructions are as follows:

1. Initially, as in the previous methods, we select the area from which it is necessary to delete formulas by any method convenient for you. Next, we copy the values ​​in one of three ways.

2. Without removing the selection, click on the RMB area. The context menu appears. In the “Paste Options” command block, select the “Values” element.

3. Ready! As a result of the performed manipulations in the original table, the formulas were replaced by specific calculation values.

Method 5: Apply a Macro

The next method involves the use of macros. Before you start deleting formulas from the table and replacing them with specific values, you need to enable the “Developer Mode”. Initially, this mode is disabled in the spreadsheet processor. Detailed instructions for enabling “Developer Mode”:

1. Click on the “File” tab, which is located at the top of the program interface.

2. A new window has opened, in which in the left list of elements you need to go down to the very bottom and click “Parameters”.

3. Settings are displayed on the right side. Find the “Customize Ribbon” section and click on it. There are two list boxes. In the right list we find the item “Developer” and put a tick next to it. After completing all the manipulations, click “OK”.

4. Ready! Developer mode is enabled.

Detailed instructions for using the macro:

1. We move to the “Developer” tab, which is located at the top of the spreadsheet interface. Next, find the “Code” parameter group and select the “Visual Basic” element.

2. Select the desired sheet of the document, and then click on the “View Code” element. You can do the same operation by double-clicking on the desired sheet. After this action, the macro editor appears on the screen. Paste the following code into the editor field:

Sub Delete_formulas()

Selection.Value = Selection.Value

End Sub

3. After entering the code, click on the cross in the upper right corner of the screen.
4. We make a selection of the range in which the formulas are located. Next, go to the “Developer” section, find the “Code” command block and click on the “Macros” element.

5. A small window called “Macro” appeared. Select the newly created macro and click Run.

6. Ready! All formulas in cells have been replaced with calculation results.

Method 6: remove the formula along with the calculation result

It happens that the user of the Excel spreadsheet processor needs not only to implement the deletion of formulas, but also to delete the results of calculations. Detailed instructions are as follows:

1. As in all previous methods, we begin our work by selecting the range in which the formulas are located. Then right-click on the selection area. A context menu appears on the screen. Find the item “Clear content” and click on it. An alternative deletion option is to press the “Delete” key.

2. As a result of the manipulations, all data in the selected cells was deleted.

Deleting a formula while keeping the results

Let’s consider in detail how to delete a formula while saving the result. This method involves using the Paste Values ​​property. Detailed instructions are as follows:

1. Select the cell or range where the formula we need is located. If it is an array formula, then you should first select all cells in the range that contain the array formula.
2. Click on a cell in an array formula.
3. Go to the “Home” section and find the “Editing” tool block. Here we click on the “Find and select” element, and then on the “Go” button.
4. In the next window, click on “Additional”, and then on the element “Current Array”.
5. We return to the “Home” section, find the “Copy” element and click on it.
6. After carrying out the copying process, click on the arrow, which is located under the “Paste” button. At the last step, click “Insert Values”.

Deleting an array formula

To perform the procedure for deleting an array formula, you first need to make sure that all cells in the range containing the desired formula are selected. Detailed instructions are as follows:

1. Select the desired sector in the array formula.
2. We move to the “Home” section. We find the block of tools “Editing” and click on the element “Find and select”.
3. Next, click “Go”, and then on the element “Additional”.
4. Click on “Current Array”.
5. At the end of the procedure, click “Delete”.

Conclusion

Summing up, we can say that there is nothing complicated in deleting formulas from spreadsheet cells. There are a huge number of removal methods, so that everyone can choose the most convenient for themselves.