1

I have a sheet called data where I perform my calculations using various formulas. I create a new sheet called summary and copy-paste the calculated values from my data sheet into my summary sheet using Paste Special. I save the file and after 30 minutes of further work on summary sheet I realise that all the formulas in the data sheet are replaced with the values. Restoring the formulas using Undo is impossible. How can I restore them and why did the original formulas disappear in the first place?

Improve this question
3
  • 4
    There is not enough information here for us to provide a meaningful answer.
    – cybernetic.nomad
    Commented Nov 12, 2019 at 18:18
  • @cybernetic.nomad Sorry for that, the situation is really weird, but I was thinking someone may have had the problem too and found a solution
    – Arthur Khazbulatov
    Commented Nov 15, 2019 at 22:30
  • I would guess this has to do with a corrupt source (data) file or a unique bug. I've certainly never heard of or observed it happening myself in my years of IT work. What version of Excel? Have you been able to duplicate the issue?
    – music2myear
    Commented Nov 16, 2019 at 2:42

2 Answers 2

Reset to default
-1

If you selected "Values" from the "Paste Special" dialogue box then you will have pasted the results of the formulas, not the formulas themselves.

I'm not sure if you can get back from this, but try the following:

  • Save your work and exit the file
  • Make a copy of the file, open the copy and verify it looks good, then close it again - THIS IS IMPORTANT, if the rest of this doesn't work then at least you can go back to this copy
  • Open the original file again
  • Highlight the area with the formulas on the original sheet and copy them
  • Highlight where you pasted on the new sheet
  • Use "Paste Special" but this time select "Formulas" then click "OK"

If this doesn't work, remember you can use Undo (Ctrl+z) to back out the paste and then you can try again, choosing some other option from the "Paste Special" dialogue box.
Don't be afraid to experiment because you have got your back-up copy of the file if things get really messed up.

Good luck :)

Nick

Improve this answer
4
  • The pasted results being values are totally fine. My problem is that the original formulas that I copied were replaced in-place by their values. This is very unsettling and the only explanation for me is: I may have accidentally pasted the values back to where I copied them from, replacing the formulas.
    – Arthur Khazbulatov
    Commented Nov 15, 2019 at 22:27
  • Please read the question. Op made it pretty clear this isn't the case.
    – music2myear
    Commented Nov 16, 2019 at 2:40
  • @music2myear and Arthur Khazbs - my apologies, I have re-read the Op and realise my foolish mistake.
    – Nick Clifton
    Commented Nov 18, 2019 at 12:22
  • Now that I properly understand, I do not have an answer, however I have encountered similar behaviour in LibreOffice Calc: When entering data into sheet2 I later find the data duplicated into sheet1, the significant thing here is that sheet2 was initially created by making a copy of sheet1, is it possible that making a copy of a sheet creates some sort of link between them?
    – Nick Clifton
    Commented Nov 18, 2019 at 12:25
-2

Paste Special values only include the text from your formulas, If you wish to paste the formulas into a different worksheet/workbook, use the paste formulas option (highlighted in yellow).

enter image description here

This paste option will allow you to paste formulas without altering the text in other cells.

Have you tried opening unsaved files?

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .