Apply data validation to cells - Excel - Office Support - Office 365


This site uses cookies for analytics, personalized content and ads. By continuing to browse this site, you agree to this use.

Learn more

Sign in

Apps Install Account Training Admin

Apply data validation to cells Applies To: Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel Starter 2010

You can use data validation to restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is to create a drop-down list. Check out this video for a quick data validation overview.

Discover more Excel training at LinkedIn Learning

Download our examples You can download an example workbook with all of the data validation examples you'll find in this article. You can follow along, or create your own data validation scenarios. Download Excel data validation examples

Add data validation to a cell or a range Note: The first three steps in this section are for adding any type of data validation. Steps 4-8 are specifically for creating a drop-down List.

1. Select one or more cells to validate. 2. On the Data tab, in the Data Tools group, click Data Validation.

3. On the Settings tab, in the Allow box, select List.

4. In the Source box, type your list values, separated by commas. For example: a. To limit an answer to two choices ("Do you have children?" for example), type Yes,No. b. To limit a vendor's quality reputation to three ratings, type Low,Average,High. Note: These steps are generally only recommended for list items that aren’t likely to ever change. If you have a list that could change, or if you need to add or remove items over time, then you’re better off following the Best Practice step below.

Best Practice: You can also create list entries by referring to a range of cells elsewhere in the workbook. The most efficient way is to create your list, then format it as an Excel Table (from the Home tab select Styles > Format as Table > choose the Table Style that works best for you). Next, select the table’s Data Body Range, which is the portion of the table that has just your list, not the table Header (Department in this case), and give it a meaningful name in the Name Box above column A.

Now, instead of typing your list values in the data validation Source box, you add the name that you just defined, preceded by an Equal (=) sign.

The best thing about using a table is that as you add or remove items from your list, your data validation list will update automatically. Note: It’s best to put your lists on a separate worksheet (hidden if necessary) so that no one can edit them.

5. Make sure that the In-cell dropdown check box is selected. Otherwise, you won't be able to see the dropdown arrow next to the cell.

6. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. Note: If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.

7. Test the data validation to make sure that it is working correctly. Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

Notes: After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if the cell is wide enough to show all your entries. If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them. Remove data validation - Select the cell or cells that contain the validation you want to delete, then goto Data > Data Validation and in the data validation dialog press the ClearAll button, then OK.

Adding other types of data validation Examples of formulas in data validation Frequently Asked Questions

Do you have a specific function question? Post a question in the Excel community forum

Help us improve Excel Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.

See also More on data validation Video: Create and manage drop-down lists Add or remove items from a drop-down list Remove a drop-down list Discover more Excel training at LinkedIn Learning

Expand your skills E X P LORE TRA I NI NG

Get new features first J OI N OFFI CE I NS I DE RS

Was this information helpful?




Apply data validation to cells - Excel - Office Support - Office 365

This site uses cookies for analytics, personalized content and ads. By continuing to browse this site, you agree to this use. Learn more Sign in A...

558KB Sizes 3 Downloads 20 Views

Recommend Documents

Excel Glossary - Excel - Office Support - Office 365
An address can be a URL (Web address) or a UNC path (network address), and can include a specific location within a file

Basic tasks in Excel - Excel - Office Support - Office 365
Get started with basic tasks in Excel such as opening a workbook, entering and formatting data, calculating data, and tr

Display numbers as fractions - Excel - Office Support - Office 365
123 26/57, rounding to the nearest double-digit fraction value ... If no fraction format is applied to a cell, and you t

Featured Excel Templates - Office Templates - Office 365
Featured Excel Templates. Welcome to Excel Excel · Formula tutorial Excel · PivotTable tutorial Excel · Simple budget Ex

Introduction to queries - Access - Office Support - Office 365
You can use a query to answer a simple question, to perform calculations, to combine data from different tables, or even

How to check Office 365 service health - Office 365
View the health status of Office 365 services before you call support to see if there is an active service interruption.

Export notes as a PDF - OneNote - Office Support - Office 365
If you want to share some of your notes, but don't want to give someone full access to all of them, you can export notes

Office 365 Nonprofit - Microsoft Office
Empower staff and volunteers to succeed with the dynamic set of tools included in our Office 365 Nonprofit plans. Provid

Basic tasks in Visio - Visio - Office Support - Office 365
Each type drawing has templates, each with its own set of shapes that are ready to use. Just drag the shapes onto the dr

Create a project report - Project - Office Support - Office 365
With Project, you can create and customize striking graphical reports of whatever project data you want, without having