How to remove formulas from Excel cells

There are many ways to remove formulas in Excel, depending on what you’re trying to achieve. Most of the time, you want to keep some kind of value in the cells. There are two main options:

• Keep the values calculated by the formulas
• Keep the formula text itself

Besides choosing what to keep in the cells, there are a few other things to consider:

• Where do you want to remove formulas from?
  • A specific range
  • The entire sheet
  • The whole workbook
• Should formulas in hidden or filtered rows also be removed?

This article answers all of those questions.

Convert Formulas to Values in a Selected Range

The most common task is to keep the result of a formula in a cell but remove the formula itself. Excel treats this result as the cell’s value.

To convert formulas to values:

1. Select the desired range (or a cell, row, column, or the entire sheet).
2. Copy the selected content.
3. Use the “Paste as values” option to insert the result.

You can do this using your mouse or keyboard — the process is the same.

Using the Mouse

This isn’t the fastest method (since you’re using one hand), but it’s intuitive. Select the range, copy it, then right-click and choose the “Paste Values” option from the context menu:

Using Keyboard Shortcuts

Copying formulas into an adjacent column or another workbook using shortcuts can be faster. Here’s how:

1. Select your range:
   • Using the mouse — for full columns, rows, or the entire sheet
   • Using Ctrl + Shift + Arrow or Shift + Arrow — for cells or cell ranges
2. Copy with Ctrl + C
3. Use the arrow keys to move to the destination or Ctrl + Tab to switch to another workbook
4. Press the Context Menu key (often near the right Alt) to open the menu
5. Navigate using arrows (Down → Down → Right)
6. Press Enter

While this might seem like a lot of steps, with practice it’s faster than using the mouse — especially on large ranges.

Using the !SEMTools Add-In

The free procedure in the !SEMTools add-in simplifies this task by skipping the need to copy and paste. Just select the range, click a couple of buttons — and the formulas are removed!

Convert Formulas to Text (Without Keeping Their Results)

Sometimes, you may want to display formulas as text — for teaching or demonstration purposes. In this case, the Find and Replace method works well.

Select your range, press Ctrl + H to open the “Find and Replace” dialog, and input the required values

The apostrophe won’t be visible, but formulas will appear unchanged and be treated as text by Excel:

In Visible Cells Only (Skip Filtered Rows)

Here, standard paste-as-values methods may fail and damage your data if the range contains filtered-out rows.

This VBA macro solves the problem:

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

This code works perfectly — just like the corresponding !SEMTools procedure. It ensures formulas are only removed from visible cells.

Remove All Formulas from a Worksheet

If you need to remove all formulas from a sheet at once, just select the entire sheet and paste as values — or use this VBA macro:

Sub RemoveAllFormulasFromSheet()
  ActiveSheet.UsedRange.Value2 = ActiveSheet.UsedRange.Value2
End Sub

Remove All Formulas in a Workbook

To remove formulas from the entire workbook, automation is essential — otherwise you’d need to repeat the process on every sheet.

This macro handles the task:

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

You can use these macros without adding them manually — just connect the !SEMTools add-in.

Removing Formulas with !SEMTools

With the !SEMTools add-in, you can remove formulas from any range instantly — no copying or pasting required, and no VBA implementation, either.

Three options for deletion are available:

1. In selection
2. On active sheet
3. In active workbook

First one skips filtered or hidden cells, so if you want to delete formulas in hidden cells of selected range, you’ll need to unhide them first.

After installing !SEMTools, simply use the relevant procedure from the “DELETE” tab. This feature is part of the free toolkit — available with no limits in any version.

This post is also available in RU.