Default Keys: Ctrl + Shift + Alt + 3
Ever been too scared to delete worksheets from your Excel file because you were afraid another sheet might need them? If you’ve ever inherited workbooks, chances are that you’ve come across some that have been passed between so many people that no one remembers what’s-what in them. You know, the workbooks where there are so many sheets that you want to clean some up, but are too afraid to because another of the sheets might need them, so you just hide the questionable ones until later… And of course later only comes to the next person who inherits the workbook… And the cycle continues.
I built the Trace Xtreme Sheet Nutility to help with this. This Nutility will not only help you decide which sheets are OK to be deleted, but also help you understand how the data flows between worksheets. The way it work is simple. Pick any sheet in a workbook that you want checked, and run the Nutility. The Nutility will then search through the rest of the sheets, within that workbook, to see if any of them depend on the checked-sheet. Once complete, the Nutility will popup a report listing the different dependent-ranges from the other sheets.
This Nutility requires you to have the Nutilities Excel add-in installed in order to use it. If you don’t know what Nutilities is, or where to get it, please start at my web-page here: http://iwishexcel.com . The add-in is completely free to use.
Instructions and Example:
Given the below example workbook picture. You can see that there are 4 sheets, and that we are looking at the Checked-Sheet. This sheet has data that the other 3 sheets depend on. While I will not show each to you, the Formulas sheet has formulas depending on the Checked-sheet, the Pivot-Sheet has a dependent pivot table, and the Chart-Sheet has a dependent chart.
If we were to run the Trace Extreme Sheet Nutility from the Checked-Sheet, we would first be met with a popup like the picture below. This popup just lets us know that we are about to run the trace Nutility, and gives us a chance to cancel it before running. Since this Nutility is very memory intensive, it is always possible Excel may freeze, so it is highly recommended that we do not run the Nutility until we have saved/closed any open workbooks. Also, while the Nutility does an exhaustive search for items that might depend on the Checked-Sheet (Named/Table Ranges, Indirect Formulas, Pivot Tables, Charts, Standard Formulas), it is not perfect ,and there are some things it cannot check (Power Pivot Tables and the Data Model for example), so it recommends saving a backup copy of our workbook before deleting any sheets out of it.
So, after having run the Nutility on the sample workbook, and clicking OK on the above popup, we will be met with the following report.
This report lists all the items that were found in the workbook that depend our Checked-Sheet. It lets us know the sheet of the dependent item, its range, and its type. This report’s user-form is also modeless — this means we can keep it open while clicking around the workbook. So we can keep the report open while investigating each of the dependent items. To quickly navigate to one of the items, either double-click it, or select it, and then hit the “Goto Selected Range” button. If you want to goto, and select all items on a worksheet, select one of the items from the desired sheet, and click “Goto Selected Sheet”. Excel will then goto the sheet from the highlighted item, and select all of that sheet’s ranges from the list.