Tag Archives: Data Validation

Highlighting Top X values with Conditional Formatting in #Excel

Well… think this topic is too simple?  How about doing this with a dropdown menu to make the Top X a dynamic one? with Icon Set? You may download a Sample File to follow along. Let’s start with the basics … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , , , | Leave a comment

Dynamic Shrinking Dropdown with Dynamic Arrays in #Excel 365

The recent HOT topics about Excel should be the new Dynamic Arrays!   After watching all the amazing demonstrations about the new Dynamic Arrays, I’ve decided to sign up to the Office Insider (Fast) programme (for Office 365 subscriber only).  … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , , , | Leave a comment

Data Validation to restrict user from inputting to future days

The situation We need to input certain data on a daily basis.  For example, daily sales.  That is supposed to be an easy and routine task for many people.  BUT… it happened that we still have a chance to input … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | 2 Comments

How to limit time interval input in a single cell?

Got the following question from a reader: Under data validation, is it possible for me to restrict the time duration (in a cell) to be 30mins or less? Example: 9.00 – 9.30 (accepted) 10.15 – 10.50 (rejected) Obviously, the answer … Continue reading

Rate this:

Posted in Excel Tips, Formula | Tagged , | 4 Comments

Move the Input Message Box out of the way

Did you know that there is Input Message box which look like a comment box? The main difference is your don’t see any “indicator” on the cell.  The Input Message box pops up when the cell is selected.  To do … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

In-cell dropdown by Data Validation – Allow input NOT from a list

This is about a trick of using named range that includes a blank cell; which allows any input even the cell is with Data Validation that allows list. When you attempt to input anything other than what is restricted by Data … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | 6 Comments

Allow time input at 15-min intervals only – Data Validation

How to limit user to input time at 15-min intervals, i.e. 00:15, 00:30, 00:45, 01:00, etc.? We talked about how to limit user to input Time in the previous post.  Let’s go one step further with Custom Data Validation, where you … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | Leave a comment

Data Validation – TIME

How to limit user to input TIME only? Sometime, actually too many times, users may not input cell content in a way you want them to.  As a result, the formula you set for calculation may go wrong; and you … Continue reading

Rate this:

Posted in Excel Tips | Tagged | 1 Comment

Dynamic Dependent Dropdown by Data Validation

So many D. 😀 This is a continuation of the previous post: Not all teachers are eligible to all classes.  e.g. only Iris and David are eligible to deliver Class F, would it be feasible to have only Iris and … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | Leave a comment

Dynamic Dropdown – Least hours assigned always on the top

How to use Data Validation to show a dynamic drop-down list to facilitate job allocation problem? I learned about using Data Validation long time ago.  It’s quite handy and useful when you want to restrict user to enter items only … Continue reading

Rate this:

Posted in Excel Tips | Tagged | 2 Comments