Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need to delete sheets from a workbook w/o breaking formula references; is there a way to do this via code or settings?

share|improve this question

migrated from Oct 21 '11 at 9:22

This question came from our site for professional and enthusiast programmers.

What do you want to happen instead of the references breaking? Your question doesn't give much to go on... – Tim Williams Oct 19 '11 at 19:02

4 Answers 4

My free Mappit! addin available from has as one of its outputs a map that shows worksheet relationships

You could use this to detect where the potential issues from sheet deletion may arise

enter image description here

share|improve this answer
Due to the nature of SO, you will get less downvoting if you host your module elsewhere ;) – mplungjan Oct 20 '11 at 20:00
Hi Michel, that certainly hasn't been my experience here thus far, it's very much about solution competency not origin. Plus ive always found the excel topics (at any forum) tend to stay above the site v site debates. – brettdj Oct 20 '11 at 21:53
Great. It was however downvoted for some reason. I voted it back up. – mplungjan Oct 21 '11 at 5:43
well there you go - I must have missed the earlier downvote that you were referring to. Thanks for that – brettdj Oct 21 '11 at 11:13
The link is broken :( – Cody Piersall May 14 '14 at 13:29

If you are hard coding to a specific sheet that would be impossible. Are you implying you would like the dependency to be shifted to another sheet?

share|improve this answer

As stated above, cannot delete the sheet without breaking the reference. However here are some options depending on your circumstances:

  1. Copy/paste special:values to retain the result of the formula and delete the sheet.
  2. Hide the sheet and protect the workbook instead of deleting the sheet.
  3. Change the references to a sheet in another workbook.
share|improve this answer

this is 4 years after but i wanted to leave my solution here in case it helps anyone having the same issue


  1. Cut (CTRL+X) the cell and it's contents that your formula is referencing from the 1st sheet (sheet that is going to be deleted, ex.: Sheet!$A$1)
  2. Paste the cut cell in the 2nd sheet at the new or same location (sheet that is going to be kept, ex.: Paste it in Sheet2!A1)
  3. Do this for all cells that needs to be transferred over
  4. Check the formulas to make sure they are referencing the 2nd sheet (Sheet2! instead of Sheet1!)
  5. Delete the 1st sheet
  6. Rename the 2nd sheet, if necessary, to the same name as the 1st sheet that you had

Hope this helps

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.