Country or region and language

    Choose your country or region and your language.

    Suggested for you

      All countries and regions

        Choose your character

        It shows in the top bar and on your profile. Change it any time.

        How to add a drop-down in Excel

        Learn how to add a drop-down in Excel with Data Validation, reject invalid entries, and extend the source range so new choices appear in every menu.

        A person practicing on a laptop in a quiet workspace

        To add a drop-down in Excel, select the cells where you want the menu, open Data > Data Validation, choose List under Allow, and select the cells containing your choices as the Source. Keep In-cell dropdown selected and select OK, then click a destination cell to choose a value from its menu. The example below uses Excel for Microsoft 365 on Windows with English (US) settings.

        A drop-down with controlled choices

        A drop-down gives you a menu of permitted choices, while Data Validation supplies the rule that controls what someone can enter in the cell. For a ticket tracker, a status menu keeps entries consistent instead of mixing labels such as Done and Finished.

        You'll create a ticket-status menu, reject a mistyped status, and check that a new choice appears after you update the source. Those are separate checks: a visible arrow doesn't prove that Excel will reject an unlisted entry or include a newly added choice.

        A reproducible ticket tracker

        The tracker uses source cells for the permitted choices and destination cells for your selections. Keep both groups on the same worksheet so you can compare the menu with its source without switching sheets.

        Start with a new, blank worksheet and follow the complete exercise below. The target, starting values, rule settings, entry test, and source expansion are kept together so you can repeat the check.

        Fictional exercise: ticket-status drop-down
        Tool: Excel for Microsoft 365 on Windows, English (US)
        Starting state: a new, blank worksheet
        Target: B2:B4 must offer only the three starting statuses.
        A directly typed unlisted status must trigger a Stop alert.
        
        Enter these starting values:
        A1: Ticket
        B1: Status
        A2: T-101
        A3: T-102
        A4: T-103
        B2:B4: leave blank
        H1: Allowed statuses
        H2: New
        H3: In progress
        H4: Done
        H5: leave blank
        
        Create the rule:
        1. Select B2:B4.
        2. Open Data > Data Validation.
        3. On Settings, set Allow to List.
        4. Set Source to =$H$2:$H$4.
        5. Keep In-cell dropdown selected.
        6. On Error Alert, select Show error alert after invalid data is entered.
        7. Set Style to Stop, then select OK.
        
        Select these values from the menus:
        B2: New
        B3: In progress
        B4: Done
        
        Expected result:
        Ticket    Status
        T-101     New
        T-102     In progress
        T-103     Done
        
        Invalid-entry check:
        Select B2, type Waiting, and press Enter.
        Expected: a Stop alert appears.
        Select Cancel in the alert.
        Expected: B2 still contains New.
        
        Variation: add a fourth choice
        1. Enter On hold in H5.
        2. Open the B4 menu.
        Expected: On hold isn't listed yet.
        3. Select B2:B4 and reopen Data > Data Validation.
        4. Change Source to =$H$2:$H$5 and select OK.
        5. Open the B4 menu and select On hold.
        Expected: B2 is New, B3 is In progress, and B4 is On hold.
        All three destination menus now offer four choices.
        
        Model calculations: none; the rule checks list membership.

        Practice what this post teaches

        The dollar signs in the Source reference make the cell references absolute, meaning the referenced rows and columns stay fixed when the validation rule is copied to another location. The source range starts at H2 rather than H1 because Allowed statuses is a header, not a status someone should select.

        Rule settings and entry checks

        Create the rule for all three destination cells together, then test both a menu selection and a directly typed invalid entry. Selecting B2:B4 before opening Data Validation gives the three ticket rows the same rule.

        On Settings, List means that an accepted entry must match a source item. In-cell dropdown makes those items available through a menu. On Error Alert, the selected alert checkbox and Stop style block a directly typed unlisted entry, rather than merely displaying a warning that you can choose to ignore.

        After setting up your worksheet, try Data validation: drop-downs and limits for hands-on practice with the same skill.

        Make the three menu selections specified in the exercise before testing the invalid entry. Selecting Cancel after typing Waiting should restore New in B2. Cancel matters because the test checks whether Excel preserves the previous valid selection when you abandon the rejected edit.

        For a longer tracker, select the intended entry range below the Status header before creating the rule. A bounded range, meaning a specific group of entry cells, makes the rule's coverage clearer than selecting the entire column, which also includes the header.

        A new choice and a source check

        A new choice appears only when the validation source includes the cell containing that choice. Adding text beneath a fixed source range doesn't extend the rule automatically.

        Follow the variation in the exercise: first enter On hold in H5, then open the menu in B4 before changing the rule. On hold should be missing because H5 sits outside the original source range. That intermediate check reveals the difference between adding a value to the worksheet and adding a value to the menu.

        Next, select B2:B4 and extend the Source through H5 as specified in the exercise. Reopen B4's menu and select On hold. B2 should still contain New, B3 should still contain In progress, and B4 should now contain On hold.

        Check the menus in B2 and B3 as well: both should now offer On hold. Updating only B4 would leave the other ticket rows with their original three-choice menus, even though the cells would still look similar.

        • Editing an existing source label changes the choice offered by menus that use that source.
        • Extending the source range adds another source cell to the choices.
        • Neither change automatically replaces text already stored in destination cells.

        Treat a source-label change as a separate cleanup task for existing records. A destination cell stores the text you selected; it isn't a live reference to the source cell. Review existing selections whenever you rename a status.

        Common mistakes and method limits

        Most drop-down problems come from the selected destination cells, the source boundaries, or the alert settings. Check those parts before rebuilding the worksheet.

        Drop-down troubleshooting checks
        SymptomLikely causeExact check
        No arrow in a selected entry cellThe cell lacks list validation, or the menu option is offSelect the cell, open Data Validation, and check Allow: List and In-cell dropdown.
        On hold is missingThe source still ends at H4Inspect Source and confirm that its final cell is H5.
        The header appears as a choiceThe source includes H1Confirm that the source starts at H2.
        An unwanted blank appearsThe source includes an empty cellInspect every cell inside the source boundaries.
        Menus differ between ticket rowsThe source update affected only some destination cellsInspect B2, B3, and B4 individually and compare their Source settings.

        Data Validation isn't a complete data-quality safeguard. Pasting can bypass validation alerts or replace validation rules, depending on how you paste. The exercise tests direct typing, not every way information can enter a workbook, so inspect imported or pasted records separately.

        A standard list also supports one selected value per cell. Multiple-selection menus and dependent lists, where one menu's choices depend on another cell, need a different setup and aren't covered by this method.

        Common questions

        Why doesn't my Excel drop-down show an arrow? Select the destination cell first, because the validation arrow normally appears when that cell is selected. If the arrow is still missing, open Data Validation and confirm that Allow is List and In-cell dropdown is selected.

        How do I add another option to an existing drop-down? Enter the new choice in a source cell, then update the validation Source if that cell falls outside the current range. Apply the update to every intended destination cell and reopen a menu to confirm the new choice appears.

        Can I apply a drop-down to a whole column? Yes, but selecting the entire column also includes its header. It's usually clearer to select the intended entry cells below the header and apply the rule to that range.

        Can I select multiple items in a standard Excel drop-down? No, a standard Data Validation list stores one selected value per cell. Choosing another item replaces the previous selection rather than adding to it.

        Practice what this post teaches

        Your Work Gym account

        Save your progress and pick up on another device. Your browser's progress comes with you.

        or

        You can keep practicing without an account.

        Help and feedback

        Ask a question, tell us what is not working, or share an idea. We reply by email.

        What is it about?

        We use it only to reply to you.