When you perform a normal copy and paste on a cell in Microsoft Excel 2019, 2016, and 365 that has been calculated by a formula, the formula gets pasted as well. There is a simple way around this if you wish to just copy and paste only the value within the cell and nothing more. Just follow these steps.
- Select the cells or ranges you wish to copy.
- Select the “Home” tab.
- Select “Copy” in the “Clipboard section.
- Select the cell you wish to paste your values to.
- Select the lower half of the large “Paste” button. From the extended menu that appears, select “Values“.
- Select “OK“.
You have successfully pasted a value in Excel without including the formula.
Sebastian says
Dear Mitch,
thank you for helpful summary. It really helped. While thinking about it, there is another workaround.
You could take the value you need to copy and paste to a cell outside the columns you need to enter data.
(Example:
The formula which generates the wanted value is in A1.
You could copy the formula or use =VALUE(A1) in B1.
Then go to Review->Protect. Open Allow Edit Ranges.
Set it to allow entries in the columns/rows you need to enter data (in this example column A).
Then right click on B1, go to format cell->Protection. Check locked and hidden. Then protect the sheet. After you have done that, if you click on B1 and right click copy it will only copy the actual value in B1. The formula won’t be copied.
If you set the allow range properly, you still can change/enter stuff in column A.
Sincerly yours Sebastian
ayad says
can it be done automatically with a function? when i change the value in the function i want to not Change the copy .
Priyanka says
Good Method to paste
George says
can it be done automatically with a function?
Kostadin says
Thank you Mitch Bartlett for this very useful information. :-)
Abdelrahman Elshorafa says
Thanks so much for useful description
Mike says
so, to summarize – you have no info, how to copy values without formulas, you know only how to paste them. So – could you fix the name of this page, as its totally incorrect for this moment?
Chaman Lal Dewangan says
It is very helpful. Thank You
Kaustav Khatua says
It was very much helpful. Thank you.
Benjamin says
Thanks i tried but i failed
Hitesh says
Is there a keyboard shortcut for this?
Efrem1 says
Thank you !
nicole says
thank you very much Mitch Bartlett for what you are doing. It is a tremendous help !