Managing and removing named ranges in Excel can be a challenging task, as you would have to do this one by one for each field. In this guide, we’re going to show you how to make Excel remove named range containing errors.
Filtering named ranges with errors
The first here is to open the Name Manager window, which essentially lists all visible named ranges in your workbook. You can find the Name Manager icon under the Formulas tab of the Ribbon.
The Name Manager window is a dialog where you can manage existing names in the workbook. Please note that the manager only shows visible named ranges. To hide named ranges, you would need to run a VBA code: How to show named ranges that were made hidden using VBA.
You can see Excel named ranges with errors easily by using the Filter button on the top of the right section. Click on the button, and select Names with Errors. Activating this filter lists all named ranges that contain an error.
Making Excel remove named range with errors
Next steps is easy, because only named ranges with errors are listed in the Name Manager window. All you need to do is to select all those named ranges, and click the Delete button.
To select all named ranges,
- Select the first named range in the list
- Scroll down to the list if necessary
- While pressing the Shift key, click the last item on the list, very much like how you would select a range of cells in Excel
Once all of named ranges are selected, click the Delete button to make Excel remove named range with errors.