how to create custom list in excel

2012-04-18 2018-08-08 https://www.launchexcel.com/wp-content/uploads/2017/04/logo-new-2-1.png Launch Excel 200px 200px

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.

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

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.
[tubepress video="g372qck60uQ"]

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.

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

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

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

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

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.

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'

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'

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

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

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

1. In Data Validation Settings choose to Allow "List"
2. Keep the two options checked "Ignore blank" and "In-cell dropdown"
3. 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)
4. 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.

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'

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

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)'.

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"

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.

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

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

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

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.

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

how to create custom list in excel

Source: https://www.launchexcel.com/excel-dropdown-list-data-validation/

Posted by: torresglin1958.blogspot.com

Share this post