- Deleting formulas while saving the result of their calculations
- With VBA or !SEMTools add-in for Excel
- Replacing formulas with their text (without saving the resulting values)
You can delete formulas in Excel in a variety of ways, depending on the actual result you want to achieve.
First you should decide what values to save in the cells, as there are two options:
- it’s either the result of computing these formulas
- or the text of the formulas
In addition to the question of what to leave in the cells after deleting the formulas, there may be other introductory ones, such as:
where to remove formulas:
- in a range,
- whole spreadsheet
- or the whole book
there are also 2 options for hidden and filtered lines
- skipping hidden or filtered lines
- removing formulas from them as well
This guide will provide you with answers.
Deleting formulas while saving the result of their calculations
When there are no barriers in the form of filters and you just need to leave the resulting values, everything is quite simple. It’ll be enough to:
- select range (cell / row / column / whole sheet)
- copy selection
- insert them “as values”
Not the fastest, because only one hand works, but the most convenient and understandable way. Select, copy, and in the context menu, called by right-clicking on the selected range, you need to select this command (highlighted in red):
Using only the keyboard
When copying is done to a neighboring column or to another book, I prefer this method, although it saves only seconds.
Step by step:
1. select the range
if it is a column, row or sheet
Ctrl-Shift-Down/Up/Right/Left and Shift-Right/Left hotkeys will help you select your cell range
2. Copy it with Ctrl+C
3. Ctrl+Down/Up/Right/Left or just Down/Up/Right/Left to move to the range where you want to insert data and / or Ctrl+Tab to go to another book
4. call the context menu with a key on the keyboard, sometimes it is not there, but usually it is next to the right ALT button
5. Navigate to the “paste as values” command (Down-Down-Right)
6. Enter key
It may seem that there are as many as 6 steps and this is a long time, but try to get used to and compare the work on large ranges of cells – the mouse will be exactly longer.
With VBA or !SEMTools add-in for Excel
I implemented quick replacement of formulas with values in the selected range in my !SEMTools for Excel add-in. For those who are not ready to understand VBA, after installing !SEMTools, the launch of the procedure is available in 2 clicks from the corresponding menu in the “DELETE” group.
Here, when using standard solutions, problems may already arise. The “as values” insertion procedure will fail and damage the data if there are filtered rows in the range being copied.
Dim cell As Range
For Each cell In Selection
If Not (cell.EntireRow.Hidden = True Or cell.EntireColumn.Hidden = True) Then
cell.Value2 = cell.Value2
But the above code will work just fine, like the same add-in procedure. The code syntax contains checking cells for whether they are hidden. Same can be done with free version of !SEMTools add-in for Excel. It simply skips hidden rows and deletes formulas only from visible ones. Look at the example:
Delete formulas on the whole worksheet
When you need to quickly get rid of all the formulas on a worksheet or in an entire book, the corresponding simple procedures written in VBA can also help:
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
You can use this code without embedding it in your files by installing !SEMTools add-in.
Delete all formulas in current workbook
When it comes to removing formulas from an entire workbook, automation is essential. Otherwise, you would have to go to each sheet and perform the copy and paste as values procedure as many times as there are sheets in the workbook.
Here is the procedure that will do it for you:
Dim w As Worksheet
For Each w In Worksheets
.Value = .Value
Replacing formulas with their text (without saving the resulting values)
Sometimes it is required for demonstration purposes to show the text of the formulas. In such cases, Excel Find and Replace procedure will be useful.
Select the range of formulas, press Ctrl+H to start the search and replace dialog box, and drive in the corresponding windows.
The apostrophe as a result of the replacement will not be visible and they will look identical to their spelling, but the formulas will be perceived as text. You might need to create a copy of initial range in order not to lose calculated values.