Google Sheets में Clean Data Entry के लिए Dropdown List कैसे बनाएं

Why use a dropdown?

When several people fill one sheet, the same thing gets typed in many ways: “10-A”, “10 A”, “X-A”, “10a”. Reports and filters then break. A dropdown lets people pick from a fixed list, so the data stays clean and filling is faster on mobile too.

Create a dropdown (typed options)

1. Select the cells, e.g. C2:C200 (the Class column).
2. Right-click → Dropdown (or Insert → Dropdown, or Data → Data validation → Add rule).
3. In the side panel, type options one per box: 9-A, 9-B, 10-A, 10-B. Pick a colour for each if you like.
4. Open Advanced options → If the data is invalid: Reject the input.
5. Click Done. Each cell now shows a small arrow.

Dropdown from a list on another sheet (easy to update)

1. Create a sheet named Lists. Put class names in A2:A20 and fee status values (Paid, Pending, Partly paid) in B2:B5.
2. Select the cells for the dropdown → Data validation → Criteria: Dropdown (from a range).
3. Enter Lists!A2:A20 → Done.
4. Later, add “11-A” to the Lists sheet – it appears in every dropdown automatically.

Other useful validation rules

Use the clean data

Because every value is now spelled the same, summaries work: =COUNTIF(F2:F200,"Pending") gives pending fees, =COUNTIFS(C2:C200,"10-A",F2:F200,"Paid") gives paid students of 10-A, and a pivot table shows everything by class.

Same thing in Excel

1. Select the cells → Data → Data Validation.
2. Allow: List.
3. Source: type 9-A,9-B,10-A,10-B or select a range such as =Lists!$A$2:$A$20.
4. Error Alert tab: Style Stop with a message like “Choose a class from the list”. OK.

Problems and fixes