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, you just don’t want to have the recipients see the formula, or you need to delete some sensitive information (POPIA/ GDPR) but don’t want to break the spreadsheet. 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.
Table of contents
YouTube- Delete formula in Excel without deleting values
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.

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.
So for your own sanity, if you want to delete formula in excel without deleting data make sure it is not on the original.
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.

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.

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

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

So now you have removed all the formulas from Excel and left only values.
Want to learn more about Microsoft Excel? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.