Create a list box in Excel to ensure valid data entry

I’ve been pulling together a spreadsheet for tracking and planning the expenses of my upcoming wedding and honeymoon. Having not used Excel for a while, I was a bit rusty. Anyway, what I wanted to do was have a simple list-box in a cell from which I could select ‘Yes’ or ‘No’ (I know, not earth-shattering at all).

So, I had a quick Google and found a plethora of tutorials on how to add a combo-box or a list-box; but, they all got way too messy and created a box that sat somewhere, and referenced a list of values before returning the index of that value, and then you could use that index to read the original list of values and select the correct one in order to use it. Phew, that was tiring just typing it!! Not a happy camper, but I knew there must be a simple way to have it “just work”.

I got there in the end, after some muddling around, and here’s the solution using Data Validation (this just does ‘Yes’ or ‘No’, but it’s easy to add more values):

  1. Select all the cells you want to have this validation applied to
  2. Go to Data | Data Validation
  3. On the Settings tab, select List from the Allow drop-down list
  4. In the Source text box, enter the valid values for the data, separated by commas (eg: Yes, No) [note: don’t use quote marks]
  5. Select the In-cell Dropdown check box
  6. Click OK

Done!
When users move to one of the selected cells, Excel displays a drop-down arrow. Clicking the arrow reveals a list of valid options which users can select for that cell. If users attempt to type an entry that’s not on the list, Excel will display an error message.

Counting unique values in Excel

The following formula counts the number of unique values in a given cell range

=SUM(IF(FREQUENCY(IF(LEN(B2:B16)>0,MATCH(B2:B16,B2:B16,0),”"),
IF(LEN(B2:B16)>0,MATCH(B2:B16,B2:B16,0),”"))>0,1))

[** Note: replace the B2:B16 range to be whatever your required cell range is]

To calculate, DON’T press ‘Enter’, instead press ‘Ctrl’ + ‘Shift’ + ‘Enter’ and you’re done.

Tah Dah!!