Dennis demonstrates how to base entries on formulas or on values in other columns, including weekdays only.
- [Speaker] Sometimes when you use data validation…you realize there's no obvious choice…among the options offered.…In this worksheet called formulas, were about to…put in shipping dates here for these entries in column B.…We want to make sure these are not Sundays.…Well how would we know that?…If you go to data validation, there is a choice…called date, but no obvious way here from these options…to figure out day of the week.…Now, in cell D2, I'm using a function called weekday.…
This is a function that allows us…to calculate day of the week.…The result we get from this is the number one through seven,…one is Sunday, two is Monday, et cetera.…So as it turns out this shipping date is unacceptable,…we don't do shipping on Sundays.…We want to prevent Sundays from being entered here.…And at the same time, we want to make sure that…the shipping date is later than the order date…or possibly equal to it.…So we need two things to happen.…Now what I'm going to do is prepare the formula…outside of data validation so we can see it better.…
Dennis begins with the basics—how to display data so that errors can be easily spotted. Next, he offers handy tips to ensure data is entered correctly the first time, using the AutoFill feature and using AutoCorrect shortcut codes for frequently used entries. Dennis provides easy ways to validate your data, which is particularly helpful when multiple team members are contributing to the same spreadsheet. This includes restricting the data that can appear in a spreadsheet by setting value limits, pre-populating data with drop-down lists, and other methods. He also shows how to avoid mistakes in formulas, how to hide data that doesn't need to be seen, and how to use workbook protection to prevent errors, and more.
- Streamlining data entry steps
- Using Excel shortcuts and automation features
- Protecting worksheets and workbooks
- Validating data
- Basing entries on formulas
- Detecting errors in formulas
- Using Range Names
- Finding mistakes in large, complex spreadsheets