In this blog post, you will learn about how to remove formulas and keep the values in Excel.
There are many formulas in the dataset and sometimes we want to share it but do not want to share the formulas.
We want to remove the formula but want to keep the values as it is.
There are a couple of ways to remove the formula from the dataset and keep the values.
In this tutorial, we are using the below image to learn the concept of this post in a better way.
See the image below:-
In this post, we will share 4 methods to remove the formulas and keep the values.
Paste Special Method
First of all, we will find the area, where we have applied all the formulas.
Select the entire formula range and copy it.
Once the formula range is copied then ‘Paste Special’ has many ways to paste the data as values.
First Way
Right-Click on the copied data and select ‘Paste Special’ from right-click properties.
See the image below:-
Just hover the mouse in the ‘Paste Special’ option and a new options menu will open.
From these options, select the second option ‘Paste Values’, and click on ‘123’ just under it.
Which will convert the copied formulas into the values.
Second Way
Just click on the ‘Paste Special’ option from right-click properties.
A new ‘Paste Special’ dialogue box will open and then select “Values” from all the given options.
See the image below:-
And click “Ok”.
See that all the formulas are removed but values remain the same.
Third Way
Once you copy data where all formulas are applied.
Then go to the ‘Home’ tab and just below this, click the drop-down arrow of the ‘Paste’ button.
You will see the multiple options to paste the copied data.
Click “123” under the ‘Paste Values’ option and all the formulas have been removed but values remain the same.
Drag and Drop from Mouse Method
In this method, just select the area where you have applied the formula.
Then copy the entire formula part, put the mouse cursor at the edge of the border and keep pressing ‘right-click’ and move the cursor a little towards the right.
See the image below:-
And then bring back the mouse at the same location, i.e. selected area, and leave the mouse here.
When we leave the mouse, there will be more options that appears here.
And choose the option of what you want to do with your copied dataset.
Here we want to change our formulas into values, so we will choose the ‘Copy Here as Values Only’ option.
Once you click on this option, you will see that all the formulas are removed but the values are remaining the same.
Keyboard Shortcut Method (Alt + ESV)
This is one of the easiest methods to remove formulas and keep the values the same in Excel.
Here we will use the keyboard shortcut (Alt +E +S + V).
First copy the data where formulas are applied for that use (Ctrl + C) as the keyboard shortcut.
And then use another keyboard shortcut (Alt + E + S + V) at the same place to paste it as values.
Adding Command Button in QAT
Here we will add a command button in the QAT area.
For that, go to the Quick Access Toolbar area and click on the provided drop-down here.
Or right-click anywhere in the QAT (Quick Access Toolbar) area and select ‘Customize Quick Access Toolbar option from here.
See the image below:-
Once you click on it, you will get the ‘Excel Option’ dialog box.
Here you need to change from ‘Popular Command’ to ‘All Command’ in the “Choose Commands From” drop-down.
See the below image for changing ‘Popular Commands’ to ‘All Commands’ and press “Ok”.
Once you click ‘Ok’, you will find more command options in the list.
And then find “Paste Values” in the list and once you find it click on the “Add” button provided at just right.
Once you click the ‘Add’ button, see that this option has been added to the right side box Customize Quick Access Toolbar.
And after this, press the “Ok” button provided below.
After clicking on the “Ok” button, see that your ‘Paste Values’ command button is added to your QAT area.
See the image below:-
So when you copy the formula and want to change it to the values.
Just copy the formula part and go to the QAT area and click on the ‘Paste Values’ command button.
See that all the formulas are now converted into values.
In this case no need to use any keyboard shortcut or any paste special method.
If you want to learn more about this, please visit Microsoft Office Support.
I hope you find this tutorial useful.
Please put your comments or suggestion in the box provided below.
Thanks
Related Post
3 Easy Ways to Select the Visible Cells Only In Excel
How to Highlight Every Other Row or Column in Excel
How to Insert Blank Rows after Every (2nd or 3rd or nth) Row in Excel