A common issue in Excel is the fear of deleting sheets that you feel are not required, but result in #REF! errors in the rest of the spreadsheet if you delete them. All to often these sheets are just left in. So the question is how to safely delete sheets so there are no REF errors.
Firstly, the #REF! error is actually useful. It is telling you that something you have deleted is actually used in the spreadsheet (even if you think the sheet is meaningless). So the trick is to figure out what is being used and whether it should still be used.
Table of contents
YouTube Safely delete sheets so there are no REF errors
Move the sheets, don’t delete them
As tempting as it is to delete a sheet, rather move it. Below we want to delete the sheet called Inputs v1 and based on the one formula we are looking at, this should be OK as it is referring to Inputs v2. But when we delete the sheet we get #REF! errors.
Rather move the sheet out. You can do this by:
- Right Clicking on the unwanted sheet
- Choose ‘Move or Copy’
- Click on the drop down and choose new book
You will now have 2 workbooks. As you ‘moved’ the sheet, any formula that was linked to this sheet will follow it. If there was no link then it won’t make a difference. If you are going to be quick you don’t need to save and name it, but if you might take a bit of time, save it as DELETED SHEETS or something like that.
Now check if the sheets are linked
The key step is to see if the original file is looking at this new file.
- Click on the original file
- Click on DATA and then EDIT LINKS as shown below (if it is greyed out there are no links)
- Have a look in the list of links for the file you just created.
- If you see the file you just created (Book3 below) it means that something in the original spreadsheet is looking at something in the new spreadsheet which contains the sheet you want to delete.
How to find the cells that are linked
Now that you know there are links, you need to find the cells which are linked to decide if they are important. The key steps are:
- Remember the name of the file in the Edit Links box. Above it will be Book3.
- Do a find (CTRL + F is a shortcut) and search for the file name (i.e. Book3 as below)
- Click on Options and choose to search through the whole workbook
- Click on Find All
You will be provided with a full list of cells that now contain ‘Book3’ in the formula. You should work through this and decide if these are valid and what you are going to do with them.
Once you have removed / changed all the linked cells, you can just save the file. You can delete or save the other file.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
Difference between IFERROR and ISERROR in Excel