Formula Errors

Shorten an Excel Data Validation Formula and Return the Correct Column

Jason MillerJason Miller Nov 13, 2026 868 views

Question details

Shorten a data validation formula that exceeds Excel's 255-character limit and correct a list formula that returns values from rows instead of columns.

Shorten an Excel Data Validation Formula and Return the Correct Column
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user is creating a data validation rule or dropdown list based on worksheet values.
Observed behavior
The original validation formula is 269 characters long, and the list formula returns values in the wrong direction.
Before you start

Select the affected cell and open Data > Data Validation to check whether the rule uses List or Custom under Allow. Confirm the source range and which cells contain the conditions before editing the formula.

Solution 1Recommended

Use INDEX to return a column for a validation list

Use this approach when a dropdown needs values from a column rather than a row.

In INDEX, a row argument of 0 returns the selected column from the specified range. The column number is relative to that range, so 1 selects column AA and 2 selects column AB in this example.

1
Create a named formula

In Excel, open Formulas > Name Manager and select New. Name the formula ColumnList and enter =INDEX('DATABASE'!$AA$2:$AD$5,0,1) in Refers to.

2
Choose the source column

In the named formula, replace the final 1 with the desired column number from 1 through 4, or with a reference to a cell containing that number.

3
Apply the dropdown

Select the destination cell, open Data > Data Validation, choose List under Allow, and enter =ColumnList in Source. Select OK.

Use INDEX to return a column for a validation list
Use a list source: This formula supplies a column of values for a List rule. A Custom validation rule instead needs a TRUE or FALSE result.
WPS Spreadsheets

Create a Short Custom Validation Rule in WPS Spreadsheets

Use WPS Spreadsheets to apply a concise AND/OR formula that checks worksheet values and returns TRUE or FALSE.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Select the validation cell: Click the cell where you want to apply the rule, then open Data > Data Validation.
  3. 3. Enter the custom formula: Choose Custom in the validation settings and enter =AND(OR(A32="BIO-ORIENTED",A32="MONO-ORIENTED"),OR(A35="LOW",A35="HIGH")) in the formula field. Adjust the cell references to match your sheet.
  4. 4. Apply and test: Confirm the settings, then change the referenced cell values to check that valid combinations are accepted.
Create custom data validation rules with short logical formulasOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceAccess WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel reject my data validation formula?

Excel limits the length of a formula entered in the data validation dialog to 255 characters. Shorten the expression, or use a named formula as the source for a List rule.

How do I make INDEX return a column instead of a row?

Set the row argument to 0 and specify the column number, such as =INDEX('DATABASE'!$AA$2:$AD$5,0,2) for the second column of the range.

Should a custom validation formula return a list?

No. A Custom rule should evaluate to TRUE or FALSE. To display a dropdown, choose List under Data > Data Validation and provide a source range or named reference.

How can I use values from another sheet in a dropdown?

Create a name under Formulas > Name Manager that refers to the source range or an INDEX formula. Then select List in Data > Data Validation and enter the name, such as =ColumnList, in Source.