Data Validation in Excel

Data validation can reduce the risk of errors occurring in a worksheet. Data validation restricts information entry into cells prompting the worksheet user to enter the correct information. It applies restrictions to forms or templates that can accept input in some cells.

Spreadsheet files accompanying this post can be downloaded at www.digidiscover.com/downloads

1. Open the workbook Data Validation

2. Highlight cells B4:B10

3. On the Data tab in the Data Tools group, select Data Validation

4. Allow Whole Numbers

5. For Data choose Between

6. For Minimum enter 18 and Maximum 65

7. On the Input Message tab, type in Enter Age for Title

8. In Input Message enter Please enter an age between 18-65

9. On the Error Alert tab, for Style, choose Information

10. On the Error Alert tab, for Style, choose Information

11. For Title type in Enter Age

12. For Error Message type, Please enter between 18-25

13. Click OK

14. Try and type 70 for cell B7

15. The Error Message will appear

16. Click OK

17. On the Data tab in the Data Tools group, select the arrow beside Data Validation

18. Choose Circle Invalid Data

19. It will circle all data that does not follow the Data Validation settings

20. On the Data tab, select the arrow beside Data Validation

21. Choose Clear Validation Circles

22. It removes data circles in the worksheet

23. On the Data tab, choose Data Validation

24. In the Data Validation dialog box, click on the Clear All button

25. Click OK

26. It removes data validation from the worksheet

27. Highlight cells D4:D10

28. On the Data tab, select Data Validation

29. Under Validation Criteria for Allow choose List

30. Click in the Source textbox and select cells A12:A14

31. Type in an Input Message Title of Enter Department

32. Include an Input Message of Choose the correct department

33. Include a Warning style prompt with the same Title and Message as the Input Message

34. Click OK

35. Click on cell D4

36. There is now a drop-down list containing department names for the cells D4:D10

37. Choose the Admin department for cell D4

38. Highlight cells H4:H10

39. Select Data Validation and for Allow choose Decimal

40. Allow values Between a Minimum of 18500 and a Maximum of 23000

41. Enter an Input Message of Enter Correct Amount for the Title and Enter between €18500 and €23000 for the Input Message

42. Enter the same Title and Input Message for the Error Alert and choose a Style of Stop

43. Click OK

44. Try and enter a value of €18000 into cell H5. A dialog prompt appears. Click on Retry

45. Now enter a value of €19500. It is allowed

46. Highlight cells I4:I10

47. Select Data Validation and for Allow choose Date

48. Allow a date that is Less than or Equal to 31/12/1980

49. Only dates of birth that are before this date are allowed

50. Enter in an appropriate Input Message and Error Alert

51. Click OK

52. Try entering a birth date in cell I4 after 31/12/1980

53. Excel will prompt you to change the date

54. Enter in the date 3/1/1979

55. The date entry is allowed

56. Select the cell range J4:J10

57. Display the Data Validation dialog box

58. For Allow choose Time

59. On the Settings tab, enter in a Start Time of 7:00

60. Enter in an End Time of 8:00

61. Enter in an appropriate Input Message and Error Alert

62. Click OK

63. In cell J4 enter in 9:00

64. It will not be allowed, and Excel prompts you to enter the correct time

65. Enter 7:30

66. It is allowed

67. Save the workbook and leave it open