Allow Weekend Dates Only in Excel

With Excel Data Validation, you can add rules to a data entry sheet, and control what people put in the cells. In today's example, we'll set up a cell that only allows you to enter a weekend date. Just remember that Data Validation isn't foolproof, and people can find ways around your rules.

Continue reading "Allow Weekend Dates Only in Excel"

Missing Data Validation Arrows

To make data entry easier, you can create drop down lists in a worksheet, using Excel's data validation. Usually, those lists are trouble free, but sometimes the arrows disappear, for no apparent reason. See some of the reasons for that behaviour, and how to fix or avoid the problems.

Continue reading "Missing Data Validation Arrows"

Multiple Dependent Drop Downs

With dependent drop downs, select an item from one Excel drop down list, and the next drop down only show the related items. For example, choose Canada in column C, and only see Canadian provinces in column D's drop down. Now, go beyond those basics – see how to create multiple dependent drop downs – Region, Country, Area and City.

Continue reading "Multiple Dependent Drop Downs"

Show Warning in Excel Drop Down

With Excel's data validation, you can show a drop down list of items in a cell. You can even create "dependent" drop downs. For example, select a region, and see only the customers in that region. See how to show a warning in Excel drop down list, if the source data is not set up correctly.

Continue reading "Show Warning in Excel Drop Down"

Excel Data Validation Troubleshooting

How many data validation rules do you have in the giant Excel workbook that you use every morning? If you inherited that file from somebody else, you might not have any idea what's on some of the sheets. Your workbook could be slowing down, or maybe it's even crashing, and you're not sure why. Use these macros for data validation troubleshooting, and see if you need to clean things up.

Continue reading "Excel Data Validation Troubleshooting"

Hide Used Items in Excel Drop Down List

You can add a drop down list in a cell, to make it easy for people to enter data. It's really easy to make a simple drop down. Then, after you've made a basic list, experiment with fancier features, like hiding items that have been previously selected.

Continue reading "Hide Used Items in Excel Drop Down List"

Hide Used Items in Drop Down List

Don’t let Excel yell at us, if we accidentally enter a duplicate value in a column. Okay, maybe Excel isn’t really yelling, but it seems like that when a data validation error message pops onto the screen. Instead of letting people pick a duplicate, use this trick to hide used items in a worksheet drop down list. You can’t pick an item if it isn’t there!

Continue reading "Hide Used Items in Drop Down List"