Formulae are a great feature to have in MS Excel, but at times we don’t need the constructs of the formula within the cells to be retained, instead only their results! What shall one do on occasions like these? Is there a way around it?
We shall explore just that in this article using the following dataset as an example.
Also read: How to Copy Formula in MS Excel?
Look a bit closer and one shall observe that there seems to be a formula within the cells in column D as indicated in the below image.
Luckily there seems to be more than one technique to remove these formulae & retain only the end result. We shall demonstrate each of them in this article.
- Method I – Using the Home Tab
- Method II – The Accelerator Technique
- Method III – Using Keyboard Shortcut
Method I – Using the Home Tab
In this method, we shall make use of the options readily available in the Home tab. The first thing one has to do would be to select all the cells containing the formula by a left click on the cell D25 & dragging all the way down to D32 as shown below.
Hover the cursor a bit top towards the left & there would be a Clipboard section with a list of options.
Choose the Copy option from the Clipboard section & the moment it is hit the selected range becomes highlighted as shown below.
Following this, click on the Paste dropdown on the Clipboard section & a list of options shall appear as shown below.
Select the Paste Values option from the dropdown & the formulae will be cleared from all the cells retaining only the values as indicated below.
Method II – The Accelerator Technique
Another way to get things done is using the infamous accelerator technique. It is in fact a faster means to clear out all the formula contents from the selected cells. The very first step of selecting the cells remains the same here too.
Once done, right click & choose Copy for copying all the cells in the selected range.
Now one shall press the following keys on the keyboard in the same sequence.
ALT – E – S – V
This shall make the Paste Special dialogue box appear as shown below.
Hit ENTER & the formulae within the selected range will be removed at once.
Method III – Using Keyboard Shortcut
A good old combo of copy & paste shortcuts is all that is needed for removing the formulae in this technique. Once the cells containing the formula are selected hit CTRL+C to copy them.
This shall then be followed by pressing CTRL+V without clicking anywhere in the spreadsheet. This shall result in an icon appearing at the end of the range as indicated below.
One might click on this icon or hit the CTRL key to have a look at what it has to offer. A drop down with a wide range of variances to exercise the paste option appears.
Choose the Paste Values option from this list. The name itself explains its functionality which is to remove whatever formatting there is in the selected cells & retain the values alone. This also includes the removal of the formula.
The very moment the option is chosen all the formulae in the selected range are gone and are gone for good!
Conclusion
That brings us to the end of this article & here’s one which details how to add hours in MS Excel. There are numerous other articles too in QuickExcel that can come in handy for those who are in looking to level up their skills in MS Excel. Ciao!