Data Validation In Excel: Not As Daunting As It Sounds

Fri 24th December 2010

Sometimes the name of something can be enough to put you off approaching it. Data validation may sound incredibly 'techie' but getting to grips with this extremely useful part of Excel needn't be a daunting task, as these examples show.

Sometimes the name of something can appear more daunting that what it describes. For example if the doctor tells you that you are suffering from nasopharyngitis, you might faint in shock, thinking your days are numbered. When the doctor brings you round and explains that nasopharyngitis is simply another term for the common cold, you straighten yourself up, dust yourself down and leave the surgery displaying the classic symptoms of an Idiopathic Craniofacial Erythema; that's a red face to you and me.

This is also the case in the world of computer applications, where certain items on those drop-down menus sound far more complex than they really are. This is often true in at least getting to know the fundamentals of how they work. I remember that, having got to grips with the basics of Excel, I steered clear of Data Validation simply because it sounded far more 'techie' than my capabilities allowed. When I did finally investigate it I found it to be a very useful tool in the Excel box.

Data Validation allows you greater control over the information that is entered in your worksheet. For example you can provide users with a list of choices, place restrictions on what is entered or create custom settings. Here are a couple of examples of the basics of Data Validation.

You could use it to insert a pop-up window that appears when a certain cell is selected, that contains a message relating to that cell. As a quick example you may have a worksheet that is updated occasionally and each time the worksheet is modified the date must also be updated. When the user selects the cell to change the date, a pop-up window appears with a reminder that the date must be inserted in the British format of DD/MM/YY and not the American MM/DD/YY. Such pop-up windows can be of great assistance to others who may use the worksheet and setting one up is simplicity itself.

In pre-2007 versions of Excel select the cell you wish to activate the pop-up window. Go to the Data menu and select Validation, then select the Input Message tab. Make sure the Show Input Message When Cell Is Selected box is checked. In the title box type the title of your message. This will appear in bold text at the top of your window and for the purposes of this demonstration you may like to type the word 'Message' as your title. Now in the message box type your message. Again, for demonstration purposes you might like to type 'If you can read this then the operation was a success'. Click OK.

For post-2007 versions select your chosen cell and display the Data tab of the ribbon. In Data Tools click the Data Validation tool. This will open a dialog box. Select the Input Message tab and make sure the Show Input Message When Cell Is Selected box is checked. Then follow the Title and Input Message stages as above and click OK.

To take this a step further, Data Validation can be used to restrict what can be entered in a cell by the introduction of a drop down list. For example let us imagine a busy haulage firm with vehicles ranging from light vans to articulated lorries. Of the twenty drivers employed by the firm, only three, Tom, Dick and Harry, hold HGV licences. In the bustle of a busy office it would be easy for the name of a non-HGV licence holder to be entered accidentally into the cell that indicates an HGV delivery. This could cause significant delay as the mistake is rectified and the drivers are allocated their correct instructions.

Data Validation can prevent this scenario occurring by restricting what can be entered in the cell relating to the heavy goods vehicle to the names of the three drivers who are qualified to drive it.

To try this, select any cell as the one that will display the name of the driver. Go to Data and click on Validation. From the Allow menu select List and in the Source box type the names of the drivers, separated by commas, i.e., Tom, Dick, Harry. Press OK.

Now you will see an arrow next to the cell and clicking this will reveal a drop down menu with the names of the qualified drivers. Select the one you want and you have allocated the job without any danger of a mistake. In fact to demonstrate the security of this feature even further, you might like to type the name Bill into the chosen cell. You will see that Excel rejects it.

Here we have a couple of straightforward but very effective techniques that involve the use of data validation. There are a whole lot more out there just waiting to be discovered, so why not go and discover them?

Author is a freelance copywriter. For more information on excel training course london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1328-data-validation-in-excel-not-as-daunting-as-it-sounds.html