Remove formulas in Excel

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”

Using mouse

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.

In visible cells, skipping hidden or filtered

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.

Sub remFormulas()
    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
        End If
    Next cell
End Sub

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:

Sub deleteFormulasFromSheet()
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub

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:

Sub RemoveFromulasFromActiveWorkbook()
Dim w As Worksheet
For Each w In Worksheets
  With w.UsedRange
    .Value = .Value
  End With
Next w
End Sub

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.