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.
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
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
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.
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
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!