H2EPostDropDowns

Create an Excel Drop Down List Without Blanks

by with No Comment dropdownformsfunctionsintermediatenamesoffsetrangestips & tricks

In a previous post, I covered how to create a form in Excel. Although I didn’t go over drop down lists specifically, they are one element you could incorporate into them. The problem is that your list can change over time, getting bigger or smaller. And that can make it difficult to maintain if your list isn’t dynamic as it will involve you always having to manually change the range of your drop down list. Otherwise, it could be incomplete or contain blanks. Below, I’ll show you how you can manually change your drop down list in excel and create it without blanks while also making it dynamic so that you don’t need to worry about whether it changes over time.

Setting up the drop down list

First, let’s start with the basics — creating the list. To create a drop down list in Excel, you just need a series of options to choose from. My list is going to be made up of the top 30 places to visit. I’m just going to put those names in a list.

After entering in the list of places into Excel, the next thing I will do is select all the values, and create a named range. This is as simple as just entering a value next to the formula bar, where you see the cell location. I will call this range VacationSpots:

Named range in Excel containing popular vacation spots.

There is no need to add headers or anything else. Just select the values, enter in a name for the list, and hit enter. A longer approach would be to go to the Formula tab and select Name Manager:

Name Manager in Excel.

Clicking this will show you all of the named ranges in the workbook:

Name manager showing a list of all the named ranges in an Excel workbook.

It shows you the named range I created. However, I could also create one from this screen and also Edit my existing range. This is where you would go to make the change manually. Clicking on the Edit button would give you this screen:

Editing a named range in Excel.

As you can, here I can manually change the address as needed in case the list changes. However, this is obviously not optimal as it can be a tedious process if your list changes frequently.

Creating the drop down

Now that my list has been created, I can set up the actual drop down. To do this, I’m going to select a cell and under the Data tab, click on Data Validation. Here, there is a place to enter your list of values:

Entering in data validation in Excel.

Under the Allow section, I choose List. And for the Source, I enter the ‘=’ sign followed by my named range, VacationSpots. Now, when I click OK and go to the cell that contains the data validation, this is what I see when I select it:

Cell showing a drop down arrow.

Clicking on the drop down arrow will show me my list of options, in the same order that they appear in my list:

Selecting from a list of drop down options in Excel.

I can select any of the values and my cell will update accordingly. This is great, but what if I decided to add more items to my list, perhaps 10 or 20 more locations I want to visit? Next, I’ll go the different ways you can create drop down lists in Excel without blanks.

Option 1: Create extra spaces in your drop down list at the very end

Technically this step involves blank spaces, which is not what this post is supposed to be about. However, I just wanted to show you how this could work. If your list has dozens of items, then having extra blanks may not be that big of a deal. For example, say I edit my named range so that it goes to 50 rows. If you do that and include empty cells, this is the biggest problem you’ll face:

Empty values in a drop down list.

My list no longer starts from the top, it goes to the first blank cell. This can be an annoying problem because now it looks like all of my options aren’t there (they are, but I have to scroll up every time). This is probably the main reason people want to avoid having blank values in their lists. If the blank values simply came after all of your selections, that might be more tolerable. But because they impact where your drop down list begins from, it can be a nuisance.

The good news is there is a simple way to get around this. For all your empty cells, enter just a single empty character. Select a cell, hit the space bar, get out of the cell, and copy that value down. Now, your empty cells technically aren’t empty because they contain a space. And by doing so, the drop down list now starts from the top again. You will still have blank values, but this time they will show at the bottom of your drop down list:

Drop down list with blanks at the bottom.

If this is acceptable then you can stop here. If you are still intent on getting rid of any possible blank value whatsoever, then head over to the next option.

Option 2: Creating a table to create a nonblank list

This option is the easiest method for getting rid of blank values. What you need to do here is convert your list into a table. Select a cell on your list, click on the Insert tab and then click Table:

Creating a table in Excel.

Leave the option for headers unchecked and then click OK. You should see something like this:

Newly created table in Excel.

By default, Excel will apply its formatting and design but you can change the look of it to make it blend in more with your spreadsheet. You can also re-name the header from Column1. Either way, you can now create a new drop down list from this table. Since the values are in range A2:A31 in my spreadsheet, that is what I will enter for my new Data Validation list:

Data validation list in Excel.

You can either select the range, or enter it in yourself. But if you enter it, you need to include the $ signs otherwise it will not auto-update properly. Now, I’ll go to my list add ‘New City’ to the bottom of the table. When I do that, the table automatically expands which you can notice because I haven’t changed the design and so the colors change:

Table in Excel after adding a new value.

And if I go back to the Data Validation settings, my source has automatically been updated:

Data validation list in Excel updated.

This is a really easy way to make your drop-down list automatically update without the need for any formulas.

If the table you are referencing isn’t on the same sheet as your drop-down list, then you will need to use the INDIRECT function reference it. For instance, if you have created a table called Table1 (which should contain just one column for your list) on a different sheet, you can reference it the following way:

This will allow you to reference the list even if it is on a different sheet.

Option 3: Using a formula to remove the blanks in your drop down list

If for whatever reason creating a table isn’t an option for you, you can still create a dynamic list using a formula. Here, I’ll go back to creating a named range. Except rather than selecting a fixed set of cells, I will rely on a single formula. First, I’ll go back to the Name Manger. I’ll create a new named range. The formula for this can look a bit complex so I will break it down into parts.

First, I’m going to use the OFFSET function. This is because it can allow me to specify a height and width, which is crucial to making this work. My data starts in cell A1, and that’s where my formula will begin:

=OFFSET(A1,0,0

A1 is my starting point and that is the first argument. The next two arguments are whether I want to offset and move to any adjacent rows or columns. Since I don’t, I leave those values as zeros. It is the next argument that is critical, as it relates to the height. Here is where I will use a COUNTA function. I want to count the number of nonblank values in my list. My formula looks as follows:

=COUNTA(A:A)

I will embed this within my previous formula:

=OFFSET(A1,0,0,COUNTA(A:A)

For the width, I will set the last argument to 1, since I don’t want to include any extra columns. Here is my completed formula:

Named range in Excel using the Offset function.

You always want to used $ signs in named ranges so that they don’t move on you. Now that this is set up, I can use this NewRange as my Data Validation source. And just like with a table, whether the list gets bigger or smaller, my named range and the drop down list will automatically update.

However, what if your list contains some formulas that look blank but really aren’t? Formulas are a great example of cells that can look empty even if they aren’t. The COUNTA function will count these values and you could again be back to square one with additional blank values. One way you can get around this is by counting the cells that are blanks, and subtracting that from the total rows. The formula would look as follows:

=ROWS(A:A)-COUNTIF(A:A,””)

Using this, you should correctly arrive at the number of cells that contain text and that aren’t blank as a result of a formula You can then insert that formula in your named range, in place of the COUNTA formula:

Named range in Excel using the countif and offset formula.

As you can see, this method isn’t the easiest and that is why I left it for the end. However, there are multiple different ways you can create a drop down list in excel without blanks. But it’s important because by removing blanks, it will make your form or spreadsheet look more polished by not having blank values in them.

If you liked this post on how to create a drop down list in Excel without blanks, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

multipledropdowns

How to Create a Drop Down List in Excel

by with No Comment dropdownindirectintermediatenames

If you need to collect user input in an Excel spreadsheet, you’ll want to be able to minimize errors and typos in the information that you receive. That’s why it’s important to know how to create a drop-down list in Excel as it will limit the selections that someone will have to choose from. Using lists can prevent someone from making a data-entry error which can save you a lot of grief later on when you go to analyze the data. Here are the steps involved in creating a drop-down list:

1. Create the list of items that you want your user(s) to be able to select from

The first step in learning how to create a drop-down list in Excel is to first identify your list of selections. This seems like an obvious step but sometimes people don’t actually set aside a space on their spreadsheet for a list of items and simply hardcode the selections later on. By listing the items, you can easily modify them later and visually see what are the user’s options. In this example, I’m going to give a user a list of stores to choose from:

List of stores for a drop-down list in Excel.

2. Convert the list into a table

You don’t need to do this but there’s an important reason to do so: if you add items later, the range you select for your drop-down list will automatically update. If you just select a regular range, you’ll have to modify it if you add more options to it later. The goal is to make this as easy as possible. And if you always have to adjust the range for subsequent additions, it’s an easy step to miss.

To create a table, just select your range and on the Insert tab, click on the Table button:

Creating a table in Excel.

Make sure to include your header in the select and that you tick off that your data includes headers:

Create table and specify that it includes headers to ensure your drop-down list contains the correct selections.

Now you’ll likely see some automatic formatting applied to your list that shows it’s now a table:

Table created in Excel that can now be used for a drop-down list.

3. Create a named range

Within the table, where you selections are, create a named range. Just select the cells you want to use (they should be everything in the column) and just assign a name to them. Here’s how you can create named ranges in Excel. Don’t worry if after assigning a named range it doesn’t reflect the name in the reference in the Name Box. Since it’s a table, it’ll still reference the table name. In my example, I created a named range called Stores.

By referencing named ranges, you avoid having to rely on cell references and it’ll make your list dynamic. This is also going to play an important role if you want to have multiple lists, with one based on a prior list’s selections.

4. Create the drop-down list using data validation

Decide where you want your drop-down list will go. Sometimes it’s helpful to highlight it so that it’s easy to distinguish it from other cells. In my example, I’ve highlighted the cell in yellow. Then, click on Data Validation under the Data tab:

Select data validation to create your drop-down list in Excel.

From there, select a List for your range. The list should be equal to your named range or the range of cells you want to use for your drop-down selections. I’m going to set this equal to the Stores named range. When using named ranges, always put the equals sign in front:

Setting your named range to a named range. This will make it easy for managing your drop-down list.

Now, the drop-down list is ready to go:

A drop-down list in action.

You don’t have to use a named range for a list. You can just select the range manually or you can just type the options in:

Manually entering in selections in a drop-down list.

As you can imagine, this is a very cumbersome approach and can be very time consuming if you have a long list. Unless you’ve got only a few options that will never change (e.g. yes/no), it probably doesn’t make sense to manually enter the selections this way.

A key benefit of using a named range, within a table, is that if you add selections they’re automatically updated. I’m going to add StoreF to my list of stores. And all that involves is just typing that store directly below the last value in the table:

Adding another store to the table is very easy to do and it will automatically update your drop-down list.

If I go back to my drop-down list, the selection’s already there:

A drop-down list is now updated.

Had I manually entered the drop-down selections, the list wouldn’t update automatically. I would need to adjust it manually. This can obviously save a lot of time if your list will grow over time.

Creating drop-down lists based on prior drop-down list selections

If you’ve got multiple drop-down lists and want to make them dependent on prior selections, this section is for you. The good news is that it’s largely the same approach. If you know how to create a drop-down list in Excel, adding dependent lists won’t be much more difficult.

You can make some very complicated and nested drop-down lists possible by using a combination of tables and named ranges. In my example, suppose not every store sells the same product. So a scenario can be that a customer is placing an order and selects a store and then in their next selection they can select from a product that’s available in that specific store.

What we’ll need to do in that case is create another named range for that specific store. That range will show the products that store has available. Now, unless the number of selections will remain the same (e.g. same number of stores as there are products available in that store) — which I’m going to guess is unlikely — you’ll want to create a separate table. In this example, I’ll create a list of products just for StoreA, and convert it into a table:

A drop-down for a list of products sold at StoreA.

I also need to select the list of products and assign a named range to them. For the named range, it’s important that I assign it the name of the store: StoreA. The reason this is important is that this becomes key to my formula in order to link the previous drop-down selection (where I chose a store) to the new named range.

In the next data validation list, I’ll need to use the INDIRECT function and refer to the previous selection (for stores). In my spreadsheet, the cell that contained the store selection was L6, and so my INDIRECT function will need to reference that in the data validation:

Using an indirect formula to reference a prior drop-down list selection.

Since the range is hardcoded in the data validation settings, if you move your drop-down box you’ll need to update the indirect formulas. Here’s how my second drop-down selection looks now:

Multiple drop-down lists.

By using the INDIRECT function, the drop-down selections for the product category were updated based on the StoreA selection. I’ll create another table for StoreB where only ProductD and ProductE will be available. Now, when I go to select StoreB, these are what my selections look like:

Drop-down list selections based on a different store.

The named range is set to StoreB for those selections. What we can do is drill down even further. Suppose ProductA and ProductB came in various sizes – for simplicity’s sake, I’ll just call them Size1, Size2, and Size3. To do that, I’ll again need to create a table for those selections, and a named range that refers to the product name:

Another list for size options based on product selections.

These ranges are called ProductA and ProductB. I’ve put them in separate tables because since there are a different number of options, I don’t want them to be part of the same table. If they were in one table then ProductB would include blanks. And if only selected the first two selections then it wouldn’t automatically update if I added more selections.

Here’s what my new drop-down selection now looks like if I select ProductA:

Three drop-down lists to select options from.

For the third drop-down selection, I have to again use data validation and use the INDIRECT function and refer to the adjacent cell. If I select ProductB, I only have two sizes to choose from. Here’s how the drop-down lists look in action:

If you liked this post on how to create a drop-down list in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Convert LAST NAME, FIRST NAME into different formats

by with No Comment intermediatenames

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.