Contents
Excel has a huge number of functions that can be used to carry out even the most complex calculations. They are used in the form of formulas written into cells. The user always has the opportunity to edit them, replace some functions or values.
As a rule, storing a formula in a cell is convenient, but not always. In some situations, it becomes necessary to save a document without formulas. For example, in order to prevent other users from understanding how certain numbers were obtained.
I must say that this task is absolutely simple. It is enough to follow a few simple steps to bring it to life: At the same time, there are several methods, each of which is more convenient to apply in a particular situation. Let’s look at them in more detail.
Method 1: Using Paste Options
This method is the easiest, even a beginner can use it. You just need to follow these steps:
- First you need to make a left mouse click and by dragging select the cells in which the task is to delete the formulas. Well, or one. Then just one click is enough.
1 - Then you should open the context menu and find the “Copy” item. But more often the combination Ctrl + C is used to achieve this goal. This is much more convenient and faster than specifically right-clicking on the required range, and then clicking on another item. This is especially useful on laptops, where a touchpad is used instead of a mouse.
2 - There is also a third copying method, which, for convenience, is exactly in the middle between the two above. To do this, find the “Home” tab, and then click on the button highlighted in red square.
3 - Next, we determine the cell where the data to be copied from the source table should begin (they will be located in the upper left part of the future range). After that, we right-click and click on the option indicated by the red square (the button looks like an icon with numbers).
4 - As a result, a similar table will appear in the new location, only without formulas.
5
Method 2: Apply Special Paste
The disadvantage of the previous method is that it does not preserve the original formatting. To lose this minus, you need to use another option with a similar name – “Paste Special”. It is done like this:
- Again, select the range that we need to copy. Let’s use the copy button on the toolbar in this case. The entire table will already be used as a range, since its headers contain complex formatting that we need to copy.
6 - The next steps are similar. You need to go to the cell in which the table without formulas will be located. Or rather, in the upper left cell, so you need to make sure that there are no extra values in place of the future table. Right-click on it and find the “Paste Special” option. Next to it there is a triangle icon, which is directed to the right with its top. If you click on it, another panel will appear, where we need to find the “Insert Values” group and select the button highlighted in red in this screenshot.
7 - The result is the same table that is in the originally copied fragment, only instead of the formula, the values uXNUMXbuXNUMXbare already listed there.
8
Method 3: Delete the Formula in the Source Cell
The disadvantage of both methods above is that they do not provide the ability to get rid of the formula directly in the cell. And if you need to make a small correction, you will have to copy, paste with certain parameters elsewhere, and then transfer this table or individual cells to their original position. Obviously, this is terribly inconvenient.
Therefore, let’s take a closer look at the method that allows you to delete formulas directly in cells. Follow these steps:
- Copy the required range using any of the above methods. For clarity, we will do the right mouse click and select the “Copy” option there.
9 - Similar to the previous method, we need to paste the area we copied earlier to a new location. And at the same time leave the original formatting. Next, we need to paste this table below.
10 - After that, we go to the top left cell of the table that was originally (or select the same range that was in step 1), after which we call the context menu and select the “Values” insert.
11 - After it turned out to completely copy the desired cells without saving the formulas, but with the same values, you need to delete the duplicate. To do this, you need to select the range of data that you want to get rid of, then right-click on it and click on the “Delete” item.
12 - Next, a small window will appear in which you should select the “line” item and confirm the deletion by pressing the “OK” button.
13 - You can also choose another item. For example, “cells, shifted left” is used to remove a certain number of cells that are on the left side, provided that no values are specified on the right side.
Everything, now we have the same table, only without formulas. This method is a bit like copying and pasting the table obtained by the second method to its original location, but somewhat more convenient compared to it.
Method 4: Avoid copying to another location at all
What actions to take if there is no desire to copy the table to another place at all? This is a rather difficult method. Its main disadvantage is that errors can significantly corrupt the original data. Of course, you can restore them using the Ctrl + Z combination, but redoing them in which case will be more difficult. Actually, the method itself is as follows:
- We select the cell or range that we need to clear from the formulas, and then copy them using any of the above methods. You can choose the one you like best. We will use the method that involves using the button on the toolbar in the Home tab.
14 - We do not remove the selection from the copied area, and at the same time we right-click on it, and then select the “Values” item in the “Paste Options” group.
15 - As a result, specific values are automatically inserted into the correct cells.
16 - If there was some formatting in the cell, then you need to use the “Paste Special” option.
Method 5: Using a Macro
A macro is a small program that performs certain actions in a document for the user. It is needed if you often have to perform the same type of actions. But you won’t be able to use macros right away, because the developer mode is not enabled by default, which must be activated before you can directly delete the formulas.
To do this, perform the following sequence of actions:
- Click on “File”.
17 - A window will appear in which in the menu located on the left, we are looking for the item “Options” and select it.
18 - There will be an item “Customize the Ribbon”, and on the right of the window you need to check the box next to the item “Developer”.
19
To write a macro, follow these steps:
- Open the “Developer” tab, where go to the Visual Basic editor by clicking the button of the same name.
20 - Next, we need to select the correct sheet, and then click on the “View Code” button. An easier option is to click twice in a row quickly with the left mouse button on the desired sheet. This will open the macro editor.
21
Then such code is inserted into the editor field.
Sub Delete_formulas()
Selection.Value = Selection.Value
End Sub
Such a small number of lines turned out to be quite enough to remove the formulas in the selected range. Then you need to select the area that we need and click on the “Macros” button. It can be found next to the Visual Basic Editor. A window for selecting saved subroutines appears, in which you need to find the desired script and click “Run”.
After clicking on this button, each formula will be automatically replaced by the result. It just seems difficult. In fact, these steps only take a few minutes. The advantage of this approach is that you can create a more complex program that, for example, will determine for itself which cells to remove the formula based on certain criteria. But this is already aerobatics.
Method 6: Remove both the formula and the result
Almost every person sooner or later has to delete not only the formula, but also the result. Well, that is, so that nothing is left in the cell at all. To do this, select those cells in which you want to clean up, right-click on them and select “Clear contents”.
Well, or just use the backspace or del key on the keyboard. In simple words, this is done in the same way as clearing the data in any other cell.
After that, all data will be erased.
Conclusions
As you can see, it is quite easy to remove formulas from cells. The good thing is that there are several methods to achieve this goal. A person has the right to choose any one that suits him best due to, for example, convenience. For example, methods with duplication are useful if you need to quickly roll back changes or redo the result so that the original information is preserved. This can be very useful, for example, if you need to make sure that one sheet contains formulas, and the other contains only values without the ability to edit formulas.