Excel has no built-in ENUM formula or worksheet keyword. To enumerate values, use a function such as SEQUENCE or a fill-down formula; to create a fixed set of named choices, use a lookup table and optionally a drop-down list.
What “enum” means in Excel
“Enum” is short for enumeration: a defined set of named values, often paired with numbers. For example, a task might have the allowed states Draft, Review, and Approved, represented internally by 1, 2, and 3. In programming, an enumerated type can restrict a variable to specified values. Excel worksheet cells do not have that kind of built-in enum type, so a user asking for an “enum formula” may mean one of two different things: numbering a list, or assigning and using a controlled set of values.
Those goals need different tools. A numbering formula generates values such as 1, 2, 3 down a column. A mapping table translates a code into a label. Data Validation can limit what someone selects or types into a cell, but it does not create a new formula function or a strict programming-language data type.
Does Excel have an ENUM function?
No. Typing =ENUM(...) into a worksheet will not work because Excel does not provide a built-in function by that name. There is also no general worksheet syntax such as ENUM(Draft,Review,Approved) for declaring a set of constants. If you saw “Enum” in a programming example, it may refer to a feature in a programming language or to a custom solution, not a standard Excel cell formula.
Excel does have functions that solve parts of the problem. SEQUENCE generates a run of numbers, CHOOSE returns an item based on a numeric position, and lookup functions can retrieve a label from a code table. A drop-down list can help keep entries consistent. Choosing among these approaches depends on whether you need numbering, a readable label, controlled input, or all three.
Use SEQUENCE to generate numbers
In Microsoft 365 for Windows, the simplest formula for creating a consecutive list is SEQUENCE. Its syntax is =SEQUENCE(rows,[columns],[start],[step]). The row count is required; the other arguments are optional. The function returns an array that spills into neighboring cells, so those cells need to be empty.
With five desired values and an empty output area beginning at A2, enter =SEQUENCE(5) in A2. It spills the numbers 1 through 5 into A2:A6. The result is a vertical list because the optional column count is omitted. To start at 10 and increase by 10, use =SEQUENCE(5,1,10,10); it returns 10, 20, 30, 40, and 50 in A2:A6. In both cases, the formula is entered only in A2.
If a source list contains four items in A2:A5 and you want their positions beside them, enter =SEQUENCE(ROWS(A2:A5)) in B2. The result spills 1, 2, 3, and 4 into B2:B5. This is useful when the source list changes length, provided the output range remains clear. SEQUENCE is available in Microsoft 365 and Excel 2021 or later; older Excel versions need a fill-down method instead.
Number rows in older Excel versions
A formula based on ROWS works in older Excel versions and can be copied down. With the first number intended for B2, enter =ROWS($B$2:B2) in B2 and fill it down. B2 returns 1, B3 returns 2, and B4 returns 3. As the formula is filled, the second reference expands while the first remains anchored.
For a list where blank source cells should not receive a number, put this formula in B2 beside the first source entry in A2, then copy it down: =IF(A2="","",ROWS($A$2:A2)). If A2 contains a value, B2 returns 1; if A2 is blank, B2 stays blank. The next nonblank row still receives its physical row position in the range, however. If you need a gap-free count that ignores blank entries, that requires a different formula or a filtered list, so decide which numbering behavior you want before filling down.
To fill a formula down in Excel for Windows, select the formula cell and the cells below it, then press Ctrl+D. On Mac, the fill-down shortcut is Control+D. You can also drag the fill handle at the bottom-right corner of the selected cell. Check the first and last results after filling to catch an accidental shift in the references.
Represent named enum values with a mapping table
For a set of named states, put a code in one column and its label in another. For example, enter 1, 2, and 3 in A2:A4, with Draft, Review, and Approved in B2:B4. This table makes the relationship visible and easy to edit, unlike embedding every label repeatedly in formulas. You can keep the table on a separate worksheet if you prefer, but use consistent references when building formulas.
With the codes in A2:A4, labels in B2:B4, and a selected code in D2, enter this formula in E2 to return the matching label: =IFERROR(INDEX($B$2:$B$4,MATCH(D2,$A$2:$A$4,0)),"Unknown code"). If D2 contains 2, E2 returns Review. MATCH finds the exact code position, INDEX returns the label in that position, and IFERROR displays a message if the code is missing. This formula uses functions available in older Excel versions as well as current releases.
For a very small fixed set, CHOOSE can be a compact alternative. With an index from 1 to 3 in A2, enter =CHOOSE(A2,"Draft","Review","Approved") in B2. If A2 is 2, B2 returns Review. The first argument selects the position in the following list. This is convenient for a short, unchanging set, but a mapping table is easier to maintain when labels or codes may change, or when the list is longer.
Restrict entries with a drop-down list
A formula that looks up a code does not, by itself, stop someone from entering an invalid value. To guide entry, create a drop-down list from the labels. With the permitted labels stored in B2:B4, select the cells that should contain a choice, then go to Data > Data Validation. In the Data Validation dialog, set Allow to List and set Source to =$B$2:$B$4. Confirm the dialog to add the list. Excel for Mac also provides Data Validation from the Data tab, though the dialog layout can vary slightly by release.
The source range is a list reference, not an enum formula. If users need to choose a label but calculations should use its numeric code, keep both columns in the mapping table and look up the code or label as needed. A drop-down improves consistency, but it should not be treated as foolproof enforcement: pasted data can introduce unexpected values, and settings may allow other entries depending on how the validation rule is configured. For important data, also check entries with formulas or review them before using them.
Common problems and how to fix them
- #NAME? after entering
=ENUM(...): Excel does not recognize ENUM as a built-in function. Replace it with a numbering, selection, or lookup approach. - #SPILL! from
SEQUENCE: one or more cells in the required output area may contain data, or the spill range may be blocked. Clear the obstructing cells or move the formula. - Unexpected numbering: a copied formula may use a relative reference where an anchored reference was intended. In a fill-down counter, keep the starting reference fixed with dollar signs.
- “Unknown code” for a code that looks correct: check for numbers stored as text, extra spaces in text codes, or a code that is not actually in the lookup range. Make the code values consistent in both places.
- A drop-down is missing an option: confirm that the validation source covers the entire list and that the labels are inside the referenced cells.
Also distinguish a generated sequence from a permanent identifier. If a row number is based on its current position, sorting or inserting rows can change what the formula displays. If a value must remain the same identifier after sorting, store it as data rather than relying on a position-based numbering formula.
Choosing the right approach
Use the approach that matches the job rather than searching for a single enum formula:
- Use
SEQUENCEwhen you need a dynamic run of numbers in Microsoft 365 or Excel 2021 and later. - Use
ROWSwhen you need a simple numbering formula that can be filled down and must work in older Excel releases. - Use a code-and-label table plus
INDEXandMATCHwhen values need a stable, editable mapping. - Use Data Validation when people should select from a controlled list, and pair it with a lookup or check if the workbook also needs a corresponding code.
- Use
CHOOSEonly for a short, fixed set where putting the options directly in the formula is genuinely convenient.
For a reusable workbook, a table is usually easier to maintain than a long formula containing many labels. A table lets someone add or correct a value in one place, and it makes the allowed choices visible to the next person who opens the file.
Frequently Asked Questions
What is the formula for ENUM in Excel?
There is no built-in ENUM formula in Excel. Use SEQUENCE or a fill-down counter to number items, and use a mapping table or CHOOSE to associate codes with named values.
How do I number a list automatically in Excel?
In Microsoft 365 or Excel 2021 and later, enter =SEQUENCE(ROWS(A2:A10)) in the first output cell beside a list in A2:A10; it spills 1 through 9. In older versions, enter =ROWS($B$2:B2) in B2 and fill down.
Can Excel restrict a cell to a set of enum values?
Use Data Validation with the List option to provide a drop-down. This guides entry but is not the same as a programming-language enum type, so check pasted or imported data if invalid values would cause problems.
How can I convert an enum number into text?
Create a table pairing each number with a label, then use a lookup formula such as INDEX with MATCH. This keeps the mapping easy to read and update.
Why does SEQUENCE show #SPILL!?
The formula needs an unobstructed area for its results. Clear the cells in the expected spill range, remove merged cells that interfere, or place the formula where enough empty cells are available.
Related
- Remove Duplicates in Excel: Shortcut and Safe Steps
- Remove Duplicates by Row in Excel
- Remove Both Duplicates in Excel: Keep One or Remove All Rows?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formulas With Text: How to Combine Words and ValuesTo use text in an Excel formula, put literal words inside straight double quotation marks, such as ="Ready".…
- Freeze Function in Excel: Freeze Panes or FormulasTo keep headings visible while you scroll, select the cell just below and to the right of the…
- Excel Formula for Quarterly Dates: Examples and TipsTo calculate the first day of the calendar quarter containing a date in A2, use =DATE(YEAR(A2),3*INT((MONTH(A2)-1)/3)+1,1). For example,…
- Rank in Excel With Ties: RANK.EQ and Unique RanksTo give equal values the same rank in Excel, enter a RANK.EQ formula. With scores in B2:B6, put…
- Excel Text Unique Values: Extract, Count, and CompareYes. In Excel for Microsoft 365, use UNIQUE to return a list of distinct text values from a…
- Excel FILTER: What the Include Argument DoesIn Excel’s FILTER function, include is the test that decides which rows or columns to return: TRUE keeps…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.