Ravindra BagaleCourses & study guides

2. Data Entry Tools

2.4 Drop-down Lists

By Last updated:

In short: A drop-down list is data validation with Allow: List.

A drop-down list is data validation with Allow: List. It gives clean, consistent values (no "pune", "Pune ", "PUNE").

Steps in Excel

  1. Put the list items on a sheet named Lists, e.g. cities in A2:A7.
  2. Convert them to a Table (Ctrl + T), name the Table tblCity (Table Design › Properties › Table Name), then create a name: Formulas › Defined Names › Define Name › Name CityList › Refers to =tblCity[City]. A name that points to a Table column grows automatically when you add items.
  3. Select the City column in Orders › Data › Data Tools › Data Validation › Allow: List › Source: =CityList › keep In-cell dropdown ticked › OK.
  4. For a short fixed list type items directly: Delivered,Cancelled,Returned.
  5. In Microsoft 365, a spill range can feed the list: Source =$H$2# (see Module 9).

Worked example. Payment Mode drop-down with source UPI,Card,Cash on Delivery,Wallet. Now Zoya cannot type "upi" or "U.P.I" – every row matches exactly, and a PivotTable later shows four clean payment modes.

Ravindra Bagale's Tip

Khup students list che items source madhe type kartat aani navin city aali ki pratyek sheet var validation badlava lagte. List vegli sheet var Table madhe theva aani named range kiwa Table column la refer kara – ekda update kela ki sagla update hota. Source madhe space chukun rahili tar tya item madhe pan space yete, dhyan rakho.

Practice task

Create drop-downs for City, Platform (Blinkit, Amazon Now) and Status. Add a new city "Satara" to the list Table and check that the drop-down shows it without editing the validation.