ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer How-Tos & Tutorials

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.