While working with Microsoft Excel, sometimes, the creation of datasets with several names that subsequently get inadequately labeled, and the danger of mistakenly creating linkages between data. So, we need to delete the name ranges, but several times the name manager delete option greyed out. In this article, we will demonstrate the causes of this problem and some effective ways to fix the name manager delete option greyed out in Excel.
Download Practice Workbook
You can download the workbook and practice with them.
3 Reasons with Solutions of Name Manager Delete Option Greyed out in Excel
We create named ranges to make the workbooks simpler to understand, formulae quicker to enter and analyze, and the ability to construct variable-size ranges to keep our sheets flexible and updatable. But everything has its pros and cons. So, suppose you have created a lot of named ranges but you do not need these anymore so, you want to delete those named ranges. But while deleting those ranges sometimes the delete button is greyed out or inactive.
To solve this problem we are going to use the following dataset. The dataset contains some items and total sales in 3 months; January, February, and March.
Now, let’s see why the delete option of the name manager greyed out and how we can fix this.
Reason 1: Name Manager Delete Option Will Be Inactive If Worksheet Is Protected
To restrict other users from modifying, relocating, or deleting data in a spreadsheet, either mistakenly or on purpose, we protect our worksheet. But the protected worksheet greyed out / inactive the delete option on the name manager.
Suppose, we have a protected worksheet. Now, to delete a named range, firstly, go to the Formulas tab from the ribbon. Then, click on Name Manager under the Defined Names category.
This will open the Name Manager dialog box. And, you can see that the Delete option is greyed out or inactive.
Solution: Unprotect Sheet to Fix Excel Name Manager Delete Option Greyed out
To solve the problem, we need to unprotect the worksheets. For this, we need to follow a simple and quick procedure down.
- Firstly, select the sheet which named ranges you want to delete.
- Secondly, go to the Review tab from the ribbon.
- Thirdly, under the Protect category, click on the Unprotected Sheet.
- Or, you can right-click on the sheet and then select the Unprotected Sheet.
- This will appear in the Unprotected Sheet pop-up window and ask for the Password.
- After that, type the password of your sheet on the Password type box.
- Then, click on the OK button.
- Finally, again if you open the Name Manager dialog box again, you will be able to see the Delete option is now active.
- How to Name a Group of Cells in Excel (3 Methods +1 Bonus)
- Edit Defined Names in Excel (Step-by-Step Guideline)
- How to Use Named Range in Excel VBA (2 Ways)
Reason 2: Table in Excel Makes Name Manager Delete Option Inactive
Excel tables allow for rapid and convenient reading of problems shown in rows and columns. While creating a table, Excel immediately formats this. However, users can simply modify the format. But if your data is converted as a table, the name manager delete option greyed out.
As you can see the named ranges are named as Table1, and this can not be deleted.
Solution: Convert Table to Range
The only method to remove them is to return the table to a range. For this, follow the steps below.
- In the beginning, select the table.
- Then, go to Table Design on the ribbon.
- After that, under the Tools category, click on Convert to Range.
- This will open a pop-up window, which will ask Do you want to convert the table to a normal range?
- Next, click OK.
- Now, to name the range, select the whole dataset and go to the Formulas tab from the Excel menu bar.
- Further, click on Create from Selection under Defined Names.
- This will open a pop-up window. Now, tick-mark the Top row and Left column box.
- Furthermore, click OK.
- And, now, if you go to the Name Manager you will see the Delete option is now active.
Read More: How to Name a Cell in Excel (4 Easy Ways)
Reason 3: Excel Display Settings Make Name Manager Delete Button Greyed out
The name manager delete button will grey out for some excel settings. When the above reasons for the problem are not found and can’t solve the problem, we have to change excel settings from excel options.
Solution: Change Advanced Settings from Excel Options
To change advanced settings from excel options, we need to follow the steps down.
- In the first place, go to the File tab from the ribbon.
- After that, click on Options.
- And, this will open the Excel Options dialog box.
- Next, go to the Advanced menu.
- And then, under Display options for this workbook, there is For objects show selection box. Now select All from there.
- Now, clicking on the OK button will solve the problem.
Read More: How to Edit Named Range in Excel
The above reasons and solutions will assist you to fix the excel name manager delete option greyed out. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!