Find broken reference in Excel

I recently had to find a broken reference in an Excel file, where data was copied from one version of the file to another. In the original version there was a data validation rule with a dropdown to a list of allowed base data from another excel sheet.

In a next step the original file was copied and modified to fit new requirements. Meanwhile the original file was used and data was collected in the old file.

After the new file version was approved, the data from the old version was copied (CTRL+C , CTRL+V) to the new file and the old file was removed. However, when opening the new file excel reported a broken link to the old file.

While this problem can easily be identified in a small excel, this can be a challenge in a huge file with lot of sheets. One way to identify the problem is to unzip the excel file (e.g. using winrar, 7zip, etc). A excel file contains many XML and other files. The worksheets for examle can be found in the xl/worksheets folder

After extracting the worksheet folder to the file system, you may use a tool like Visual Studio Code and open the folder.

Simple by searching for the name of the broken reference you will find the place where to fix the problem