Create a Dropdown List in Google Sheets for Clean Data Entry
Use dropdowns to avoid spelling mistakes in attendance, fees, class lists and status sheets.
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)
- Select the cells, e.g. C2:C200 (the Class column).
- Right-click → Dropdown (or Insert → Dropdown, or Data → Data validation → Add rule).
- 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.
- Open Advanced options → If the data is invalid: Reject the input.
- Click Done. Each cell now shows a small arrow.
Dropdown from a list on another sheet (easy to update)
- Create a sheet named Lists. Put class names in A2:A20 and fee status values (Paid, Pending, Partly paid) in B2:B5.
- Select the cells for the dropdown → Data validation → Criteria: Dropdown (from a range).
- Enter
Lists!A2:A20→ Done. - Later, add “11-A” to the Lists sheet – it appears in every dropdown automatically.
Other useful validation rules
| Need | Criteria |
|---|---|
| Marks only from 0 to 100 | Is between 0 and 100 |
| Only dates in this session | Date is between 01/04/2026 and 31/03/2027 |
| 10-digit mobile | Custom formula is =AND(LEN(D2)=10, ISNUMBER(D2*1)) |
| Tick box for Present | Checkbox (Insert → Checkbox) – counts with =COUNTIF(E2:E40, TRUE) |
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
- Select the cells → Data → Data Validation.
- Allow: List.
- Source: type
9-A,9-B,10-A,10-Bor select a range such as=Lists!$A$2:$A$20. - Error Alert tab: Style Stop with a message like “Choose a class from the list”. OK.
Problems and fixes
| Problem | Fix |
|---|---|
| People paste other values into the cells | Pasting can bypass validation – use Reject input and check with a filter regularly. |
| Old wrong entries still there | Data validation only checks new input. Filter the column and fix old values. |
| Dropdown not copied to new rows | Select a cell with the dropdown, copy, then Paste special → Data validation only. |
Dropdown क्यों?
जब कई लोग एक sheet भरते हैं, तो एक ही चीज़ कई तरह से type होती है: “10-A”, “10 A”, “X-A”, “10a”। फिर reports और filters टूटते हैं। Dropdown से लोग तय list से चुनते हैं, data साफ़ रहता है और mobile पर भरना भी तेज़ होता है।
Dropdown बनाएँ (type किए options)
- Cells चुनें, जैसे C2:C200 (Class column)।
- Right-click → Dropdown (या Insert → Dropdown, या Data → Data validation → Add rule)।
- Side panel में हर box में एक option लिखें: 9-A, 9-B, 10-A, 10-B। चाहें तो हर एक का रंग चुनें।
- Advanced options खोलें → If the data is invalid: Reject the input।
- Done पर click करें। अब हर cell में छोटा arrow दिखता है।
दूसरी sheet की list से dropdown (update करना आसान)
- Lists नाम की sheet बनाएँ। A2:A20 में class names और B2:B5 में fee status values (Paid, Pending, Partly paid) रखें।
- Dropdown वाली cells चुनें → Data validation → Criteria: Dropdown (from a range)।
Lists!A2:A20लिखें → Done।- बाद में Lists sheet में “11-A” जोड़ें – हर dropdown में अपने-आप आ जाएगा।
दूसरे उपयोगी validation rules
| ज़रूरत | Criteria |
|---|---|
| Marks सिर्फ़ 0 से 100 | Is between 0 और 100 |
| सिर्फ़ इस session की dates | Date is between 01/04/2026 और 31/03/2027 |
| 10 अंकों का mobile | Custom formula is =AND(LEN(D2)=10, ISNUMBER(D2*1)) |
| Present के लिए tick box | Checkbox (Insert → Checkbox) – =COUNTIF(E2:E40, TRUE) से गिनती |
साफ़ data का इस्तेमाल
अब हर value एक जैसी लिखी है, इसलिए summaries काम करते हैं: =COUNTIF(F2:F200,"Pending") बाक़ी fees देता है, =COUNTIFS(C2:C200,"10-A",F2:F200,"Paid") 10-A के fee देने वाले students, और pivot table सब कुछ class-वार दिखाती है।
Excel में यही काम
- Cells चुनें → Data → Data Validation।
- Allow: List।
- Source:
9-A,9-B,10-A,10-Bलिखें या=Lists!$A$2:$A$20जैसी range चुनें। - Error Alert tab: Style Stop और message जैसे “list से class चुनें”। OK।
समस्याएँ और सुधार
| समस्या | सुधार |
|---|---|
| लोग cells में दूसरी values paste कर देते हैं | Paste से validation बच सकता है – Reject input रखें और filter से नियमित जाँचें। |
| पुरानी ग़लत entries अब भी हैं | Data validation सिर्फ़ नया input जाँचता है। Column filter करके पुरानी values ठीक करें। |
| नई rows में dropdown copy नहीं हुआ | Dropdown वाली cell copy करके Paste special → Data validation only। |
Frequently Asked Questions
How do I add a dropdown list in Google Sheets?
Select the cells, right-click → Dropdown, type the options in the side panel (or choose Dropdown from a range), set “Reject the input” under Advanced options, then Done.
How do I edit or remove a dropdown?
Select the cells → Data → Data validation, click the rule to edit the options, or click the bin icon to remove the rule.
Can a dropdown allow more than one choice?
Yes, in Google Sheets: Data validation → Advanced options → Allow multiple selections (available for the Dropdown criteria). Excel needs a macro for that.
