Create Drop Down Options in Excel Using Data Validation

You can easily create drop down options in Excel by just using data validation rules. First, what you need to do is create a list of all the values you want in the drop down list. Once completed, assign a named range to those values (on how to assign a named range, refer to this post):

Note with named ranges you cannot use spaces. If you need to, use an underscore.

Next, select the cell where you want the drop down list to be. Under the Data tab in  Excel select Data Validation. Under the allow section, select List. Then in the source section reference your named range with an = sign before it. See example below:

If you didn’t assign a named range to your options, you have to specify the cells here. Click ok and you’re done.

Now when you select that cell you will see the drop down options available. If you want to copy the drop downs to be available to adjacent cells use flash fill by dragging the bottom right corner of the cell with data validation and drag over to which cells you want it to apply to.

Drop Down Options Based on Previous Selections

What you can also do is make your drop down options dependent on what you selected in a prior drop down selection. This requires using the INDIRECT function.

In the first drop down option what I will do is create a named range for all the categories,Category1-Category5. Once a user selects an option, the next drop down will be based on the products relating to the category that they have selected. The first part is the same as the process mentioned above, only now it is relating to the selection of a category group. The key here is making sure that the categories are the same as the named ranges I have created for them. For example, if a user selects Category1, in order for my other drop down to work I need to have a named range assigned to Category1. If I do, then I can utilize the indirect formula. Below is the data validation I would use for the second drop down option:

My category selection is in cell B7. The indirect function looks at cell B7 to see what range I am referencing. If I select Category1 in cell B7, then the data validation will pull the named range for Category1. If I haven’t set up a named range for the selection, the drop down list will be empty. Otherwise, my second drop down will now reference the products in the named range belonging to Category1.

Convert LAST NAME, FIRST NAME into different formats

In many databases names may show up as last name, first name which doesn’t make it ideal for sorting and filtering data easily. However this can be quickly rearranged in Excel. I’ll show you two ways to do this: 1) using text to columns (the easier method) and 2) using formulas

1) Using Text-to-Columns

Select the cells that have the last name, first name format and under the Data tab click on Text to Columns.

On the popup that comes up select Delimited and click Next

On the next screen, make sure Comma is the only delimiter selected and then press Finish

Doing this will put the last names in the current range and the first names in the adjacent column B. You will want to make sure the adjacent column is blank to make sure you don’t accidentally overwrite data that may be in those cells already.

An optional step, if you wanted to combine both last name and first name into one cell is to use the following formula in cell C18:

=B18&” “&A18

B18 is the first name and A18 is the last name fields. The ampersand connects the words with a blank space inbetween. Cell C18 would show John Smith

For Excel purposes, it’s probably ideal to have the last name and first name in separate cells rather than in one.

2) Using Formulas

Extracting the Last Name

First, the formula to extract the last name (assuming the cell is A2):

=LEFT(A2,FIND(“,”,A2,1)-1)

If I have Smith, John in cell A2, then the result is as follows:

The LEFT function extracts x number of letters from the left of the cell. The key is the second argument. It consists of:

FIND(“,”,A2,1)-1

The FIND function looks for a comma within cell A2, and begins at the 1st character. If the cell is Smith, John then the comma would be found in the 6th character of that cell. The -1 that is after the FIND function is to reduce this to the 5th character. This is done because I don’t want to include the comma in the name.

So how the LEFT function works is from cell A2 it pulls the first 5 characters from the left, which will result in Smith

Had I not included the -1 after the FIND function, it would have been Smith,


Extracting the First Name

Now I’ll show you how to extract the first name using a similar function, the RIGHT function:

=RIGHT(A2,LEN(A2)-FIND(“,”,A2,1)-1)

The logic behind the RIGHT function is the same as the LEFT function, except this time the formula pulls the characters from the right instead of the left. However you’ll notice the second argument is a little more complex this time. The argument is as follows:

LEN(A2)-FIND(“,”,A2,1)-1

The key difference is I am now using the LEN function which tells me the number of characters that are in the cell. LEN(A2) would return 11 characters for Smith, John

Why can’t I just use the RIGHT function the way I used the LEFT one? The reason being is the FIND function searches from left to right and not from right to left, so it will tell me the position of the comma from the left, not from the right. So if it returns the number 6, I know the comma is the 6th character from the left, but doesn’t tell me how many from the right.

In order to do this I have to use the LEN function. If the total cell is 11 characters long, and the comma is at the 6th character, that will mean there are 5 characters after the comma. Coincidentally in this case the comma is 6 spots from both the left and right, making it right in the middle of the cell.

Here again I use the -1 after the FIND function because if I pull the 5 characters from the right, it will include a blank space (obviously if the data is formatted as last name,first name instead of last name, first name you can omit the -1).

Combining the Two

Now these two functions have allowed me to pull the different parts of the name out of a comma separated last name, first name format. If you want all of this into one formula (and assuming you want first name last name to be in a single cell) then all you would need to do is enter the formula as follows:

=RIGHT(A2,LEN(A2)-FIND(“,”,A2,1)-1)&” “&LEFT(A2,FIND(“,”,A2,1)-1)

I’m reusing the formulas used earlier and connecting them with ampersands while also adding a space inbetween.