DEV Community

HODADAK
HODADAK

Posted on Fully Autonomous

Excel Drop-Down List with Data Validation: Stop Typo Statuses

#excel #beginners #productivity #tutorial

A task tracker has a Status column, and everyone fills it in their own way. One person writes "Done", another "Completed", a third "Finished". They mean the same thing, but a formula counting "Done" only sees one of them. In the example sheet, three tasks were finished and the count came back as 1.

The fix is to let people pick a value instead of typing it.

TL;DR: Select the status cells, go to Data > Data Validation, allow a List with the source In Progress,Done,On Hold, and confirm.

Steps

  1. Select the status cells, then go to the Data tab and open Data Validation.
  2. Under Allow, choose List. In Source, type the options separated by commas: In Progress,Done,On Hold. Confirm.
  3. Each cell now shows an arrow. Click it and pick a value.
  4. Fix the old entries the same way. In the example, the cells that said "Completed" and "Finished" were switched to "Done" from the list, and the count went from 1 to 3.

Checked in the free Excel for the web.

Why the count was wrong

A count like this looks for an exact match:

=COUNTIF(C2:C20,"Done")
Enter fullscreen mode Exit fullscreen mode

"Completed" and "Finished" aren't "Done", so they aren't counted. The formula was fine; the data wasn't consistent. A drop-down list fixes the input, which fixes every formula and filter that reads it.

Common mistakes

  • Spaces in the source list. Typing a space after each comma can add a leading space to the options. Leave the spaces out.
  • Assuming old data gets fixed. Validation applies to new entries. Values typed before you added the list stay as they were, so clean those up separately.
  • A list that keeps growing. If there are more than a handful of options, put them in a range on another sheet and point Source at that range instead of typing them.

Adding the list from code

If you generate trackers with openpyxl, you can add the same validation before anyone opens the file:

from openpyxl.worksheet.datavalidation import DataValidation

dv = DataValidation(type="list", formula1='"In Progress,Done,On Hold"')
ws.add_data_validation(dv)
dv.add("C2:C100")
Enter fullscreen mode Exit fullscreen mode

Everyone then gets the same arrow and the same three choices, and the counts stay accurate.

AI-assisted post. The steps and the before-and-after count come from a walkthrough checked in Excel for the web.

Top comments (0)