How to delete formula in excel without deleting data

You have created a spreadsheet with multiple sheets and now you need to send it out, but you want to remove all the formula, either because of size or you just don’t want to have the recipients see the formula. This post will show you how to delete formula in excel without deleting data, on one sheet and on multiple sheets at the same time.

So below we have a spreadsheet with the formula shown (note that in cell G12 there is a formula).

  • Firstly we want to remove all the formulas in this sheet, but leave the data behind, and
  • Secondly we want to do the same thing across the four sheets.

How to delete formula in excel without deleting data

Before you do this- SAVE A COPY

Before you do anything, save a new version. You have probably spent hours building all the formulas. Although you don’t want other people to see them, do you really want to have to recreate them? It is very easy to accidently remove all the formula and then overwrite the original. Start by saving a new version and call it something like VALUES ONLY.

Remove the formulas within a sheet (but leave the data)

All you need to do is

  • highlight the area (or the whole sheet)
  • Copy it (CTRL + C or whatever way you use to Copy cells)
  • Click on Paste Special (you don’t have to choose where it will go- it will overwrite what it copied)
  • Choose the paste as Values icon.

How to delete formula in excel without deleting data

When you look at the cells again you will see that the cell that previously had a formula (cell G12 below) now only shows the result.

How to delete formula in excel without deleting data

Across many sheets (at the same time)

The above is useful for a single sheet. But what if you have 20 different sheets. You can either do each sheet individually or else do everything in one go.

The first step is to tell Excel that you want it to do the same thing to all the sheets you highlight. To do this,

  • Click on the first sheet
  • Hold your SHIFT button down
  • Click on the last sheet

How to delete formula in excel without deleting data

You will notice that the colour of the sheet names turns whiter and the name of the file includes the word GROUP. This sheets are now grouped so whatever you do to one, happens to all

WARNING: When you do this it is critical that you switch it off as soon as you are finished

Now follow the same steps as before i.e.

  • highlight the area (or the whole sheet) in the sheet you are (don’t worry about the other sheets)
  • Copy it (CTRL + C or whatever way you use to Copy cells)
  • Click on Paste Special
  • Choose the paste as Values icon.

Because the sheets are grouped, it will do the exact same thing on all the other sheets i.e.

  • It will copy sheet 1 and paste as values onto sheet 1, then
  • It will copy sheet 2 and paste as values onto sheet 2, etc

Be careful not to click into any individual cell and make a change. When you click on a cell, it assumes that you want to take the contents of the cell and do something with it, but if you click in the cell, it assumes you want all the grouped cells to assume the same contents.

You will see now that all the sheets only have numbers in them, no formula.

Immediately switch off the grouping feature by right clicking on any one of the sheets and clicking UNGROUP sheets

How to delete formula in excel without deleting data

So now you have removed all the formulas from Excel and left only values.