Excel has no built-in ENUM worksheet function or enum data type, so there is no single “Excel formula enum.” To get enum-like behavior, use a Data Validation dropdown for a controlled list, a formula such as SWITCH to translate codes into labels, or a numbering formula to generate a sequence.
What “enum” means in Excel
In programming, an enumeration (often shortened to enum) is a defined set of named choices, such as Draft, Approved, and Rejected. A program can use those choices to make values more consistent and easier to interpret. Excel does not provide that programming-language feature as a worksheet function: typing =ENUM(...) returns an unrecognized-function error.
People searching for an “Excel formula enum” may mean a few different things. They might want a dropdown containing approved values, a formula that converts short codes into readable descriptions, or a numbered list of options. Those are related goals, but they are solved in different ways. Picking the right one matters: a dropdown controls what a user can enter, while a lookup formula interprets a value that is already in a cell.
There is an Enum statement in Visual Basic for Applications (VBA), but it belongs to VBA code, not to formulas entered in worksheet cells. For ordinary workbook data entry and calculations, the options below provide the practical equivalent without requiring a custom macro.
Choose the kind of enum-like behavior you need
- Restrict entries to approved choices: Create a dropdown with Data Validation. This is usually the best fit for status fields, categories, and standard responses.
- Convert a compact code into a label: Use
SWITCH, a lookup table, or (for a small position-based mapping)CHOOSE. - Generate consecutive numbers: Use
SEQUENCEin a supported Excel version, or use a row-counting formula that can be filled down. - Keep choices maintainable across a workbook: Store the permitted values in a range and use that range as the source for validation or a lookup.
These approaches can be combined. For example, a dropdown can limit a Status cell to a known set of codes, and a neighboring formula can display the full description for each code. Keeping input control and display logic separate makes a sheet easier to audit and update.
Create an enum-like dropdown with Data Validation
For a short list of fixed choices, a dropdown is the closest worksheet equivalent to an enum. In Excel for Microsoft 365 on Windows, select the cells that should contain the choices, then go to Data > Data Validation. On the Settings tab, set Allow to List, enter the items in Source separated by the list separator used by your Excel settings, and leave the in-cell dropdown option enabled. Select OK to apply it.
For a list that may change or needs to be used in several places, put the allowed values in a dedicated range instead of typing them into the Source box. For example, place the three status values New, In progress, and Closed in H2:H4. Select the cells that need a status dropdown, open Data Validation, choose List, and use =$H$2:$H$4 as the source. The dropdown now reflects the contents of that range. If the list grows, consider turning the source range into an Excel table or updating the validation source so new entries are included.
Validation helps prevent accidental entries, but it is not an absolute security boundary. Users may paste data into validated cells, and pasted values can bypass the usual dropdown interaction. If data quality is important, check the resulting values as well as providing the dropdown. You can also configure the validation error alert to stop invalid entries or simply show a warning, depending on how strict the sheet should be.
On Mac, the Data Validation feature is also available from the Data tab, although the dialog’s layout can differ slightly by Excel release. Excel for the web supports data validation, but some editing options and dialog details can differ from the desktop app. If a workbook relies on a carefully maintained list source, verify that the dropdown behaves as expected in the versions your collaborators use.
Translate codes into readable labels with SWITCH
If a cell contains a short code and another cell should show its meaning, SWITCH is a clear option when the set of alternatives is small and stable. The formula compares one expression with possible values and returns the result associated with the first match. It can also return a default result when none of the values match.
With a status code in A2, where N means New, P means In progress, and C means Closed, enter this formula in B2:
=SWITCH(A2,"N","New","P","In progress","C","Closed","Unknown")
If A2 contains P, B2 returns In progress. If A2 contains a code not listed in the formula, B2 returns Unknown. The final text is the default result, so the formula can flag unexpected values instead of displaying an error. Copy the formula down column B to interpret the codes in subsequent rows.
This is convenient for a handful of choices, but embedding a long list of codes in each formula can become hard to maintain. If the choices change frequently, a two-column mapping table is often better: one column for codes and another for labels. A lookup can then refer to the table, allowing you to revise descriptions in one place rather than editing formulas throughout the workbook.
Use CHOOSE for position-based choices
CHOOSE returns an item according to a numeric index. It can be useful when the input is already a small integer that directly represents a position, such as a rating level from 1 to 3. It is less suitable for text codes, and the index must be within the supplied choices if you want to avoid an error.
With a number from 1 through 3 in A2, where 1 means Low, 2 means Medium, and 3 means High, enter this formula in B2:
=CHOOSE(A2,"Low","Medium","High")
If A2 contains 2, the formula returns Medium. If A2 contains 4, there is no fourth choice and Excel returns an error. For more complicated mappings or a changing set of values, a lookup table is generally easier to inspect than a long CHOOSE formula.
Generate a numbered enumeration
Sometimes “enumerate” means produce a run of numbers rather than define allowed labels. In Microsoft 365 and Excel 2021 or later, SEQUENCE creates a dynamic array of consecutive values. With A1 empty and the formula entered in A1, enter:
=SEQUENCE(5)
The result spills into A1:A5 as 1, 2, 3, 4, and 5, provided those cells are empty. You can specify the number of rows, number of columns, starting value, and increment as arguments. For example, a sequence can begin at 10 and increase by 10; remember that cells in the spill area must remain clear or Excel will report a spill problem. SEQUENCE is available in Microsoft 365 and Excel 2021 or later, not in older perpetual editions such as Excel 2019.
For older versions, a formula copied down can number rows. With the first item on row 2 and A2 containing the first entry, enter this formula in B2 and fill it down:
=ROWS($A$2:A2)
On row 2 it returns 1; on row 3, after copying the formula down, the expanding range makes it return 2, then 3 on row 4, and so on. This approach is useful when each number should stay beside an existing record. If rows are inserted or deleted, the result reflects the current row positions counted from A2, which may or may not be the numbering behavior you want.
When a lookup table is a better alternative
A lookup table is often the most maintainable choice when many codes map to labels, when users may add choices, or when the same mapping is used in several formulas. Put each code once in one column and its description beside it. That structure is visible to other workbook users, can be sorted or reviewed, and avoids repeating a long chain of conditions.
For a small, fixed set, SWITCH keeps the mapping close to the calculation and can be easier to read than a separate table. For a larger or changing list, a table-based lookup is easier to maintain. In Microsoft 365 and Excel 2021 or later, XLOOKUP is one option; older Excel versions can use established lookup functions such as VLOOKUP or INDEX with MATCH. Choose based on workbook compatibility, not just formula length. If people using older Excel need to open the file, avoid relying on newer functions unless they have a suitable version.
For reliable mapping, avoid duplicate codes in the lookup column: a lookup may return only one matching result, which can hide a data design problem. Also decide what should happen when a code is missing. A deliberate fallback such as Unknown is usually more informative than a blank cell, particularly in reports where unexplained blanks could be mistaken for missing calculations.
Common problems and practical checks
- Using a nonexistent function:
=ENUM(...)is not a native Excel worksheet formula. Use validation, mapping logic, or a sequence function according to the intended outcome. - Unexpected invalid entries: A dropdown is a useful prompt, but pasted data can circumvent normal selection. Review imported or pasted values when consistency matters.
- Formula result does not match a code: Check for leading or trailing spaces, inconsistent spelling, and different code formats. A code stored as text may not behave like a number in formulas that expect numeric input.
- Sequence will not spill: Clear cells below or beside the formula where the results need to appear. Merged cells or occupied cells in the spill range can prevent the full array from displaying.
- Formula works for the author but not a colleague: Confirm the colleague’s Excel version supports the function. Also remember that list separators in Data Validation can depend on regional settings.
Before distributing a workbook, test a valid choice, an unexpected value, and a blank input. Confirm what the sheet displays for each case. These checks reveal whether the dropdown, fallback text, and formulas are aligned with the way the workbook will actually be used.
Frequently Asked Questions
Does Excel have an ENUM function?
No. Excel has no built-in worksheet function named ENUM. Use Data Validation for a controlled list, a mapping formula for code-to-label conversion, or SEQUENCE for consecutive numbers.
How do I make an enum in an Excel cell?
Select the cell, open Data > Data Validation, set Allow to List, and specify the allowed choices or a range containing them. This creates a dropdown, although pasted content may still need separate validation.
Can I use SWITCH for an enum in Excel?
Yes. SWITCH can map a fixed set of codes to labels and can include a default result for unrecognized codes. Use a lookup table instead when the mapping is large or likely to change.
Is Excel VBA Enum the same as a worksheet formula?
No. VBA’s Enum statement is used in VBA code to define named constants. It cannot be typed into a worksheet cell as a formula and is unnecessary for ordinary dropdowns or cell-based mappings.
Which Excel version supports SEQUENCE?SEQUENCE is available in Microsoft 365 and Excel 2021 or later. If you need compatibility with older Excel versions, use a formula such as ROWS copied down to number records.
Related
- Scenario Manager in Excel: How It Works and When to Use It
- Scenario Manager in Excel: Meaning and How to Use It
- Excel Scenario Manager: How to Create and Compare Cases
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Table Formulas Not Carrying Down? Fixes That WorkIf a formula in an Excel table is not carrying down, first confirm the data is an actual…
- Excel COUNTIF and SUMIF With Dates: Practical ExamplesTo count or sum rows by date in Excel, use COUNTIF or SUMIF with a date criterion; for…
- Excel LOWER Formula: Convert Text to LowercaseUse Excel’s LOWER function to convert text to lowercase: if the original text is in A2, enter =LOWER(A2)…
- Power Query IF Examples: Conditional Logic in ExcelIn Power Query, write an IF test in M as if condition then value_if_true else value_if_false. For example,…
- Excel Protect Sheet From Being Deleted: The Right SettingTo stop a worksheet from being deleted in Excel for Microsoft 365 on Windows, protect the workbook’s structure:…
- Transpose in Excel Using a Formula: Step-by-Step GuideTo transpose a range in Microsoft Excel for Microsoft 365, switch its rows and columns with the TRANSPOSE…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.