All You Need to Know About

Data Validation in Excel

Learn how to add drop down lists, whether static or dynamic, to have complete control of your data input!

This section shows how much you can do in implementing data validation in Excel.

You would think that Microsoft Excel is all about inputting raw data inside. But add the layer of data validation and you can have more control over getting clean data. Data Validation in Excel is a wonderful feature and is essential to master if you want to have complete control of your data.

You can have custom data validation in Excel as well with dynamic drop down lists! It will look like magic when you see your drop down lists change their contents after another field has been populated. Read all about it below!

Here are the top things on how to do Data Validation in Excel:

Dynamic Data List using Excel Tables

Excel Tables have many great features to them and one of them is their ability to create a dynamic drop down list.

A dynamic drop down list expands as the Excel Table expands when new data gets added to it.

This is great when you want to have users select from a predefined text or value list rather than having them manually enter data, which can lead to mistakes.

DOWNLOAD EXCEL WORKBOOK

Here is how to create a dynamic data validation list using Excel Tables:
STEP 1:  Convert your list in to an Excel Table (Ctrl+T)

STEP 2: Select your Table column by hovering over the Excel Table and left clicking when the arrow pointer shows

STEP 3: In the ribbon menu, go to Formulas > Define Name > enter a custom name with no spaces (we will put this name in step 5) and press OK

defined name list

STEP 4: Click on the other Excel Table column that you want to enter the dynamic list into and go to the ribbon and choose Data > Data Validation > List

STEP 5: In the Source box enter the name you created in Step 3 and press OK or click in Source box, press F3 and select the named range from there

data validation

STEP 6: If you want to add extra data into your Excel Table list, hover with your mouse in the bottom right hand corner and when you see a double arrow, drag down.

STEP 7: Enter a new entry and this will automatically be updated in your drop down list

Drop Down List with Data Validation

One of the coolest features of Excel is to create a drop down list with your data.  It is one of the first things that I learned and use on a daily basis.

You can create a drop down list to eliminate manual entry and re-entry of data, as well as reduce data entry mistakes if you rely on other users entering data.

Data Validation lists can also be used on a form or a template where external users can choose from the drop down list to enter their choices.

DOWNLOAD WORKBOOK

STEP 1: Click on the cell that you want to enter your list in

STEP 2: Go to the ribbon and choose Data > Data Validation > List

STEP 3: Click in the Source box and select the range that includes your list of text/values and press OK

data validation list

Drop Down Menu In Your Excel Table

Apart from creating a simple Drop Down Menu/List or Data Validation List, you can expand this concept to include it in your Excel Table or Database.

That way you don’t have to do repetitive tasks like entering the same customer over and over again or copying and pasting time and time again.

See how easy it is to implement this in under 1 minute!


DOWNLOAD WORKBOOK

 

Latest Tutorials

Want to get better at Excel?

Join Our Excel Community

Get our Free Weekly Excel lesson plans that cover the must know Excel features and tips to make you better at Excel!
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]