Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In Excel 2010 Power Shortcuts, Excel expert Dennis Taylor shares tips and shortcuts to vastly increase efficiency and get the full power out of Excel 2010. There are tips for working with the Ribbon and Quick Access toolbar, navigating workbooks and selecting cells, rapid data entry and editing, working with formulas, formatting data, working with charts, sorting data, and much more. Exercise files accompany the course.
One of Excel's great tools for controlling data in worksheet is data validation. In column C, we want to put in shipping dates, but there are two things we might be concerned with. We don't want any typos that in any way would suggest that the shipping dates before the order date. That wouldn't be so useful. Furthermore, maybe we're suggesting here that because this particular product that the company deals with, maybe it requires some assembling and packaging before shipping, it must be more than two days later than the order date. So, there is already a Data Validation rule in place here, and here it is.
If you're familiar with this feature, even though this looks a little strange, Formula, what it really is saying is any entry in column C must be more than two days later than the entry in column B. So, if for example here if we put in 9/4/10, this does not work, because it's got to be more than two days. So we'll change that and put in a 5. That will be acceptable. Let's imagine that you might want to have a further restriction here that prevents any of the shipping days from being Sunday, possibly even Saturday.
Which day of the week is this? If you're familiar with the Excel function weekday, you can find that out pretty quickly. This function for some people was disappointing at first, but the weekday of this date is 1. Well, 1 means Sunday, 2 Monday, etcetera. If the weekday is 1, this is a Sunday. We want to prevent any entries in here that are Sundays. So, we're going to make a change to this rule here. We'll just get rid of this. We didn't have to do that necessarily, but let's click column C again, choose Data Validation.
A change to the rule, we want to embed this within the function called and, and now we want to use the weekday function. The weekday of C1 not equal to, and how do we say that? The less than arrow, followed by the greater than arrow. We want this to not be equal to 1. So, we've altered the formula to prevent two kinds of entries, those shipping dates that are within the two days after the order date, and any weekday that's equal to 1, namely Sunday.
This message throws you a little bit sometimes. It simply means that C1 and B1 don't fit our rules, but we don't care. We're ignoring those specific cells. Yes, okay. We'll try this again, 9/5/10. Now, remember, that was acceptable the first time around. Now, it's not, because we have augmented our rule. That's a Sunday. We can't have that in there. We certainly can't have the 12 either, because that's also a Sunday, and so on.
You can imagine, altering this rule, again slightly, we could simply also make sure that it's not a Saturday, by putting in almost the same thing here. I'm just going to Ctrl+C here, copy this, put it after the 1, Ctrl+V, paste this in. We also want the weekday to not be equal to 7, which is Saturday, and click OK. So, an example here, remember we couldn't put in the 12. That was a Sunday.
Can we put in the 11? No, we can't do that. Now, we could customize the message to explain why, but let's say that's not exactly a shortcut, but you could certainly do that as well. When you do set up these rules, we have the option here under Data Validation to provide an Arrow Alert and customize the message. You can actually put in your own title and message and so on if you wish. But the main idea here is we do have control, because we're aware of a function called Weekday, to prevent weekdays of a certain day from occurring here and you can image other applications of this.
But Data Validation, along with the Weekday function do give us firm control over the entries in column C.
Find answers to the most frequently asked questions about Excel 2010 Power Shortcuts.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
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.