Have you ever seen anything like this:
What a mess! The longer a list gets with more people using it, the more likely it is that you’ll wind up with this kind of dirty data. The problem will be even worse if the data is difficult to type.
One easy solution is to restrict everything in column C so that the user has to select from a pick list or drop down box. This only takes a couple steps in Excel.
You need to store the list of acceptable values somewhere. I prefer to use another tab in the same workbook, and then hide that tab when I’m done.
First… just to make things clear, I’m going to clean up the three tabs Excel gave me by default (you can make Excel start with just a single tab if you like, or up to 255 if you’re into that sort of thing).
First, I’ll rename Sheet1 Actors. I just double click on Sheet1 and type the new name.
Then I’ll double Click Sheet2 and name it Lookup (or Data, or Pick Lists, or whatever suits you).
I don’t need Sheet3 at all, so I right-click and delete it.
On my freshly minted Lookup sheet, I type in the acceptable values. I give the list a heading just to make it obvious later on what the list is doing there.
Now return to the Actors tab and highlight the C column (Either do the entire column, or everything from C to the bottom of the spreadsheet).
Now click Data on the Ribbon, then choose Data Validation from the Data Validation drop down.
Excel gives us this screen. In the Allow box I choose “List” from the drop down. Then in the Source box, I use the little range picker (the button on the right of the box) to switch over to the Lookup tab and highlight my two genders (but not the header for the list). After I select the range, I click the range picker button again and see my range entered for me like you see here. Just click OK.
Now you’ve got this handy little dropdown whenever you’re editing something in Column C. Users will be prevented from entering anything other than the values you say are acceptable. This doesn’t clean up the old data, you’ll still need to edit them by hand, or do a Find and Replace if you have lots of dirty data.
The last step you might consider taking is hiding the Lookup tab. You don’t need to see it anymore unless you’d like to use it for other drop down boxes. To hide it, just right – click and Hide.
That’s it. We’ve now got a nice clean list that’ll be just a little harder for someone to mess up.