ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Validate with a Validation List: Create you own Validation List in Excel

Updated on June 21, 2011

Excel enables you to restrict the values a user can enter in a cell. This type of restriction can easily achieved by using validation list. Using validation list, you can easily validate cell value with a validation list. By restricting values, you ensure that your worksheet entries are valid. As the worksheet entries are valid, calculations are also valid.

At the time of data entry, a validation list forces any user using your worksheet to select a value from a drop-down menu. So, the user don't need to type and it's potentially prevent typing the wrong information to the worksheet during data entry.

Remember, validation lists can be numbers, employees, products, name of the country, name of the departments etc.

Steps to create a validation list in a cell

In the following, you will find the step by step solution to create a validation list in a cell:

Step 1: At first, click or select the cell on your worksheet in which you want to create a validation list.

Step 2: Click on the Data tab on Excel.

Step 3: Under Data Tools group in Excel, you will find Data Validation. Click on it.  Soon, Data Validation dialog box will be appeared.

Step 4: Click on the Settings tab in Data Validation dialog box.

Step 5: In the Settings tab, you will find several validation criteria option. From the Allow option select List option.

Step 6: Now, click and drag for selecting the valid entries. Alternately, type = and then the range number to add the valid entries.

Step 7: At last, click on OK button on Data Validation dialog box.

After finishing the above steps, you will find that Excel creates a validation list for the cell you selected. Thus applying the above steps, you can easily create a validate list of your desired cell.

Steps to create a validation list in a cell
Steps to create a validation list in a cell

Steps to paste validation list into a selected cells or columns:

In the previous steps, you just create a validation list for only one cell. Now, if you want to apply that validation list to a column, do you need to do all the previous steps again and again? Obviously not. There is a solution to do that. In the following steps you will learn the way to create a validation list for an entire column.

Step 1: Click on the cell that contains the validation list. (You already learn it in the previous steps. If you forget then re-read those steps.)

Step 2: Click on the Home tab.

Step 3: Click on Copy button or press Ctrl + C keys together.

Step 4: Now, select all the cells or column in which you like to place the validation list.

Step 5: Click the arrow button under Paste and a menu will be appeared.

Step 6: From those menu, click on Paste Special and soon "Paste Special" dialog box will be appeared.

Step 7: Click on Validation radio button.

Step 8: At last, click on OK button.

After doing the above steps, you will see that Excel places validation list in each cell which you selected below. Click any of the cell and the list will be appeared. So, when the users try to make an entry into any of the cells they can pick that form the list.

Steps to paste validation list into a selected cells or columns
Steps to paste validation list into a selected cells or columns

Summary of creating validation list and applying validation list:

To create a validation list, type the values you want to include into adjacent cells in a column or row. After you type your values, use the Data Validation dialog box to assign values to your validation list. Then copy and paste your validation list into the appropriate cells by using the Paste Special Validation option. You may want to place your validation list in an out-of-the-way place on your worksheet or on a separate worksheet.

@Written by rancidTaste


    0 of 8192 characters used
    Post Comment

    No comments yet.


    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at:

    Show Details
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)