Do you want to learn how to delete a defined name in Excel? Follow along with the simple methods mentioned in this guide.

Managing defined names in Excel is like cleaning up an overstuffed file cabinet. Over time, your workbook can accumulate unnecessary or outdated defined names that clutter your data and make formulas harder to manage.

Figuring out how to delete these names might seem tricky, especially if you’re not familiar with Excel’s Name Manager tool. But don’t worry—this tutorial breaks down the process into simple, clear steps anyone can follow. Let’s dive in and get your Excel workbook back in shape!

Using the Name Manager

The built-in tool that allows you to create, edit, delete, and filter named ranges in Excel is the Name Manager. You can find it in the Formulas tab. Find below quick steps to use this command to delete a defined name in Excel.

Go to the source worksheet and click on the Name Manager command inside the Defined Names block of the Formulas tab.

Select the named range you want to delete within the dialog box.

Hit the Delete button in the Name Manager toolbar.

A Microsoft Office warning dialog box will pop up. Click Yes to confirm the deletion.

If you delete any named range involved in an Excel formula, you’ll see the #NAME? error in the cells where that formula was applied.

To delete more than one defined range in the Name Manager dialog box you can use the Shift select method.

For example, if you’d like to delete all the named ranges, select the first item, press the Shift key, and click on the last defined name.

Click on the Delete button.

You must replace the formula argument with a new value or use the IFERROR function to resolve this error message.

📒 Read More: What Does #NAME? Mean in Microsoft Excel

Deleting the Cell Range

Suppose, you’d like to remove many named ranges to improve the workbook performance. You’ve also found out that the cell ranges of those named ranges aren’t needed anymore. Hence, you can simply delete the entire column or row to remove the named range automatically.

Click on the down arrow in the Name Box to view all named ranges.

Select the defined name you want to delete. This will highlight the underlying row or column.

Right-click on the column alphabet or row number and choose Delete from the context menu.

Now, if you go back to the Name Box and expand the list, you’ll see the defined name that was representing the recently deleted row or column, is missing.

Using an Excel Add-In

Often, you might encounter stubborn and corrupted named ranges that don’t show the Delete button in the Name Manager dialog box.

In such a situation, you can use an Excel add-in to remove the troubled defined names. One of the best add-ins for this purpose is the Name Manager for Excel by JKP Application Development Services. It’s a free add-in so you don’t need to subscribe to any paid plan.

To use an Excel add-in, you must enable the Developer tab. If you don’t know how go through the following quick tutorial:

📒 Read More: 2 Ways to Add the Developer Tab in Microsoft Excel

Once you’ve activated the Developer tab, click on the Add-ins command inside the Add-ins block of the Developer tab.

The Office Add-ins dialog box will open. Click on the Store tab to navigate to the Excel add-in marketplace.

Click on the Search box and type Name Manager.

Hit Enter. The Name Manager for Excel add-in will show up at the top.

Click Add to download and install the add-in.

Navigate to the Formulas tab to find the Name Manager add-in. Click on the N icon in the Excel ribbon menu to open the Name Manager navigation panel on the right side.

Hit the Refresh button to load all visible and hidden named ranges of the workbook.

You can use the Names Type list, like External refs, Errors, Volatile, Hidden, etc., to filter the necessary defined names you want to delete.

Select one or multiple named ranges below the Names column.

Click on the Delete button to remove all unwanted named ranges.

Using Excel VBA

Setting up an Excel VBA macro and using that is the best way to delete defined names in an Excel workbook intuitively. It allows you to create a graphical user interface where you can look at all the active named ranges in the workbook and find out to which formulas these defined names are linked. Therefore, you can remove such named ranges without causing trouble to formula cells that you don’t want to disturb.

This method also automates the entire process from start to finish. So, highly suggested if you have to manage many workbooks with countless defined names.

The Excel VBA-based approach has two phases. In the first phase, you must create a VBA macro using a VBA script. Find below an excellent Excel guide that explains the complex process in easy steps:

📒 Read More: How To Use The VBA Code You Find Online

Now, use the following script to set up a VBA macro:

Sub DeleteNamedRanges()
    Dim wb As Workbook
    Dim nm As Name
    Dim usedInFormula As Boolean
    Dim refCell As Range
    Dim formulaCell As Range
    Dim namedRangesList As String
    Dim deleteNames As Variant
    Dim i As Integer
    Dim deletedCount As Integer
    
    Set wb = ThisWorkbook
    namedRangesList = "Named Range" & vbTab & "Reference Cell/Formula" & vbCrLf
    
    ' Scan through the workbook and list all named ranges
    For Each nm In wb.Names
        usedInFormula = False
        For Each refCell In wb.Sheets(1).UsedRange
            If InStr(1, refCell.Formula, nm.Name, vbTextCompare) > 0 Then
                namedRangesList = namedRangesList & nm.Name & vbTab & refCell.Address & ": " & refCell.Formula & vbCrLf
                usedInFormula = True
            End If
        Next refCell
        If Not usedInFormula Then
            namedRangesList = namedRangesList & nm.Name & vbTab & "Not used in formula" & vbCrLf
        End If
    Next nm
    
    ' Show the list of named ranges and prompt for input
    deleteNames = InputBox(namedRangesList & vbCrLf & "Enter the named ranges to be deleted, separated by commas:", "Delete Named Ranges")
    
    ' Delete the named ranges specified by the user
    If deleteNames <> "" Then
        deleteNames = Split(deleteNames, ",")
        deletedCount = 0
        For i = LBound(deleteNames) To UBound(deleteNames)
            On Error Resume Next
            wb.Names(Trim(deleteNames(i))).Delete
            If Err.Number = 0 Then
                deletedCount = deletedCount + 1
            End If
            On Error GoTo 0
        Next i
        
        ' Show confirmation message
        MsgBox deletedCount & " named range(s) deleted.", vbInformation, "Confirmation"
    Else
        MsgBox "No named ranges specified for deletion.", vbInformation, "Operation Cancelled"
    End If
End Sub

When the macro is ready and saved, Alt + F8 to launch the Macro dialog box.

Select the DeleteNamedRanges macro and hit Run.

This will show an input box with a list of named ranges and their links to other formulas or cells.

Type in the defined names you want to delete. For multiples, use a comma to separate the named ranges.

Click OK to execute the script further.

Excel will remove the designated named ranges from the workbook and show a confirmation dialog box.

You can click on the Name Box arrow to check whether the selected defined names have been deleted or not.

Using Office Scripts

Office Scripts in Excel for the Microsoft 365 desktop app also lets you automate the task of removing named ranges with ease. You can also integrate this script in Power Automate to remotely trigger such actions.

Click on the Automate tab and select the New Script command in the Scripting Tools block.

You’ll see the Code Editor on the right. Select the existing code and hit Delete to remove that.

Now, copy and paste the following script into the Code Editor field and hit the Save script button.

function main(workbook: ExcelScript.Workbook) {
    // Get all the named ranges in the workbook
    let namedItems = workbook.getNames();

    // Loop through each named range and delete it
    namedItems.forEach(namedItem => {
        namedItem.delete();
    });

    // Log a confirmation message
    console.log(`${namedItems.length} named ranges have been deleted.`);
}

Click on the Run button to execute the script.

Excel will instantly remove all the named ranges in the workbook.

Conclusions

These are some of the proven ways to delete one or many defined names in any Excel workbook, provided that the file isn’t locked for editing.

You can try any of the above methods depending on the task you need to achieve and your Excel expertise level.

Did this Excel tutorial help you learn to manage named ranges? Do you know of a better method than the ones I’ve outlined above? Use the comment box to leave your reply.