Data Validation Tips

Data Validation a tool , through which Duplication of work can be avoided , Human errors can be minimised and TAT can be saved. There are many more rules. here a glimpse of the same.

Introduction: How do I set a maximum number for the cell?

1. Highlight the area you want to apply data validation to

2. You can highlight the entire column or click in the top cell, press Ctrl + Shift + Arrow down just to select pre-existing data.

3. Click on Data – Data Validation

4. On the Settings tab, choose Whole Number

5. Choose Less than or Less than or Equal to from the dropdown.

6. Enter the value e.g. 20 or if it’s a cell, click on the cell that contains the value you want to use as the maximum.

7. To help the users, it can be useful to add a message

8. Click on Input Message

9. In the Input Message box, type something like “number must be less than 20”

10. And if they persist….

11. Click on the Error Alert tab and in the Error Message box, you could type something like “what part of “enter a number less than 20 did you not understand?”

How do I only allow values between certain amounts ?

1. Follow steps 1-4 above

2. Choose Between from the dropdown

3. Enter your maximum and minimum values.

4. Apply the Input Message and Error Message as above if required. (Steps 7-11 from above)

How do I extend the data validation?

So your data validation is working well but now you find you need to extend it.

1. Highlight where you want to extend it to.

2. Make sure you have included cells with the data validation

3. Click on Data – Data Validation

4. You will receive a message that says “ The selection contains some cells without Data Validation settings. Do you want to extend Data Validation to these cells?”

5. Click on Yes

6. Your data validation dialog box will open up

7. Click OK.

How do I copy the data validation to another part of the sheet?

1. Click on cells with data validation

2. Click on Copy

3. Highlight the cells you want to copy the validation to.

4. Click on Paste – Paste Special.

5. Choose Validation

6. Now test.

How do I stop people changing some entries?

The most straightforward way to do that is by cell protection. But it’s not intuitive. The idea is that you indicate the areas you want people to be able to change and lock down everything else.

1. Begin by highlighting the areas you WANT people to be able to change.

2. Click Ctrl and 1 (to open the Formal Cells dialog box)

3. Click on the Protection tab

4. Clear the Locked box

5. Go to Review – Protect Sheet

6. Choose what you want people to be able to do.

7. Enter your password

8. You will then be prompted to re-enter it.

How do I only allow items from a specific list in?

The clue is in the name…you will be using a list

Method 1 – Create your list - static

1. Use this method if the items in your list will not be changing e.g. months, days etc.

2. On a separate worksheet, create the entries. (Type them vertically)

3. It can be useful to put your list entries on this sheet and call this sheet Lists

4. Highlight your list

5. In the Name Box (to left of formula bar) enter the name you want to give this range.

6. Note no spaces allowed in this name e.g. week_days NOT week days

7. Press Enter

8. Return to the sheet you want to apply the data validation to

9. Follow steps 1-3 in Introduction

10. Choose List from the list

11. Click in the Source box

12. Press F3 to call up your list of range names

13. Click on week_days

14. Now you will have a dropdown box where you can pick the week days from

15. Apply steps 7-11 from the Introduction if you want to add an error message.

Method 2 – Creating a dynamic list

1. Follow steps 1-3 as in Method 1

2. Click in your list.

3. Click Insert – Table

4. If your table doesn’t have a heading, leave the box for My table has headers unticked.

5. Note that it may add a “column 1” at the top.

6. You can remove that by deselecting Header Row from the Design tab (right hand side)

7. Now follow steps 5-15 from Method 1

8. The difference with this is that if you add a new item to your list, it will be automatically reflected in your data validation list

9. In order to make any changes other than the specified ones, you will have to unprotect the sheet (Review – Unprotect Sheet) by entering the password.

I want to delete the list

1. Click on Formulas – Name Manager

2. Click on the range you want to delete

3. Press Delete

I want to change the list

If you need to change the list

1. Simply follow the steps in Method 1 and at step 13, click on the new list you want to use.

How do I remove data validation?

1. Highlight the list you want to remove the data validation from

2. Choose Data – Data Validation

3. From the Allow box, choose Any Value

4. To extend the removal to all the cells, tick the Apply these changes to all other cells with the same settings box

I don’t even want other users to open my file.

1. Click on Review – Protect Workbook

2. Enter your password

3. Don’t forget to write down your password in a safe place

If you are responsible for the work of someone else, make sure they tell you what the password is. I’ve heard horror stories about disgruntled employees who have left without giving the password….(and on a side note, why don’t we call happy people gruntled…?)

OK, I’ll let them look, but not touch

1. Go to File – Save As

2. Click on Browse (if you are using Excel 2010 or later)

3. Go to folder you want to save it into.

4. Click on Tools (bottom right hand side)

5. Click General Options (nope, me neither )

6. Enter a password that allows the user to open or to modify.

7. Click OK and then Save.

I only want certain dates entered

From the Introduction, follow steps 1 – 3

1. On the Settings tab, choose Date

2. Note that from the Data tab, you can choose from any of the following:

3. Between (enter your Start Date and End Date)

4. Not Between ((enter your Start Date and End Date)

5. Equal to (enter your date here)

6. Not Equal To (Enter the date here)

7. Greater than (Enter the date here i.e. later than this date)

8. Less than (Enter the date here i.e. earlier than this date here)

9. Greater than or equal to (Enter the date here i.e. later than or equal to this date)

10. Less than or equal to (Enter the date here i.e. earlier than or equal this date here)

An example of how to use this would be to use =today() in the Date box using Less Than to restrict date entry to only those dates in the past.

I only want the users to enter certain text lengths in it.

An example of this would be where you only want users to be able to enter an 8 digit number.

1. Follow steps 1-3 from the Introduction

2. On the Settings tab, choose Text Length

3. Choose Equal to

4. Enter 8

5. Note that you have the same set of choices as referenced in the I only want certain dates entered section. i.e. greater than, lesser than etc.

6. Apply the Input Message and Error Message as above if required.

Prevention of Duplicate Entries:

It is set up by using the CountIf function.

Let’s say we want to prevent duplicates in cells M5:M100

1. From the Introduction follow steps 1-3

2. From the Allow box, choose Custom

3. In the Formula box enter the following: =COUNTIF($M$5:$M$100,M5)<=1

4. Note that what this function does is assess the range $M$5:$M$100 which is fixed. The M5 part of the formula will change as copied down. Therefore what it will do is see if what is in the current cell is counted more than once i.e. duplicate.

5. Apply your error messages if required (Introduction: steps 7-11)

I want the users to be able to sort and filter the list but not do ANYTHING ELSE.

One would think that specifying Sort and Autofilter in the Protect Sheet box would sort this. Nope, it doesn’t.

1. Highlight the area you want users to be able to sort and filter

2. Make sure the Filter is turned on (Data – Filter)

3. From Review – Allow users to Edit Ranges

4. Don’t use a password at this point

5. Click New

6. The range you have selected should appear in the dialog box

7. Click OK.

8. Then click Apply and OK

9. Now follow the instructions given in How do I stop people changing some entries?

10. Make sure the Sort and Autofilter boxes are ticked and that the Select Locked Cells and Select Unlocked Cells are deselected.

11. Note that for sorting you will have to use the Data – Sort dialog box to sort the data.

Courtesy By www. the-excel-expert.com