Ways to Clear Formulae in MS Excel

Clearing Formulae in MS

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?

Sample Data
Sample Data with Formula

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.

Locating Formulae
Locating the Formula within Cells

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.

Selecting the Cells with Formula
Selecting the Cells with Formula

Hover the cursor a bit top towards the left & there would be a Clipboard section with a list of options.

Locating Clipboard
Locating the Clipboard Section

Choose the Copy option from the Clipboard section & the moment it is hit the selected range becomes highlighted as shown below.

highlighting copied cells
Copied Cells Highlighted!

Following this, click on the Paste dropdown on the Clipboard section & a list of options shall appear as shown below.

Selecting Paste Values
Selecting Paste Values Option

Select the Paste Values option from the dropdown & the formulae will be cleared from all the cells retaining only the values as indicated below.

Cells with Formulae Removed
Cells with Formulae Removed!

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.

Selecting the Cells with Formula 1
Selecting the Cells with Formula

Once done, right click & choose Copy for copying all the cells in the selected range.

Method II Copied Cells
Copied Cells Highlighted!

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.

Paste Special Dialog
Paste Special Dialog Box

Hit ENTER & the formulae within the selected range will be removed at once.

Cells with Formulae Removed 1
Cells with Formulae Removed!

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.

Method II Copied Cells 1
Copied Cells Highlighted!

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.

Paste Icon Appears
The Paste Icon Appears!

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.

Selecting Paste Values Option
Selecting the Paste Values Option

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.

Cells with Formulae Removed 2
Cells with Formulae Removed!

The very moment the option is chosen all the formulae in the selected range are gone and are gone for good!


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!