Seasonal Savings: 20% off selected memberships for a limited time. Give now

Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Sequencing and placing lists

From: Excel 2010: Data Validation in Depth

Video: Sequencing and placing lists

In this worksheet there are three sets of data validation criteria. In column B we've got status entries. That list is currently in column J. You see it off to the right. Column C, state names. We see that off to the right in column K. And then column D for the Departments, that's out in column L to the right. When you set up data validation rules it's not a bad idea to have the data on the same worksheet so you can check out the results, but as a long-term solution this probably isn't the best location.

Sequencing and placing lists

In this worksheet there are three sets of data validation criteria. In column B we've got status entries. That list is currently in column J. You see it off to the right. Column C, state names. We see that off to the right in column K. And then column D for the Departments, that's out in column L to the right. When you set up data validation rules it's not a bad idea to have the data on the same worksheet so you can check out the results, but as a long-term solution this probably isn't the best location.

Depending upon the environment, you might simply want to hide these columns. Nothing wrong with that. Others who are using this particular worksheet might uncover them, possibly maliciously or by mistake. They could change the content. It may not be a good solution. Another approach could be to take these three columns, move them way, way off to the right, and then hide the columns. Better yet, why not put these on a separate worksheet? Now, in prior versions of Excel, if you did move a list like this to another worksheet, it wouldn't work properly until you gave them a range name.

It's still true, however, that lists that are used in data validation criteria must be within the same workbook. In other words, they must be in this file; they can be on other sheets. Prior versions require that if they were, you had to refer to them by a range name. So we're concerned about the location of the lists. The other concern is the order of the lists. Every time we choose a state here, we see the names this way.

We don't necessarily know how to find a state. Probably, it would've been better to have alphabetized these. Now if California entries appear very, very often--maybe it's not exactly in sync with the population-- but if California does appear most often, you might want to keep it at the top and alphabetize the remainder. Or based on your analysis of these three entries, if these first three states comprise the most frequent choices, keep them on top and then alphabetize what's left. So we want think of the order and we can change the order.

And a third concern is what if we need to add entries to the list? Or change the order of just an entry or two? Those are the concerns that we have. Let's first talk about the idea that where these are located can be changed. Let's use states for an example here. We don't want this list right here anymore. We're going to highlight it, and we can do this in a couple of different ways of course: right-click and cut, or Ctrl+X. I am going to put this on Sheet1, anywhere out here. Right-click and paste.

So it's out here now. So what happens to our pick list? Click here. There's the drop arrow. There's the choices. We're all set. Now, could we've referred to that location from the beginning? Yes, we could have. And so when you're setting up a list, you can refer to another sheet. We probably would want to move the other two lists as well. And again, they could be on the same sheet; they could be on different sheets. Remember, in prior versions of Excel you had to give them a range name first. And I could've done all three at once. So I'll just take these together, right-click, and Cut, and I'll go to that same Sheet1, and put them out here: right-click, Paste. There we go.

So they're over here now. They're not on the OtherList over here, and yet they still work properly. Now, how about the order of the lists? We can first with the Department list here. The most common choice in this list might be Manufacturing, but we have to scroll to it every time we need to use it. So let's get it at the top of the list. How can we do that? Keep in mind that we could have done this earlier in thinking out how the list should've been here.

We don't always have that option. But let's do consider, as we jump back to Sheet1, where the list is. We want Manufacturing on top of this. Now, you may know that you can drag a cell with the Shift key and insert it. So where do we want to put this? Well, as I drag upward--and I am holding down the Shift key--we want it on top, don't we? Unfortunately, if we put it on top, the cell reference is not going to follow. We need to put it right here, at least for the moment. So I am dragging top or bottom edge with the Shift key, putting it right here, letting go of the mouse first.

ADC, we're going to drag it downward using the Shift key. Put it underneath. Let's go check out that list and see how it's working now. There's Manufacturing at the top of the list. It works just fine. We might consider the same kind of thing with states as we suggested earlier. Let's go back to Sheet1 where I put this list and consider another aspect of how we adjust the list. We've added a new Marketing department. Where are we going to put it? If we put it at the bottom of the list, it will not appear in here. Let's type it here first, and maybe it's not going to have that many people.

We should be able find it simply by inserting it in its proper location. So we'll simply drag this upward with the Shift key and put it alphabetically right in there. It appears to have indenting also. I'll un-indent that. There we go. And how is our list going to look now? Marketing will be there alphabetically in the appropriate spot. The last entry here is Research/ Development, and that is the last entry in the list as we jump back here and check that out as well. Research and Development. You want to insert from within.

If one of these organizations no longer exists, you'll want to delete a cell-- not erase it or move, but simply right-click and choose Delete and then Shift cells up. So we are removing the group, International Clinical Safety, from the list. It's between Executive Education and Logistics. And now it's gone. Let's see what happens in our list here as we try and use it. It's not there anymore, as you would expect.

So the order of the lists, the placement of the lists, and the ability to add new entries and take out entries are critical features that we need to be thinking about if we're using the dropdown list capability of data validation.

Show transcript

This video is part of

Image for Excel 2010: Data Validation in Depth
Excel 2010: Data Validation in Depth

17 video lessons · 9574 viewers

Dennis Taylor
Author

 

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ .

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Excel 2010: Data Validation in Depth.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member ?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferences from the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Learn more, save more. Upgrade today!

Get our Annual Premium Membership at our best savings yet.

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.