Changing lists and limitations - Microsoft Excel Tutorial

- [Instructor] Creating dropdown lists, as you well know, can provide some exceptional data that is clean and meets expectations, but your list can require some cleanup. Things like sorting. People have become accustomed to things being sorted alphabetically for most things. It's quicker to scroll down, requires way less reading. So as a best practice when appropriate, you might consider always sorting your list. Let's get started there. I'll go to Data List and let's take a look at Tour Types. They're already sorted in alphabetical order so we're good there. Let's look at Backpack_Cali. I'll go ahead and highlight C2 through C6. On the Data tab, I'll choose Sort Ascending. Because Excel sees data to the right, it thinks we may want to expand the selection. In this case, we don't. We want to continue with the current selection. I'll go ahead and choose Sort. Now I'll work with my next list, California_Calm. I'll highlight, I'll choose Sort Ascending, Continue with the Current Selection, and choose Sort. And then, last but not least, is the Nature_Watch list. Perfect, now I'm going to go make sure that I see that sort reflected in my actual dropdowns. I'll go to Explore, I'll choose Backpack_Cali for this example, I'll hit my dropdown, and they're perfectly sorted. I'll go ahead and choose the Mt. Whitney Climbers Tour. There can be times where you actually want to place a popular value at the top of the list that way people aren't having to search for it alphabetically. This is great when you have a really large lists. Let's go take a look at our Data List. For the Nature_Watch, the Fossil Tour is by far the most popular tour. Now, it may seem weird, but we're going to have to move this Fossil Tour and some of the data twice in order to make the change. Okay, so I'll choose the Fossil Tour, I'm going to hold my Shift key on my keyboard, and I'll place my mouse right on the border, and then I'm going to drag it up one time. And then I'll grab the Coastal Experience. Again, hold my Shift key and move it down one time. This moves the Fossil Tour to the top, but it also tells Excel to shift the list. All right, let's go back and check Explore California. I'll choose the Nature Watch, and I can clearly see the Fossil Tour is at the top. If ever you're making a movement in the list, if you come to the dropdown and you don't see that value anymore, you might need to move it one more time. Also, do you notice how we have some blanks here? This is another way to clean up your list. This was produced from the way that the data was named. Let's go back to our Data List. So for California_Calm and Nature_Watch, when we created our list, we had them selected like this. This produced blanks under each of these other columns. So there's two ways to address this. Okay, we'll start with the first way. I'll go ahead and highlight Backpack_Cali and California_Calm. I'm going to go to the Home tab. I'll go to Find & Select. I'll choose to Go to Special. I'll select the Blanks and click OK. Now it's just highlighted these blanks. So now I can right-click and Delete and shift these cells up. Let's go check out California_Calm and see if it got rid of those blanks. Okay, I'll go choose California_Calm here, my Tour Type, I'll hit the dropdown for the Actual Tour, and now I see this nice, clean list. Okay, let's look at the other option. I can actually go in and adjust the named ranges. As a data person, I would probably go this route because I like the control. I'll go to Formulas. I'll choose the Name Manager. Okay, I'll start with the Taste_of_California and I'll go ahead and just limit this to F2 because that's the only value we have for the Taste_of_California. I'll go ahead and check. All right, I'll go to Nature_Watch and I want to limit this down to Row 4. So I'll backspace out the 6, replace it with 4. And then I'll apply it. Choose Close. And again, it's never enough to just do the commands. You always want to test. So I'll go back to Explore California. I'll hit the dropdown by the Fossil Tour. Perfect. Let's go choose the Taste_of_California. And then we should see our one option. Perfect. One of the biggest limitations, unless you want to start programming, is the fact that when the values on the list change, it doesn't update the values already entered. For example, if we renamed Big Sur Retreat to Big Sur Endurance Tour, you would only see that in the list going forward. There are coding options in VBA, but for most users, it's just enough to know that you have to manually change them through something like Find & Replace. No matter what, you want, nice, clean, and sorted lists that make the user experience as easy and as comfortable as possible. Why? Because an easier user experience always equals better data.