Microsoft Excel is one of the world’s most used spreadsheet programs. This is because of its user-friendly interface that both beginners and experts can use with great ease.
It comes pre-installed with the Microsoft Office bundle on every PC these days. It’s fairly simple to use, and after a few hours with it, you will know your way around it.
It is mostly used for data entry and anything related to using numbers and formulas.
The program comes with a set of formulas that help the user perform basic as well as complex mathematical calculations.
But then a question arises – what if you wish to remove the formula from Excel?
How to remove the formula from Excel?
To remove formulas from Excel but leave the data untouched, there are a few ways to do it. The first is the copy-paste method. Begin by selecting all the cells you wish to remove the formula from.
- 1Right-click, then select copy. Alternatively, you can use the keyboard shortcut of Ctrl + C.
- 2Once you have the selected cells copied, now you have to paste it.
- 3Right-click either on the selected cells or on any blank cell.
- 4When you right-click to paste, you will notice you are now getting paste options.
- 5Among the options, you will notice one says Paste values.
- 6Click on it, and now only the values will be pasted in the selected cells.
- 7You can click on the cell, and you will notice that only the formula bar’s values appear.
- 8You will not be able to see the formula now.
The simpler copy-paste method is for those who are not used to using keyboard shortcuts or are new to Excel.
- 1Click and drag over the cells you wish to remove the formula from.
- 2Once you have the cells selected, move your mouse pointer to the copy button under the Home tab.
- 3Click on Copy to copy the selected cells.
- 4Click and drag a new selection of cells where you would like to paste the values. Ignore this step if you want to paste the values on the already selected cells.
- 5Beside the Copy button, you will see the Paste button.
- 6Click on the Paste button, and you will notice some options.
- 7You can click on the Paste Values option to paste the selected cells’ values without the formula.
- 8Alternatively, you can also click on the Paste Special option.
- 9This will bring up a new dialog box of options.
- 10From here under the Paste options, click on Values.
- 11Click Ok to continue.
Once you click ‘Ok’ the values will be pasted without the formula. To bring up the Paste Special dialogue box, you can easily use the keyboard shortcut of Ctrl + Alt + V. A simpler alternative way to do so is:
- 1Click and drag over the cells you wish to remove the formula from.
- 2Once you have the cells selected, bring your mouse pointer to the right edge of the cells.
- 3The mouse pointer should change. You will notice four arrows pointing in different directions above your mouse pointer.
- 4Hold the right key of your mouse, click and drag your selected cells to the right and then bring it back.
- 5Let go of your mouse’s right key, and you will notice a few options pop up.
- 6One of the options is Copy Here As Values Only.
- 7When you click on this option, it will remove the selected cells’ formulas and only leave the values visible.
How to Remove Formula without Deleting Data
Removing Formula from multiple data sheets
The steps, as mentioned above are for removing formulas from one single sheet. But what if you have multiple sheets of data from which you want to remove the formulas? There is a simple process to accomplish this. Begin with selecting the sheets you want to change:
- 1To select multiple sheets, hold the Shift key while clicking on the sheets to select them together. (Alternatively, you can bring the mouse pointer to the sheet name and right-click. You will see an option called Select All Sheets.)
- 2If you have multiple sheets open, you can easily hold down the Shift key and click on the last sheet. This will select all the sheets in-between the first and last ones.
- 3The selected sheet tabs will turn to a whiter hue to let you know they are selected.
- 4Once you have your sheets selected, go back to sheet 1.
- 5You will have to select the cells that are in use or just select the entire sheet by pressing Ctrl + A.
- 6Once the whole sheet is selected, press Ctrl + C or right-click and select Copy.
- 7Right-click again and bring your mouse pointer to Paste Special.
- 8Click on Paste Values.
Since all the sheets have been grouped, this will be applied to all of them. Excel will copy the values from sheet 1 and paste only the values on sheet 1. Then it will copy the values from sheet 2 and paste only the values on sheet 2. This process will happen to all the selected sheets.
Once the process is completed on all the sheets, you will be left with just the values and no formulas on any of the selected sheets.
Warning: Once you have copy-pasted the information and it has affected all the sheets, make sure to ungroup them. To ungroup sheet: Right-click on any one sheet and select the Ungroup Sheet option.
Why would you need to remove formulas?
So, why would you ever need to remove formulas from an Excel sheet? There are two reasons why:
If you share your Excel sheets with people, you might not want them to know how you came to a certain figure. If you use the formulas given in Excel, it is hard to hide. Even if you use custom formulas, it is hard to find.
When you use a formula to get a certain value, that formula will appear in the formula bar for all to see. There have been multiple stories of confidentiality leaks because formulas were not removed from Excel sheets before they were sent out.
Profit margins and discounts can all be calculated using Excel formulas, and if these formulas are not removed in the final datasheet, then competitors can easily get the upper hand. This is also used for training purposes. Many companies train their employees by using Excel.
They use data tasks like finding how the calculations were made to get to certain values. They remove the formulas from their datasheets, which makes it harder for the employee to know how it was done.
