Working with Data in Excel Part 2: Fine Techniques for Excel Lists, Drop Downs and Data Validation

In Part 2 of this series we’ll look at Excel lists, drop downs and data validation. These are very important areas of Excel that you should master if you want to take your Excel skills to the next level.

If you missed Part 1 you can read it here: Working with Data in Excel Part 1: 10 Excel Data Entry Tips Everyone Should Know

In Part 2 you’ll find handy tips and techniques for speeding up data entry, as well as making sure that data is entered accurately. I’ll also show you a trick for quickly defining multiple Named Ranges very quickly, and explain how Data Validation in Excel can be easily bypassed – and what you can do about it.

[Image] Working with Data Part 2

I have divided the techniques into four different sections:

  1. Excel Built-in Lists – What are they and how to use them
  2. Excel Custom Lists – 3 Practical Use Cases
  3. How to Create Drop Down lists for Data entry using Data Validation
  4. Data Validation of E-mail address and Date of Birth

There is a downloadable workbook with worked examples that you can use to follow along. Download it here and get ready to learn some useful Excel techniques for lists, drop downs and data validation!


Download the Sample Workbook

[Image] Download Workbook In order to follow along with the video and article please download the Sample Workbook “Lists_Dropdowns_DataValidation.xlsx” by clicking here

Note: the sample workbook has been tested on Excel 2010 (32-bit, English language version) and may or may not work in other versions of Excel. In particular the keyboard shortcuts and instructions for adding custom lists may be different in your version of Excel. For instance if you use the Mac version of Excel you will find custom lists in Excel Preferences, under “Formulas and Lists”.

If you find that you can’t use this workbook on your version of Excel, please let us know using the comment section below.

Excel Tutorial Video – Excel Lists, Drop Downs and Data Validation

Here is our 20 minute video with various techniques for Excel Lists, Drop Downs and Data Validation.

Please note that the video was made using Excel 2010, hence the specific menu and look will be for Excel 2010. Excel 2007 is very similar but Excel 2003 and earlier will show differences.

You can scroll past the video highlights to the step-by-step written instructions “Microsoft Excel Tips – Lists, Drop Downs and Data Validation”. These are shown with simple screenshots and have no audio commentary.

Click on the video below to play it and listen to my audio commentary.

Excel Power Tips: Useful Techniques for Excel Lists, Drop Downs and Data Validation
Runtime
21:14
View count
135,022


Video highlights

Here are the video highlights:

Section 1 – Excel Built-in Lists

0:10 Excel’s Built-in Lists (Days of week, Months of year)

Section 2 – Excel Custom Lists

1:06 Three use cases for Custom Lists
3:57 How to create a Custom List (instructions for Excel 2010)

Section 3 – Dependent Drop-down Lists (Country, City)

5:08 Drop Downs for Data Entry
6:47 How to create Dependent Drop Downs with Data Validation and Named Ranges
9:38 TIP: How to define multiple Named Ranges
10:02 The “Slow” Way using the Name Manager
11:24 The “Fast” Way using CTRL + SHIFT + F3
14:09 Why do we need “Helper” columns?

Section 4 – Data Validation (E-mail, Date of Birth)

15:40 Data Validation of Email addresses
18:20 Data Validation of Date of Birth (you must be at least 15 years old)
19:48 Warning: Data validation can be easily overwritten by Copy ➜ Paste and what to do about this

What do you think?

Try working through these tips after you watch the video or even while watching the video (you can pause and rewind) using the sample workbook that you can download above.

And please share you comments to tell us if you have any favorite Excel data validation tips or links that I do not mention in the video or in the article below.

We all love to learn tips & tricks and it’s helpful to share! Use the comment section below.


Microsoft Excel Tips – Lists, Drop Downs and Data Validation Explained

Here’s the list of tips and techniques split into 4 sections:

  1. Excel Built-in Lists – What are they and how to use them
  2. Excel Custom Lists – 3 Practical Use Cases
  3. How to Create a Drop Down in Excel for Data entry using Data Validation
  4. Data Validation of E-mail address and Date of Birth

1. Excel Built-in Lists – What are they and how to use them

Microsoft Excel comes with four built-in lists:

  1. Sun, Mon, Tue, Wed, Thu, Fri, Sat
  2. Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
  3. Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
  4. January, February, March, April, May, June, July, August, September, October, November, December

NOTE: You cannot edit or delete a built-in list.

Built-in lists are useful because they allow you to quickly enter the days of the week or months of the year, and you can also perform sorting by days of the week or months of the year.

To quickly fill in the days of the week all you need to do is enter a day of the week and use the Fill Handle to drag down.

[Image] Fill Handle with Built-in Lists

Quickly fill in the Days of the Week and Months of the Year using the Fill Handle

Let’s say you type in “Mon” in cell B5. Make sure you have selected cell B5, then locate the Fill Handle which is a little black square at the bottom-right of cell B5.

Drag the Fill Handle downwards to B18 and you’ll see 2 weeks worth of days filled in. If you only want weekdays, you can select from the context menu to “Fill Weekdays” (this applies to Excel 2010) – or you can use the right mouse button to drag the fill handle and when you release the right mouse button you can select from the context menu that comes up.

And as well as dragging down, you can also fill right, left and up by dragging in the appropriate direction.

2. Excel Custom Lists – 3 Practical Use Cases

Even more useful than the built-in lists, you can create your own Excel custom lists and use them to quickly sort or fill in data. For example, if you want to sort or fill by the following lists, you need to create a custom list because there is no natural sort order.

  • High, Medium, Low
  • Large, Medium, Small
  • North, South, East, West
  • Regional Sales Manager, Department Sales Manager, Sales Representative
  • Automotive Products, Healthcare Products, Industrial Products, Finance, Human Resources, Production, R&D, Sales & Marketing
[Image] Sort Task List: High Medium Low

Sort a Task List in Excel using a Custom-defined List (High, Medium, Low)

I’m sure you’ll be able to find many more examples where you’d benefit from defining your own Excel custom lists (e.g. cities, countries, product names, department lists, business unit names).

For instance, Glenn Lloyd has a nice blog on Office Tips and Methods and he gives the example of managing shift assignments for the same group of people every week. Read Glenn’s article on “Excel Data Entry Tricks”

How to create an Excel custom list

There are 2 ways to create an Excel custom list. You can manually enter the list in the Custom Lists dialog box, or you can create a custom list based on a cell range.

Method 1 – Manually enter the custom list

[Image] Custom List Dialog Box

Create a new Custom List by selecting "NEW LIST" and typing in the list in the order your want it to sort e.g. High, Medium, Low - use Enter key to separate the list items

  1. In Excel 2010 click the File tab on the Ribbon (in Excel 2007, click the Office button)
  2. Click on Options (in Excel 2007, click on Excel Options)
  3. Select Advanced
  4. Scroll down to the section labelled “General”
  5. Look for the Edit Custom Lists button and click on it
  6. This brings up the “Custom Lists” dialog box.
  7. Select “NEW LIST
  8. Under “List entries” type out each list item, using Enter to separate list entries, and adding entries in the order you want Excel to sort the list
  9. Click on “Add” then click on “OK” twice to return to your workbook

Method 2 – Create a custom list based on a cell range

[Image] Custom List Import

Import a Custom-defined list from an existing range of cells

  1. In Excel 2010 click the File tab on the Ribbon (in Excel 2007, click the Office button)
  2. Click on Options (in Excel 2007, click on Excel Options)
  3. Select Advanced
  4. Scroll down to the section labelled “General”
  5. Look for the Edit Custom Lists button and click on it
  6. This brings up the “Custom Lists” dialog box.
  7. Import a list from cells by clicking on the Import Range button
  8. Select the cells containing your custom list
  9. Click on Import and check that Excel has added a new Custom list at the bottom of your custom lists
  10. Click on “OK” twice to return to your workbook

NOTE: A custom list can only contain text or text mixed with numbers. For a custom list that contains numbers only, such as 0 through 100, you must first create a list of numbers that is formatted as text.

How Custom Lists are Stored and How you can quickly Copy a custom list to another computer

When you create a custom list it’s stored on your computer and is available in other workbooks. If you use a custom list when sorting data, it’s also saved with the workbook, so it can be used on other computers.

But if you open the saved workbook on another computer, you don’t see the custom list that is stored in the workbook file in the Custom Lists dialog box, you only see the list in the Sort dialog box. The custom list stored in the workbook is not initially available for the Fill handle command.

However if you want to add the custom list stored in the workbook to another computer, you can do this with the following steps:

  1. Open the Sort dialog box
  2. Find the Sort Order column
  3. Select Custom Lists to display the Custom Lists dialog box
  4. Select the custom list you want to add
  5. Click Add and the list is added to the Custom Lists stored on the other computer

Now you can use the Fill handle to quickly add the custom list on the other computer.

3. How to Create a Drop Down in Excel for Data entry using Data Validation

[Image] Drop Down List - Country

Use an incell Drop-Down List to restrict entries to a predefined list of Countries

Drop down lists using Data Validation are useful for when you want to restrict data entry to a specific list of items. When you use Data Validation to create a dropdown list, the list items can be entered by directly typing them in the cell or they can be selected using the mouse from the dropdown list.

You can add an input message that pops up when the dropdown is selected, to guide the user as to what they can enter. You can also show a custom error message that appears when the user inputs an incorrect value.

[Image] Drop Down City Selection

Use a dependent incell Drop-Down List to restrict entries to a predefined list of Cities that is dependent on what Country is selected in the adjacent cell


Dependent drop-downs are a very nice extension of this idea. Let’s say you have two columns, the first being “Country” and the second “City”. When you select a Country using the drop-down in column A, you want the drop down in column B to be dependent on the Country selected.

We’ll take a look at how to achieve dropdown lists and dependent dropdown lists using Named Ranges inside Data Validation, together with the INDIRECT() function.

Remember if you haven’t got a copy of the sample spreadsheet you should download it here so you can follow along.

Step 1 – Create Drop Down lists for Data entry using Named Ranges to drive Data Validation

Here are step-by-step instructions for creating the drop down list you see in column B “Country” on worksheet ‘Data Entry’ in the sample download spreadsheet.

First you need to create the Named Range that will be used to generate the drop-down list of countries. This is done on a separate worksheet for clarity.


Create the Named Range “Countries” on worksheet ‘Data Validation’

[Image] Create Named Range "Countries"

Create the Named Range "Countries" using keyboard shortcut CTRL + SHIFT + F3 to quickly define a named range from selected cells

  1. Select worksheet ‘Data Validation’
  2. Select cell A1 and type in the word “Countries
  3. Fill out the list of countries in cells A2:A15 (Australia, Canada, Croatia, France etc.)
  4. Select the range A1:A15
  5. Press CTRL + SHIFT + F3 to create a Named Range from the selection
  6. Choose to Create names from values in the Top Row and click OK

Apply Data Validation to the “Country” column on worksheet ‘Data Entry’

[Image] Data Validation Button

Click on the Data Validation Button after selecting the range of cells you want to apply Data Validation rules

  1. Select worksheet ‘Data Entry’
  2. Select the range where you want to apply Data Validation dropdowns (in this case B2:B20)
  3. Click on the Data tab in the Ribbon
  4. In the Data Tools section, click on the Data Validation button
  5. [Image] Data Validation Settings

    In Data Validation settings allow a List, and set the source to be the Named Range "Countries" - TIP: inside the formula bar you can use the F3 key to bring up a list of Named Ranges to select

  6. In Data Validation Settings choose to Allow “List
  7. Keep the two options checked “Ignore blank” and “In-cell dropdown”
  8. Select the Source box and type “=Countries” which is the name of the Named Range you created above (or you can use the keyboard shortcut F3 to bring up a list of Named Ranges to insert, instead of typing out the name)
  9. Click OK

Step 2 – Create Dependent Drop Down Lists with Data Validation, Named Ranges and the INDIRECT() Function

Here are step-by-step instructions for creating the dependent drop-down list you see in column C “City” on worksheet ‘Data Entry’ in the sample download spreadsheet.

[Image] Screenshot of City drop-down selector

Screenshot showing the City Drop-Down List in action (remember the list of valid Cities is dependent on what Country is picked in Column B)

First you need to set up the Named Ranges that will be used to generate the drop-down list of cities. This is done on the worksheet ‘Data Validation’ for clarity.

Create the Named Range “Countries” on worksheet ‘Data Validation’

  1. Select worksheet ‘Data Validation’
  2. For each of the Countries listed in column A, create a list of Cities with the country name as the header on row 1
    E.g. for Australia – create a list of cities in column C with the heading Australia in cell C1
  3. Individually select each list of cities and create a Named Range using the Name Manager (CTRL + F3) or using the shortcut CTRL + SHIFT + F3 to create a Named Range from the selection (see above “Create the Named Range ‘Countries’ on worksheet ‘Data Validation’”)

TIP: If you want to save time (who doesn’t?!) then you must check out the fast way to do this: “How to Quickly Create a set of Named Ranges in Excel” and come back for the next steps.

Apply Data Validation to the “Country” column on worksheet ‘Data Entry’

[Image] Data Validation settings for City

In Data Validation settings allow a List, and set the source to be the formula =INDIRECT(SUBSTITUTE(B2," ","_"))

  1. Select worksheet ‘Data Entry’
  2. Select the range where you want to apply Data Validation dropdowns (in this case C2:C20)
  3. Click on the Data tab in the Ribbon
  4. In the Data Tools section, click on the Data Validation button
  5. In Data Validation Settings choose to Allow “List”
  6. Keep the two options checked “Ignore blank” and “In-cell dropdown”
  7. Select the Source box and type “=INDIRECT(SUBSTITUTE(B2," ","_"))
  8. Click OK

Explanation of the INDIRECT() formula

The INDIRECT() formula tells Excel to look at the range inside the brackets and treat that as the name of the range.

=INDIRECT(B2)

Let’s say that cell B2 contains the word “Australia”. When you use the formula =INDIRECT(B2) you ask Excel to look at cell B2 and it finds “Australia”. Then it looks up the Named Range “Australia” and returns the contents of that range.

This works fine in the Data Validation dropdown for all the countries which are one word long, but when we get to a country that’s made of 2 words or more Excel has trouble.

In Excel it’s not possible to use spaces inside Named Ranges, and if you use the CTRL + SHIFT + F3 shortcut to define a Named Range based on the header row “United States” Excel will rename the range “United_States” where the space is replaced with an underscore.

Value on worksheet "United States" ➜ Excel converts to Named Range "United_States"

To get around this behavior, we must use a SUBSTITUTE formula to ask Excel to search the Named Range in cell B2 and substitute any spaces with underscores “_”.

=INDIRECT(SUBSTITUTE(B2," ","_"))

Once we do this, Excel can handle Country names that are made of two words or more.

How to Quickly Create a set of Named Ranges in Excel

Here’s a technique you can use to quickly define a set of Named Ranges all in one go. It doesn’t matter how many Named Ranges you need to define, the steps are the same whether you want to create one Named Range or ten Named Ranges.

Don’t be put off by the long explanation – once you understand the technique and have set up helper columns, it only takes a few seconds to execute which is great when you need to add or remove items to your Named Ranges.

Step 1 – Set up Helper Columns on the Data Validation sheet

To use this technique you must first set up your data with “Helper columns”. Open the sample workbook (if you haven’t downloaded it yet click here) and go to the worksheet called ‘Data Validation’.

[Image] Worksheet with Helper Columns

Worksheet 'Data Validation' with "blank" helper columns B, D, F etc.

You’ll see that the columns are set up like this:

  • Column A – Countries list
  • Column B – Helper column
  • Column C – City list for Australia
  • Column D – Helper column
  • Column E – City list for Canada
  • Column F – Helper column
  • etc.

The Countries list is set up in Column A, there is a Helper column in column B, then the City list for Australis is set up in Column C and so on.

Each helper column looks ‘blank’ but the first row of every helper column is filled with a formula that returns nothing – so although it appears blank to us, Excel sees it as a formula.

The formula I used was ="" i.e. two double quotes with nothing inside

Later on we will ask Excel to select “Constants” and so we get just the ranges we want i.e. the cells containing Countries and Cities.

Without the helper columns Excel might sometimes pick out ranges that we don’t want to treat separately. For example in the sample workbook there is a worksheet without helper columns ‘Data Validation (error)’.

[Image] section3-8

Worksheet 'Data Validation (error)' with no helper columns

Go to the worksheet ‘Data Validation (error)’ and follow these instructions:

  1. Select cell A1
  2. Press CTRL + SHIFT + 8 to select the current region (Excel should select A1:O26)
  3. Press CTRL + G to bring up the Go To dialog box
  4. Click on Special to bring up the Go To Special dialog box
  5. Select “Constants” to ask Excel to select all constants, then click OK

Check the selected outline and you should see an error where Puteaux, Osnabruck and Pune are treated as header rows, which means when we press CTRL + SHIFT + F3 to define named ranges from the selection we get three incorrect named ranges called “Puteaux”, “Osnabruck” and “Pune”

[Image] Selection includes incorrect ranges

As we do not have helper columns on worksheet 'Data Validation (error)' we get three incorrect named ranges "Puteaux", "Osnabruck" and "Pune"

Step 2 – Define Named Ranges quickly using Go To Special and CTRL + SHIFT + F3

After defining helper columns in Step 1, we can proceed to quickly define the required named ranges using a few keyboard shortcuts.

[Image] How to Quickly Define Named Ranges

You can quickly define Named Ranges after setting up helper columns

  1. Select the worksheet ‘Data Validation’
  2. Select cell A1
  3. Select the current region using CTRL + SHIFT + 8
  4. Press CTRL + G to bring up the Go To dialog box
  5. Click on the “Special” button to bring up the Go To Special dialog box
  6. Select “Constants” and press the ENTER key (or click the “OK” button)
  7. Now define the Named Ranges using your selection by pressing CTRL + SHIFT + F3
  8. Excel will ask you to “Create names from values in the:”
  9. Make sure only “Top row” is selected, then press the ENTER key (or click the “OK” button)
  10. Check that the correct named ranges have been defined by opening the Name Manager with CTRL + F3
[Image] Screenshot of Name Manager

Name Manager showing that correct named ranges have been defined

Note: in English versions of Excel 2007 / 2010 you can bring up the Go To Special dialog box using this keyboard shortcut ALT ➜ H ➜ F ➜ D ➜ S and while it is quite a long sequence (5 key presses) it’s actually quite easy to remember if you realize the F, D and S are placed under the index, middle and ring finders of your left hand (assuming you can touch type and place your left index finder on the F key).

How to Quickly Redefine an existing set of Named Ranges

When you change any of the Country or City data used in the data validation list you can simply repeat the same steps to redefine all Named Ranges.

Let’s say you need to add a new city to the list of cities in India. Go through the same steps to quickly create a set of Named Ranges:

  1. Select the worksheet ‘Data Validation’
  2. Select cell A1
  3. Select the current region using CTRL + SHIFT + 8
  4. Press CTRL + G to bring up the Go To dialog box
  5. Click on the “Special” button to bring up the Go To Special dialog box
  6. Select “Constants” and press the ENTER key (or click the “OK” button)
  7. Now redefine the Named Ranges using your selection by pressing CTRL + SHIFT + F3
  8. Excel will ask you to “Create names from values in the:”
  9. Make sure only “Top row” is selected, then press the ENTER key (or click the “OK” button)
  10. Excel will ask you whether you wish to Replace existing definition of “India”
  11. Press the ENTER key (or click the “OK” button) to accept

Just make sure that you do not have an empty cell in the data columns between country names or city names, otherwise Excel will stop at the empty cell and create an incorrect named range.

4. Data Validation of E-mail address and Date of Birth

Here are some reasons you should use data validation in your spreadsheets:

  • Restrict entries to a specific type of data (whole numbers, e-mails)
  • Restrict entries to a specific range of values (min – max for dates and text length)
  • Prevent data entry mistakes on your spreadsheet
  • Display a drop down list to make it easier for users to select the correct data (see Section 3 – How to create a Drop Down in Excel)

Excel Data Validation Basics

We won’t cover the basics of Data Validation here, as there is very comprehensive coverage of Data Validation techniques from the basic to the advanced at Debra Dalgleish’s site “Contextures“. Please note this is an external website and I have no control over the contents there (however Debra is really very good with Excel and you’ll learn quite a lot if you head over).

You can find a link to the introduction article here. Simple Data Validation Examples are given on this page. More advanced examples are available here (custom criteria) and here (hide previously used items in dropdown).

Instead, we’ll cover 2 specific techniques in this article. Remember if you haven’t got a copy of the sample spreadsheet you should download it here, so you can play around with the data validation settings on worksheet ‘Data Entry’

Technique 1: Easy E-mail address validation

[Image] section4-4 circle invalid data button

E-mail address validation showing popup providing user instructions

The first technique we’ll look at is how to validate e-mail addresses. This isn’t a foolproof method but it comes close enough that I’m happy to share it with you. My guess is that it should catch at least 80% of invalid email address entries.

Email validation rule

The technique is based on a simple rule:

Every e-mail address must have an “@” symbol that’s followed by a “.” period (or full stop) that does not come straight after the “@” symbol.

The rule will reject the following types of incorrect “e-mail address”:

fake-email
fake-email@
fake-email.com
fake-email@.com

Obviously this still leaves a lot of possible errors such as the following emails that are allowed by the above rule but are not valid e-mail addresses:

name@domain@another.com
name@domain..com

Current versions of Excel (when this article was written in April 2012 these included: Excel 2010, Excel 2007 and yes even Excel 2003) also catch out another class of incorrect email address that’s not explictly stated by this simple rule – just due to the legacy usage of the “@” symbol. When Lotus 123 was the de facto standard in spreadsheet programs in the 1980s, the “@” sign was apparently the way to indicate the start of a formula.

This means that typing “@domain.com” returns an error message “That function is not valid” because Excel believes you are trying to write a formula using the function “domain”. And so it does not allow you to enter the incorrect e-mail address “@domain.com”

So the rule is not perfect but is close enough that it should catch around 80% of invalid “email addresses”

Implementing the Email rule using Excel Data Validation

To put this rule into action, you need to use the following formula in a custom Data Validation formula.

=SEARCH(".",D2,(SEARCH("@",D2,1))+2)

The formula is TRUE (and the Data Validation passes OK) if the contents of cell D2 contains at least one “@” symbol (SEARCH("@",D2,1)) and cell D2 contains at least one “.” symbol that’s at least the 2nd character after the “@” symbol (SEARCH(".", D2, [previous search] +2)

Tip: Use the F2 key to toggle formula entry mode in the Data Validation dialog box

When you enter a formula in the Data Validation dialog box, you will find that the arrow keys do not move you left and right in the formula.

Instead using the arrow keys will select cells in the direction you press. Don’t let this confuse you!

If you want to use the arrow keys to edit the formula, press F2 to toggle formula entry mode. You can press F2 again to get back to cell selection mode.

And if you make a mistake when editing a long formula in the Data Validation box that’s almost correct, you can click on Cancel (or press the Escape key) to cancel any changes you make. This can be easier than starting again from the beginning, especially if you have just replaced the entire formula with a cell reference after inadvertently using the arrow keys!

More about validating email addresses in Excel

If you’d like to read more about using Excel to validate email addresses you could check out these articles (on external websites so the content is not under my control):
How to Use Excel to Filter Invalid Email Addresses at TimeAtlas.com
Regular Expression to validate Email address at ExcelExperts.com

As far as I’m aware it’s not possible to use a custom defined function in data validation with Excel 2010 or earlier. If someone else knows differently please let us know using the comment section below!

This means that you cannot define a custom function in the VBE editor called IsEmailValid() and define specific REGEX rules inside that function, then call that from the data validation tool.

Otherwise it would be possible to apply more exact email validation rules using REGEX (regular expressions). Not being a professional programmer I know next to nothing about regular expressions – just that they can do amazing things with text searches.

For a very detailed and technically demanding article on using regular expressions for email validation you could check out this page (not for beginners!):
http://www.regular-expressions.info/email.html

Technique 2: Date of birth validation – you must be at least 15 years old

[Image] Screenshot showing Date Validation

Date of Birth validation showing popup providing user instructions

The second technique we’ll look at is how to validate dates of birth where you need to make sure that the person’s is at least a certain number of years old.

In our example we’ll say that we can allow people who are at least 15 years old as of today. Anyone younger cannot be entered. We’ll also say that the date of birth must be after January 1st 1900 to catch some potential typos.

Formula to enter into Data Validation

=DATE(YEAR(TODAY())-15,MONTH(TODAY()),DAY(TODAY()))

This formula returns a date that is 15 years earlier than today, with the same month and same day as today. The DATE() function takes three arguments:

  1. Year value
  2. Month value
  3. Day value

The TODAY() function returns a number that represents today’s date in Excel. Using YEAR() and MONTH() and DAY() converts the number for today’s date into the corresponding Year, Month and Day values to use inside the DATE() function.

Putting the formula into Data Validation

To put this formula into action, you need to apply Data Validation to the range of cells you wish to restrict. In the Data Validation dialog box under Validation criteria:

Allow: Date
Data: Between
Start date: =1/1/1900
End date: =DATE(YEAR(TODAY())-15,MONTH(TODAY()),DAY(TODAY()))

Recommendation: Use ‘Circle Invalid Data’ to double check

Sadly the Data Validation feature in Excel is very easy to over-ride. You can do this by pasting data directly into the cell, or by choosing Home➜Clear➜ClearAll (Excel 2003: Edit➜Clear➜ClearAll)

You can double check that the Data Validation rules have not been circumvented by doing these 2 things:

  1. Check that the Data Validation rules have been applied to the correct cells
  2. Use the feature ‘Circle Invalid Data’

To check that Data Validation rules have been applied to the correct cells, first select a cell that you know has the correct Data Validation. Then press CTRL + G to bring up the Go To dialog box, click on “Special” to bring up the Go To Special dialog box, and select the option “Data Validation” ➜ “Same“.

This will highlight all the cells with the same Data Validation rules as the currently selected cell. You’ll be able to see if there are gaps where Data Validation is not being applied.

To use the feature ‘Circle Invalid Data’ click on the Data tab (Excel 2010 Ribbon), then click on the Data Validation drop-down and select ‘Circle Invalid Data‘. This will put a red oval around any cells that don’t meet the defined data validation rules.

[Image] Screenshot showing Invalid Data Circled in Red

Screenshot showing Invalid Data Circled in Red - bad@email addresses were copied and pasted over cells existing Data Validation!

Subscribe to our email newsletter

If you like Excel tips & tricks why not subscribe to our free weekly newsletter, which you can do by filling out the form below the sharing buttons.

And remember to share this great free resource with your friends and colleagues using the sharing buttons – they’ll love you for it!

Cheers – Victor

Image Credits:
spreadsheet 3 courtesy of “lustfish“.


About-Launch-ExcelWelcome to Launch Excel
If this is your first time to LaunchExcel.com - Welcome to Launch Excel. My name is Victor and I'm the Chief Teaching Officer of Launch Excel, a website I started to help you learn how to use Microsoft Excel more effectively. Check out my welcome page created specially for you: Click here to view Welcome Page


36 Responses to Working with Data in Excel Part 2: Fine Techniques for Excel Lists, Drop Downs and Data Validation
  1. Nabeel
    May 24, 2012 | 9:08 am

    Thanks a Lot Victor for making and sharing this excel magic………….

    • Victor
      May 24, 2012 | 10:57 am

      Hi Nabeel – thanks for your comment ☺
      Hope you enjoy the rest of my material – Cheers, Victor

  2. Nabeel
    May 28, 2012 | 12:35 pm

    Hi victor – Sorry for late response to your comment, i think i didn’t get the notification correctly. Really appreciate your effort, as a beginner i learned a lot from your presentation.Keep Going. God Bless you…

  3. Gerard
    May 30, 2012 | 8:08 pm

    Victor,

    At first, thanks for the usefull, practical information.
    When I use dropdown boxes in combination with named ranges I often use the formula in combination with OFFSET and COUNTA. Then the boxis filles only with cell that have content. As soon as you add a new item (city i.e.) it’s automatically available. I don’t get this to work in combination with your way of filling the dropdown box. Is this not possible in this case?

    Still I love your article.

    Regards

    Gerard

    • Victor
      May 30, 2012 | 8:54 pm

      Hi Gerard,

      You are right that OFFSET and COUNTA can be used to create a dynamic list that expands as you enter more data. My article aims to keep things simple, with the aim of making it easier for the majority of Excel users to understand. Dynamic formulas are handy but can sometimes be hard to audit (especially if a spreadsheet is used by people of varying levels of ability).

      For anyone who is interested in more formula-based approaches, I recommend 2 great articles from Contextures explaining how to create dynamic dropdowns using different combinations of OFFSET, COUNTA and INDEX:
      Excel Data Validation — Create Dependent Lists (See using dynamic lists)
      Excel Data Validation — Create Dependent Lists With INDEX (allows any number of dependent lists)

      Thanks for your comment and I look forward to more comments from you :)

      Cheers
      Victor

  4. Camilo
    July 21, 2012 | 2:28 am

    Hello Victor,
    Do you know how make a drop list with special character. For example a check mark, an “x” mark and N/A

    • Victor
      July 23, 2012 | 9:04 am

      Hi Camilo

      From what I can see Excel does not have built-in functionality to create drop down lists with a mixture of special characters and normal characters (like N/A). As an alternative you can check out my 2 minute video on how to add checkmarks using the Conditional Formatting tool:

      If you really want to create a drop down with a mix of special characters and normal characters, the following articles might be useful for starting you off with a custom VBA solution:

      Ozgrid Re: Choose Tick Or Cross in Drop-Down
      http://www.ozgrid.com/forum/showthread.php?t=62059

      Excel Hero: Excel Dynamic Checkmark
      http://www.excelhero.com/blog/2011/03/excel-dynamic-checkmark.html

      Victor

  5. Tina Dravecky
    July 31, 2012 | 3:49 pm

    Your information is helpful, although my issue is different. Using the Data Validation tool, I want to create sub-lists within the dropdown option, is this possible?

    Example: I have a list of 75 members; therefore, I would like to group the member names and title it MEMBERS, so if they select MEMBERS then a sub-list would show all the member names to choose from. Then within that same dropdown, I would like an option for SELLERS and that to will have a sub-list with the seller names to choose from. It this possible? If yes, how is it achievable?

  6. izal
    August 14, 2012 | 5:07 pm

    Thanks victor for sharing your knowledge.

    According to our user case, using data validation tools in excel posses several flaws sometimes. Hence we are propelled to build a web app so called Podbox. It acts as a complimentary application for various software. And one of the features is data validation.

    • Victor
      August 14, 2012 | 5:28 pm

      Thanks for sharing the link to Podbox. I checked out the “Customers” profiles and was interested by these lines:
      http://pod-programming.com/customers/

      We expect the following profiles would be interested in the podbox:

      Marketing officer/manager
      You need to merge, clean heterogeneous data (for example your prospects and customers).

      Administrative officer/manager
      You use many dedicated apps (HR, accounting, sales) and many spreadsheets. You waste a lot of time to check everything.

      Ms Excel addict
      You deal with lists and need to clean and merge the data with the corporate ones.

      Data journalist
      You need to merge several (open) data sources and share the result.

      Pro with several software packages and mobility
      You waste time in copy/paste. You open many apps and websites to investigate on your data. You need a complete and clear view of all your data in one place, wherever you are.

      Team member/leader
      You want to share collaborative and qualitative structured data with a team, maybe using different software packages to do their job. Everyone needs to see the information, at the same time.

      • izal
        August 16, 2012 | 9:54 am

        Victor, thanks for checking out our website.
        we are currently working new features which may expand our customer profiles.

  7. [...] our previous article we saw step by step instructions for how to create drop down lists for data entry using data [...]

  8. Justin Kelly
    October 9, 2012 | 3:37 am

    I would like to make a drop down list but have it with an IF statement. Meaning that if an option isnt selected in the drop down, it is filled with “Not Applicable”. I am doing this for pivot table presentation rather than saying (blank).

    Please help!

    • Victor
      October 9, 2012 | 8:21 am

      Hi Justin – often simple solutions work best, so here’s a suggestion. You should add an option for “N/A” in the actual data validation drop-down list. Then, everywhere you need to put in “N/A” you select just those cells and copy/paste in “N/A” to fill them all. How does this sound, and does it meet your needs?

      Victor

  9. Darshan
    October 16, 2012 | 1:38 am

    How to put data validation for a number that is exactly 6 digits but can start with zero. For eg. 032303 would be valid but 32303 would be invalid.

    Any help would be appreciated. Thanks.

    • Victor
      October 16, 2012 | 3:06 am

      Hi Darshan – thanks for the question. I don’t have an immediate answer to this problem, so I’d like to open this up and ask if anyone else can suggest a solution?

      Cheers
      Victor

      P.S. I am currently travelling abroad and will not be able to answer further comments for a couple of weeks. But you are definitely welcome to leave more comments and questions as someone else may be able to offer a solution. And I will definitely read every comment when I’m back.

  10. Peter Bartholomew
    October 16, 2012 | 9:48 am

    Hi Darshan.

    The first issue is do you see the number as being stored and processed as a number or is it really a text string that just happens to comprise of digits 0-9?

    If it is a number then the validation test is simply that it lies between 0 and 1000000. A number format of “000000″ will put in leading zeros if the user doesn’t.

    Conversely, if the cell is formatted as text, you will need a custom validation that checks whether the string has 6 characters and, secondly, whether the ‘value’ function gives an error or not. To validate the cell A1

    =AND(NOT(ISERROR(VALUE(A1))),LEN(A1)=6)

    • Darshan
      October 16, 2012 | 10:06 am

      Worked perfectly well. Thanks. Mine was the second case when number is stored as text because it can start from zero.

      Thanks again.

  11. Francisco
    October 16, 2012 | 5:13 pm

    How can I create a third drop down menu?

    Thanks

  12. Mr. Huff
    December 3, 2012 | 10:33 pm

    Thank you for the easy solutions to the drop down lists. This streamlined my student progress reports to save me hours.

    I used an auto fill function for cells in ‘report card’ sheet that I wanted filled from horizontally organized data in the ‘student report data’ sheet. By selecting the name from the drop down and having this formula in each cell with the correct reference it auto-filled the report card.

    =IF($C$5:$D$5=”",”",INDEX(‘Student Report Data’!C:C, MATCH($C$5:$D$5, ‘Student Report Data’!$B:$B, 0)))

    I have no idea how it actually works or why (I copy and pasted from a site I can’t find again) and I had to go through each function line and edit to point it to the appropriate column. I was wondering if you could share some simplified solutions to auto-fill or share a more comprehensive auto-fill function resources?

    Thanks again for your great work!

  13. Victor
    December 13, 2012 | 5:04 pm

    Hi Mr. Huff

    Thanks for your comment, and I’m happy you saved lots of time using the data validation drop down lists I wrote about.

    The formula you have is a Lookup formula (INDEX/MATCH) wrapped in an IF function to show blanks “” if there is no value to lookup.

    Check out Charley Kyd’s explanation of INDEX/MATCH to see an explanation of how it works:
    http://exceluser.com/blog/465/excels-best-lookup-method-index-match.html

    Charley describes INDEX/MATCH as the best general purpose lookup method in Excel, and I agree that it’s very powerful.

    Feel free to send me a copy of your spreadsheet (with private or confidential information removed) and I can take a look at how it’s been put together and whether I can suggest improvements.

    I am planning to create some videos on Lookup Functions, including VLOOKUP and INDEX/MATCH. Understanding how to use Lookup Functions is key to doing the auto-fill for your report card.

    Cheers
    Victor

  14. Yin
    March 1, 2013 | 4:01 am

    Thanks for tutorial!

    I tried but I encounter a problem at the drop down list. Take your sample to illustrate the problem I encounter, for some of the country, I do not have a city as a selection. So I define it as N/A as a selection.

    After I follow the step you given, when I select the country (with N/A as city), there is no such option for me to select and excel did not show me anything. This do not happen to all country that with N/A because some of it actually display the N/A when I select the drop down at the city column. There are those country I already define the city it also can’t display on the next drop down list. So I’m not sure what is the problem.

    Appreciate your advise.

    Yin

  15. buy cialis
    March 31, 2013 | 1:31 pm

    Every weekend i used to pay a quick visit this web site, because i want enjoyment,
    as this this website conations in fact nice funny material
    too.

  16. Vlad
    April 14, 2013 | 10:57 am

    Hi,

    I tried your formula for email data validation, however I keep receiving this error alert when I press OK.

    I am sure that I have typed the right formula!

    Can you please help?

    “The formula you typed contains an error.
    - For information about fixing common formula problems, click Help.
    - To get assistance in entering a function, click Function Wizard (Formulas tab, Function Library group).
    - If you are not trying to enter a formula, avoid using an equal sign(=) or minus sign (-), or precede it with a single quotation mark(‘)”

  17. dell ie discount codes
    April 17, 2013 | 2:56 am

    We stumbled over here different web page and thought I might
    as well check things out. I like what I see so now i’m following you. Look forward to exploring your web page repeatedly.

  18. Gregory Meyerowitz
    June 14, 2013 | 11:41 am

    There is a fault with the link to the part 2 excel lists, drop down and data validation spreadsheet (countries, email, etc) as the link takes you to the part 1 drop down list. Please forward me the correct files

    Thanks

  19. Andrea
    June 28, 2013 | 4:03 am

    Hi I am hoping anyone can help me with this, here’s my problem:

    IF A=”1″ B should be equal to “1″ also. else, B should have a list of values to choose from. (ex. C1:C5)

    • Peter Bartholomew
      June 28, 2013 | 1:34 pm

      Andrea. I think there are two distinct avenues to follow depending on what you wish to happen when A=”1″. With VBA you could detect that A has been set and insert 1 into B, overriding any previous user selection. With worksheet formula alone you could change the contents of the validation list to show “1″ only but B would remain unchanged until set by the user.

      • Andrea
        July 3, 2013 | 8:06 am

        Sorry but im not that good in VBA. can you give me the code for this? Really appreciate your help.

  20. Peter Bartholomew
    July 3, 2013 | 11:04 am

    Hi Andrea

    The following VBA will check the contents of cell A1 whenever a user changes a value on the worksheet.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If [A1] = 1 Then [B1] = 1
    Application.EnableEvents = True
    End Sub

    To enter the code you can use Alt/F11 to open the code window and double-click the appropriate module e.g. “Sheet1(Sheet1)”. Select “Worksheet” in the left dropdown and “Change” in the right. Then insert the 3 lines of code into the Sub.

    • Andrea
      July 4, 2013 | 10:50 am

      Got it. Thank you very much it’s working. Another thing, my problem is like this: if A1 = “1″ then B1, C1, D1, E1, F1 should be equal to “1″ also. i’m trying to place ‘And’ but it’s not working. maybe im doing something wrong? Please help. also can you help me loop this to all rows such that the rule will also apply to A2, A3, A4… etc… (example if A2 = “1″ then B2, C2, D2 = “1″ also)

      • Peter Bartholomew
        July 4, 2013 | 4:47 pm

        @Andrea

        Does the code below do what you want?

        Private Sub Worksheet_Change(ByVal Target As Range)
        Dim testRange As Range
        Dim dataCells As Range
        Dim c As Range
        Application.EnableEvents = False
        ‘set up test range as the intersection of column 1 and target
        Set testRange = Intersect(ActiveSheet.Range(“A:A”), Target)
        If Not testRange Is Nothing Then
        ‘Process any changed cells contained in Column 1
        For Each c In testRange
        ‘Define a range comprising 4 cells, starting one to the right
        Set dataCells = c.Offset(0, 1).Resize(1, 4)
        If c.Value = 1 Then
        ‘If the cell contains 1, set the contents to 1
        dataCells.Value = 1
        Else
        ‘If the cell 1, clear the contents
        dataCells.ClearContents
        End If
        Next c
        End If
        Application.EnableEvents = True
        End Sub

  21. Andrea
    July 8, 2013 | 5:52 am

    Thank you very much Peter! You’re truly a genius.

  22. protein shakes
    July 14, 2013 | 2:09 pm

    I just discovered this blog via Bing. You’ve got some practical tips today. That is seriously crazy. Thanks for providing this information!!