Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Remove Dropdown in Excel? 3 Easy Methods

How to Remove Dropdown in Excel? 3 Easy Methods

  • Facebook
  • Twitter
  • YouTube

(Note: This guide on how to remove dropdown in Excel is suitable for all Excel versions including Office 365)

While using Excel, manually entering data into each cell can be a bit arduous. Especially when the data you enter is similar, typing them may feel a little redundant. 

In such cases, adding a dropdown to the cells in Excel helps you to enter the data easily. This method of adding data as dropdown in Excel cells is called Data Validation. 

But sometimes, using the data validation method and dropdown to add values in Excel has some disadvantages. In this article, I will tell you why and how to remove dropdown in Excel using 3 easy ways.

You’ll Learn:

  • Why Remove Dropdown in Excel?
  • How to Remove Dropdown in Excel?
    • Using the Data Validation Method
      • Remove a Particular Dropdown List
      • Remove an Option from the Dropdown List
    • Using the Clear Button
    • Using the Copy and Paste Option

Related Reads:

How to Create Excel Drop Down List With Color?

How to Switch Between Sheets in Excel? 6 Useful Methods

How to Add Dynamic Dropdown lists in Excel — 4 Easy Steps

Why Remove Dropdown in Excel?

A dropdown list consists of a list of values that you can click and select to populate the cell. 

The main drawback of using a dropdown list to enter data is that you cannot enter any data other than the values in the dropdown list.

When you enter any data manually, Excel either throws a pop-up saying “this value doesn’t match the data validation restrictions defined for this cell”. In some cases, Excel accepts the data but shows a flag in the corner of the cell.

Popup appears when you add additional data
Popup appears when you add additional data

If you are not satisfied with the dropdown list, you can modify them or even remove them completely. Let us see how. 

How to Remove Dropdown in Excel?

There are a couple of use cases that have different ways to remove dropdown from Excel sheets. 

Using the Data Validation Method

Data Validation is an effective method to create dropdown lists in Excel. Using the same option, you can clear or remove the dropdowns created before.

There are multiple ways and cases you can use to remove dropdowns in Excel.

Remove a Particular Dropdown List

If you are not satisfied with any data column being a dropdown list, you can choose to remove any specific columns with dropdowns. Look at the above-mentioned example. The columns Test Center, Grade, and Result all have a dropdown list to choose from. Suppose you want to remove the dropdowns in the Test Center column, you can remove them using the Data Validation option.

The cells contain dropdown
The cells contain dropdown
  • First, select all the cells with the dropdown you want to remove. In this case, we want to remove the dropdown from the Test center column.
  • Navigate to Data. Under the Data Tools section, click on Data Validation.
Click on Data
Click on Data
  • If you have selected any other cells which do not have the Data Validation, Excel throws a popup saying “The selection contains some cells without Data Validation settings. Do you want to extend data validation to these cells?”. Click Yes.
  • This opens the Data Validation dialog box. Under the Settings tab, click on the Clear All button.
  • Once you click on Clear All, the data validation inputs and settings will be cleared. Now, click OK.
Click on Clear All
Click on Clear All

Thus the data validation inputs and the dropdown for the selected cells have been removed.

How to Remove Dropdown in Excel
How to Remove Dropdown in Excel

Note: If you want to remove the data validation from all the cells and columns, select all the cells by clicking on the select all triangle at the top or by pressing Ctrl + A. Now, navigate to Data > Data Validation and select Clear All.



Remove an Option from the Dropdown List

The above Clear All option in the Data Validation dialog box will delete all the dropdowns from the selected cells. However, if you want to remove any particular option from the dropdown, you don’t have to remove the whole dropdown. You can just remove it from the same Data Validation dialog box.

  • First, select all the cells which have the particular data validation. 
  • Then, navigate to Data. Under the Data Tools section, click on Data Validation.
  • This opens the Data Validation dialog box. 
  • In the Source text box, delete the particular option/data. Also, check the checkbox for Apply these changes to all the other cells with the same settings, and click OK.
Delete the option and click OK
Delete the option and click OK
  • This removes the particular data from the dropdown.
The particular option is removed
The particular option is removed

Note: If you have linked the data to the dropdown from another sheet, you can easily navigate to the sheet and delete that particular option. 

Suggested Reads:

How to Remove Dotted Lines in Excel? 3 Different Cases

How to Remove Comma in Excel? 5 Easy Ways

How to Remove Spaces in Excel? 3 Easy Methods

Using the Clear Button

This is a very effective and quick way to remove dropdown in Excel. When you delete any data in the data validated cell, the data in the cell will be removed but the dropdown remains. When you use the Clear button, it removes all the data in the cell and also all the formatting applied to the cell. 

  • To remove the data validation, first, select the cells you want to clear. 
  • Navigate to Home. Under the Editing section, click on the dropdown from Clear and select Clear All.
How to remove dropdown in Excel using the Clear button
How to remove dropdown in Excel using the Clear button
  • This removes the Data Validation from the selected cells and also the data in the cells.
The dropdown is cleared
The dropdown is cleared

Note: As the Clear button removes all the data and format of the cells, you can use this option only when it’s indispensable. If you want to retain the data and just remove the dropdown, you can use the above-mentioned method. 

Using the Copy and Paste Option

This is also another easy and effective method to remove dropdown in Excel. This method works on the principle that copying and pasting a cell with particular formatting changes the format of the cell where it is pasted. This way, you can copy a blank cell and paste it into a cell that has a dropdown list and remove it.

  • The most important step is to select an equal number of cells as the number of cells you want to replace. If not, only the number of cells copied will be replaced and the rest of the cells will be left off.
  • Now, select the cells you want to remove dropdowns from.
  • Either, right-click on the starting cell and select Paste or press Ctrl + V.
Copy and paste the blank cells
Copy and paste the blank cells

Thus the selected cells will have the formatting of the copied cell and Excel removes the data along with the dropdowns.

Also Read:

How to Remove Hyperlinks in Excel? 3 Easy Methods

How to Split Cells Diagonally in Excel? 2 Easy Ways

How to Enable Full Screen in Excel? 3 Simple Ways

Frequently Asked Questions

What is the easiest and fastest way to remove the content and dropdown in Excel?

Using the Clear button is the easiest and fastest way to remove the content and dropdown in Excel. First, select the cells. Navigate to Home, under the Editing section, click on the dropdown from Clear and select Clear All. This will clear the data and the data validation in the particular cells.  

Can we remove specific data from the Excel dropdown?

Yes, we can remove certain data from the Excel drop-down and not the whole dropdown. Select the cells. Go to Data and select the Data Validation option. In the Source text box, delete the data and click OK.

How to remove the dropdown in Excel easily?

Select the data and click on Data Validation in the option in the Data ribbon. In the dialog box, click on Clear All and click OK. This removes the dropdown from the cells in Excel.

Closing Thoughts

Removing the data validation dropdowns in Excel helps you to easily enter data into cells, especially when they are not listed in any of the dropdowns.

In this article, we saw how to remove dropdown in Excel using 3 easy ways. You can either choose to remove just the data validations or the data from the cells. Choose the method that suits your purpose best.

If you need more high-quality Excel guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 140+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

Top Ten Excel Functions you HAVE to know.

How to use Date and Time Functions in Microsoft Excel 2013

Getting Excited About Outlook 2013 New Features

How to Insert a New Worksheet in Excel? 5 Easy Ways

How to Create a Credit Memo in QuickBooks 2019

How to Combine Two Schedules in MS Project? 2 Easy Steps

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)