Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • Gmail
    • Productivity
      • Time Management
      • Task Automation
    Inside The Web
    Home»Excel»How to Delete Drop-down List on Excel

    How to Delete Drop-down List on Excel

    jabinBy jabinFebruary 7, 2023 Excel 4 Mins Read

    Dropdowns in Excel are usually created to let users choose from the pre-defined options. However, once they submit their preferred option, you may no longer require the dropdown except for their final answer.

    And, in some cases, you may need to edit and remove only the particular dropdown values.

    In both cases, you can use the Data validation tool to remove these lists along with all their associated dropdowns on your worksheets. Also, you can search across the whole worksheet for both similar and different kinds of dropdowns to remove them.

    By Removing the Data Validation

    Using this method, you can remove a dropdown along with all its associated dropdowns in the whole worksheet. Once you remove the list from a cell, it will only contain the most recently selected values. 

    1. Select the cell (s) containing the drop-down list.
    2. Under the Data tab, select Data validation > Data validation. You can find the settings inside the Data tools section.
      Select-Data-validation
    3. On the next window, click Clear All.
      Clear-all-Excel-dropdown-list
    4. Additionally, enable the Apply these changes to all other cells with the same settings checkbox to select all the cells of the cell range with the same dropdown values. Then, click Clear All.

    On the same Data validation window, you can select replace List with Any value under the Allow field to remove the dropdown.

    Note: When selecting multiple dropdown lists with different data validation settings, you might be prompted with a message like “The selection contains more than one type of validation.” If so, just click OK to continue and then click Clear all.

    Using the Copy-Paste Method

    This method utilizes the fact that when you copy a cell and paste it on another cell, its formatting is applied to the new cell. It’s useful in cases where you want to remove the dropdown and replace it with a preferred cell formatting.

    1. Copy a cell whose formatting you want to apply to the cell containing the dropdown list. Choose a blank cell if you want no formatting after the removal.
    2. Select the cell (s) containing the dropdown lists.
    3. Right-click and select the Paste option.
      Paste-over-dropdown-lists
    Note: This method will remove the dropdown list completely along with all of its options.

    Using the Clear All Option

    With this option, you can delete the dropdown list completely with all its values. However, you must already know the cell references containing the dropdown lists.

    1. Select the dropdown lists.
    2. Click the Home tab.
    3. Select Clear > Clear all.
      Using-Clear-all

    Using Go To Method

    If you have dropdowns scattered all over your worksheet, finding and deleting them manually can be inconvenient. Using this method, you can choose to remove them irrespective of whether they are based on the same dropdown values or not.

    1. Select a cell inside a dropdown list and press Ctrl + G.
    2. On the Go To window, click Special.
      Select-Special
    3. Then, select Data validation and choose one of the following options and click OK.
      Clear-Data-validation-using-Go-To
      • All: Every dropdown cell that exists on the worksheet
      • Same: Only the dropdown cells are based on the same dropdown list and data validation settings.
    4. Once all the dropdowns are selected, select Clear > Clear all under the Home tab.

    How to Delete Dropdown List Items in Excel?

    Instead of deleting the entire list, you may sometimes require to delete specific dropdown values. In that case, you can follow a slightly different process depending on what kind of dropdown list you have.

    Dropdown with Manually Entered Inputs

    1. Select the cell (s) containing the dropdown list.
    2. Under the Data tab, select Data Validation > Data Validation.
    3. Select the Settings tab.
    4. Now, you can delete the dropdown items under the Source field.
      Delete-Manual-dropdown-list-options
    5. When done, click OK. Alternatively, enable the Apply these changes to all other cells with the same settings checkbox to update the dropdown options across all cells.

    Dropdown Based on Cell Range or Named Range 

    1. Select the worksheet where you have stored the dropdown list.
    2. Select the cell (s) with the dropdown values you want to remove.
    3. Then, right-click on it and select Delete. Here, we are removing the TipMonth option from the dropdown list.
      Delete-dropdown-value
    4. On the Delete window, select Shift cells up.
      Shift-cells-up-delete-a-dropdown-option

    If you can’t figure out where the cell range or named range based dropdown values are stored in the list, follow the following steps.

    1. Select one of the cells with the dropdown list.
      Select-a-cell-reference-containing-the-dropdown-list
    2. Click Data validation under the Data tab.
    3. Click anywhere under the Source tab.
    4. Once the dropdown values are highlighted with the dotted lines, close the Data validation window.
      Locate-Dropdown-list-values
    5. Now, repeat steps 3 and 4 mentioned above to delete the dropdown values.
    Excel Basics
    jabin

      As a tech content writer, Jabin covers Excel-related articles at InsideTheWeb. His articles mainly involve helping new users to quickly familiarize themselves with the Excel interface and explaining various essential features. While he got introduced to Excel in his early school days, he developed a keen interest in it after working on a college project. He was impressed at how quickly one could accomplish several tasks with built-in functions like the filter function and user-friendly tools like the power query. Keeping beginner audiences in mind, he loves to explain even the most fundamental Excel concepts in detail and break down complex topics with a step-by-step approach. As an avid Excel user, he believes every task can be done a lot quicker if you know the right tools and techniques. When he’s not behind a keyboard, he loves to listen to interesting audiobooks and podcasts.

      Add A Comment
      Table of Contents
      • By Removing the Data Validation
      • Using the Copy-Paste Method
      • Using the Clear All Option
      • Using Go To Method
      • How to Delete Dropdown List Items in Excel?
        • Dropdown with Manually Entered Inputs
        • Dropdown Based on Cell Range or Named Range 
      Recent Posts
      • How to Calculate Discount Percentage in Excel
      • How to Create a Progress Bar in Excel
      • What is VSTACK in Excel
      • How to Separate Dates in Excel
      • How to Lock a Cell in Excel Formula
      • Home
      • Privacy Policy
      © 2023 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.