Excel Drop Down Lists - Data Validation (2023)

Excel Drop Downs From List on Different Sheet

In this workbook, there's a sheet where people will enter order information. They'll put in a date, and then a product, and the quantity sold.

To make it easier for people, in the Product column, instead of them typing a product name, which could result in errors or invalid products, we're going to create a drop-down list, so they just have to select a product instead of typing it.

List on Different Sheet

On a different worksheet, on the Products sheet here, there's a list of products and we'll use that to create the drop-down list.

Because this is on a different worksheet, we're going to have to name this list first before we can use it for the drop downs.

Name the List

  • I'm going to select all the cells that have product names, and then click in the name box
  • And I'm going to type a one-word name for this. I'm going to call this ProductList
  • And then I'll press the Enter key to finish naming that range.
  • And you can see the name up here now.

If I select a different cell, and select ProductList, it goes to that list right away. So this is a name in the workbook now

Create Drop Down Lists

And I can go back to the Orders sheet

  • And I'll select all the cells where I'd like that drop-down to appear.
  • Then on the Data tab of the ribbon, in the Data Tools section, I'll click, at the top of this Data Validation command, and that opens this window.
  • Under Settings, for Allow, I would like a list.
  • As the Source of this list, I could type an equal sign and the name of the list, but an easier way is on your keyboard, press the F3 key, and that opens up a Paste Name window.
  • I'll click on the list I want to use.
  • Click OK and you can see it now, just as if we had typed it.
  • Click OK

And now each of these cells has a drop-down list.

Arrow in Cell

The arrow only appears when you click on the cell. You can only have one drop-down visible at any time.

You can't make these appear all the time but you can then click that arrow and select one of the products and it'll automatically then appear in the cell.


Is there a limit to list data validation in Excel? ›

There are limits to the number of items that will show in a data validation drop down list: The list can show up to show 32,767 items from a list on the worksheet. If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators.

How do I extend data validation to more cells? ›

How to copy Excel data validation rule to other cells
  1. Select the cell to which the validation rule applies and press Ctrl + C to copy it.
  2. Select other cells you want to validate. ...
  3. Right-click the selection, click Paste Special, and then select the Validation option. ...
  4. Click OK.
Apr 5, 2023

How do I increase the size of a data validation list in Excel? ›

The only way to increase the size of a Data Validation drop-down list is to increase the zoom percentage at which you view the sheet.

How do I ignore blanks in data validation list in Excel? ›

Select the cells where the data validation is set, and then in the Ribbon, go to Data > Data Tools > Data Validation. This option is available with any validation criteria be it whole number, decimal, list, date, time, text length or a custom format. Make sure Ignore Blank is checked and then click OK.

How do I make a drop down list show more than 8? ›

By default, a data validation drop-down list only shows 8 items in the list when clicking the drop-down arrow. So you need to drag the scroll bar down for viewing more values in the list.

Why is my Data Validation not working? ›

If data validation isn't working, make sure that: Users are not copying or filling data - Data validation is designed to show messages and prevent invalid entries only when users type data directly in a cell. When data is copied or filled, the messages do not appear.

How do I allow multiple selections in Data Validation in Excel? ›

Go to Data –> Data Tools –> Data Validation. In the Data Validation dialogue box, within the settings tab, select 'List' as Validation Criteria. In Source field, select the cells which have the items that you want in the drop down. Click OK.

How do you apply a drop-down list to an entire column? ›

Select the cells that you want to contain the lists. On the ribbon, click DATA > Data Validation. In the dialog, set Allow to List. Click in Source, type the text or numbers (separated by commas, for a comma-delimited list) that you want in your drop-down list, and click OK.

Why is my drop-down list so small in Excel? ›

The length of the drop-down box is not big enough to display the entire text. What is this? The simple solution is to widen the column that the drop-down box is in. You can adjust the column manually by dragging the border of the column header.

How do I increase the size of the text in a drop-down list in Excel? ›

Re: Font size in a drop down list
  1. Highlight the cells you want to format.
  2. Invoke the "Format Cells..." command. ...
  3. In the dialog box that appears, activate the "Alignment" tab.
  4. Click the "Fit to cell size" option. ...
  5. Confirm your definition with the OK button.

Does Data Validation increase file size? ›

You may also consider reducing the number of formatting variations used in a workbook and the number of times you use data validation. These can both inflate the size of a workbook, so you'll especially want to check that formatting and data validation is not being applied to empty cells.

How do I remove blanks from Data Validation drop-down list? ›

Remove Blanks Quickly

From the window that opens, select the Blanks option and hit OK. Now, right-click a selected cell, which will be a blank one, and click Delete. Hit OK and that's it! Now that all blanks are gone, we can create the Data Validation dropdown list without any blanks.

How do I allow blanks in Data Validation list? ›

In the Data Validation dialog, select List from the Allow drop-down list, select the list including the blank cell you want to create data validation list based on and uncheck the Ignore blank option. 4. Click OK. Now you will see the first option in the data validation list is blank.

What is a dynamic drop-down list? ›

It's a list that we can select from in order to filter attendance data by department. The source of this dynamic data validation list has been created using the UNIQUE function. UNIQUE is a Dynamic Array Formula that returns all the unique values from a data range. In other words, it removes duplicates from a data set.

How do I allow multiple selections in a drop-down list? ›

How to make Excel drop down with multiple selections
  1. Select one or more cells for your dropdown (D3:D7 in our case).
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. In the Allow drop-down box, select List.
  4. In the Source box, enter the formula that indirectly refers to Table1's column named Items.
May 5, 2023

How do you expand a drop-down list? ›

Shortcuts for expanding drop-down list. Select a cell that you want to expand its drop-down list. Press Alt + down arrow button, then the drop-down list is expanded. Press down arrow to choose the value in drop-down list that you want to fill in cell, press Enter key to fill it in cell.

Why do I keep getting a validation error? ›

The below validation errors are the most common and will respond with some details, including a list of validation messages. Validation errors typically occur when a request is malformed -- usually because a field has not been given the correct value type, or the JSON is misformatted.

What are the 3 types of data validation in Excel? ›

Time - The user should enter a time. Text Length - It validates input based on the length of the data. Custom - It validates the user input using a custom formula.

Does validation stop all data entry errors? ›

Data validation is the process of checking the accuracy and quality of data before it is entered into a database or a spreadsheet. Data validation can help you avoid errors, inconsistencies, and duplicates that can affect your analysis and reporting.

Can you do data validation with multiple selections? ›

Instead of limiting the drop down list to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections. With a few adjustments to the VBA code, you can display all the selected items across a row, or down a column, or keep them in a single cell.

How do you use data validation for multiple columns? ›

How to create a multi column data validation list in Excel
  1. Name : ProductList.
  2. BorderColor : Menu bar.
  3. ColumnCount : 4.
  4. ColumnWidths : 100 pt;80 pt;80 pt;100 pt.
  5. DropButtonStyle : 1 or 2.
  6. LinkedCell : ProductName.
  7. ListFillRange : ProductTable.
  8. ListWidth : 390 pt.

What is cascading dropdown? ›

A cascading drop-down list is a series of dependent DropDownList controls in which one DropDownList control depends on the parent or previous DropDownList controls. The items in the DropDownList control are populated based on an item that is selected by the user from another DropDownList control.

How do I create a dynamic Data Validation list? ›

To create a dynamic list, you need to specify which column of the table contains the information to be used in the drop-down list by naming the range of cells. I want Company A to Company H to appear in my drop-down list. Select cells A2 to A9. Give the cell range a name by typing it into the Name Box just above.

How do I insert the same drop-down list into multiple cells? ›

Copy drop down lists across multiple rows

To copy the dependent drop down list, let's do a regular copy/pasting: Select the cell with the secondary drop-down (C3) and press Ctrl + C to copy it. Select all other cells where you want the dependent list to appear (C4:C12) and press Ctrl + V to paste the copied contents.

How do I create a dynamic list in Excel based on criteria? ›

Creating a Dynamic Drop Down List in Excel (Using OFFSET)
  1. Select a cell where you want to create the drop down list (cell C2 in this example).
  2. Go to Data –> Data Tools –> Data Validation.
  3. In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.

Why is my data validation list so wide? ›

In most cases: the worksheet had other cells with data validation in the same row. all dropdowns became the width of the widest dropdown. one of the data validation lists is in a merged cell.

What are the disadvantages of data validation in Excel? ›

Changing Needs: One of the most significant disadvantages of data validation is that data must be re-validated once specific changes to the data are made. As new data types and inputs are added, schema models and mapping documentation will need to be updated.

Does data validation slow down Excel? ›

This is helpful when troubleshooting a workbook -- sometimes entire columns have data validation added, and that can cause Excel to slow down, or to crash. If you see a high number of data validation cells listed, you'll know where to focus your troubleshooting.

How do I make Data Validation drop-down arrows visible? ›

Click on the cell that is right for the top cell of the drop-down list, then select Insert, then select Symbols and select an arrow mark under wings from the drop-down list as represented in the below image, then click on Insert and close the pop-up window.

How do I remove a selected value from a dropdown? ›

To remove any option user have to select it and by using the remove() function and getting the element by 'getElementById' and 'selectedIndex' methods the user can remove it.

How do I remove a value from a dropdown? ›

Select remove() Method

The remove() method is used to remove an option from a drop-down list. Tip: To add an option to a drop-down list, use the add() method.

Why is Data Validation blanked out? ›

If you spreadsheet is “Protected” or being “Shared” with another file, the Data validation tool is turned off in Excel. This is to protect the linked data from producing an error. Annoying right! In order to fix this issue, make sure you unprotect your worksheet.

How to do Data Validation without duplicates? ›

Remove Duplicate Values From the Data
  1. First, copy the list of data you need for the drop-down list. Highlight the list of values.
  2. In the Ribbon, go to Home > Clipboard > Copy or press CTRL + C.
  3. Select the cell where you wish the list to be placed and, in the Ribbon, go to Home > Clipboard > Paste or press CTRL + V.

How to show the first item in the drop-down list instead of blank? ›

To set default value for the drop down list, you need to create a general drop down list first, and then use a formula.
  1. Create a drop down list. ...
  2. Then in the Data Validation dialog, under Settings tab, select List from Allow list, and then select the value you want to show in the drop down list to Source textbox.

Why is my dropdown list not showing in Excel? ›

Click the DATA tab, and click Data Validation. In the Data Validation dialog, set Allow to List; this enables a list in the cell. Leave In-cell drop-down selected; this enables a drop-down list in the cell.

How do I get more than 255 characters in Excel? ›

"Text values in formulas are limited to 255 characters. To create longer text values in a formula, use the CONCATENATE function or the concatenation operator (&)."

Can you put more than one Data Validation on a cell? ›

Re: Excel multiple data validation

You can create two lots of data validation in one column based on another column by using a custom formula. Here's how you can do it: Select the cells you want to apply data validation to. Go to the Data tab on the ribbon and click on Data Validation.

What is the data limit for Excel? ›

Worksheet and workbook specifications and limits
FeatureMaximum limit
Total number of rows and columns on a worksheet1,048,576 rows by 16,384 columns
Column width255 characters
Row height409 points
Page breaks1,026 horizontal and vertical
32 more rows

How to increase character limit from 255 in Data Validation? ›

To avoid this limitation, I can suggest using one of the following approaches: Add a hidden worksheet into your document, fill this worksheet with all required values for data validation and use a range of this worksheet as a data validation parameter.

How do I auto populate data from a drop-down list in Excel? ›

Fill data automatically in worksheet cells
  1. Select one or more cells you want to use as a basis for filling additional cells. For a series like 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. ...
  2. Drag the fill handle .
  3. If needed, click Auto Fill Options. and choose the option you want.

How do I store more than 32767 characters in Excel? ›

Use multiple cells: If you need to enter more than 32,767 characters into a cell, you can simply split your data up into multiple cells. For example, if you have a list of 1,000 items that you need to enter into a single cell, you can simply put them into 10 different cells instead.

What are character limits on Excel? ›

An Excel cell accepts only maximum 32767 characters.

Why is there a 255 character limit? ›

The limit occurs due to an optimization technique where smaller strings are stored with the first byte holding the length of the string. Since a byte can only hold 256 different values, the maximum string length would be 255 since the first byte was reserved for storing the length.

Can you have a drop-down list in Excel with multiple selections? ›

By using VBA, you can create drop-down lists with multiple selections. With the ability to prevent duplicates and remove incorrect items, this feature can streamline data input and improve accuracy in your Excel spreadsheets.

How do I validate multiple columns in Excel? ›

To compare multiple columns in Excel, you can use the conditional formatting option on the home and format the setting to “duplicates” or “uniques”, and choose the desired color to highlight the values to compare multiple columns.

How many rows of data can Excel handle before it slows down? ›

How Many Rows of Data Can Excel Handle? Excel spreadsheet can handle 1,048,576 rows and 16,384 columns at a time. If you are dealing with large datasets, it's inherent that you will have limitations using Excel Power Query.

What are the limits of Excel when working with big data? ›

Hands-on big data with Excel

In fact, Excel limits the number of rows in a spreadsheet to about one million; this may seem a lot, but rows of big data come in the millions, billions and even more. At this point Excel would appear to be of little help with big data analysis, but this is not true.

How do I handle more than 1048576 rows in Excel? ›

Step-by-Step Procedures to Handle More Than 1048576 Rows in Excel
  1. Step 1: Setting up Source Dataset. In the first step, we prepared the source dataset. ...
  2. Step 2: Importing Source Dataset. ...
  3. Step 3: Adding to Data Model. ...
  4. Step 4: Inserting PivotTable from Data Model. ...
  5. Step 5: Employing Slicers. ...
  6. Step 6: Inserting Charts.


Top Articles
Latest Posts
Article information

Author: Kimberely Baumbach CPA

Last Updated: 09/12/2023

Views: 6278

Rating: 4 / 5 (61 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Kimberely Baumbach CPA

Birthday: 1996-01-14

Address: 8381 Boyce Course, Imeldachester, ND 74681

Phone: +3571286597580

Job: Product Banking Analyst

Hobby: Cosplaying, Inline skating, Amateur radio, Baton twirling, Mountaineering, Flying, Archery

Introduction: My name is Kimberely Baumbach CPA, I am a gorgeous, bright, charming, encouraging, zealous, lively, good person who loves writing and wants to share my knowledge and understanding with you.