Excel Data Validation Questions
1. What is the main purpose of Data Validation in Excel?
A. To format cells
B. To filter data
C. To control the type of data entered
D. To calculate totals
Correct Answer: C
2. Which of the following is a benefit of using Data Validation?
A. Automatically saves your workbook
B. Prevents incorrect or unexpected data entry
C. Sorts your data
D. Converts numbers to text
Correct Answer: B
3. Which tab contains the Data Validation option in Excel?
A. Review
B. Insert
C. Data
D. Home
Correct Answer: C
4. What can you use the “Input Message” tab for in Data Validation?
A. To hide the cell
B. To insert comments
C. To show a message when the cell is selected
D. To color the cell
Correct Answer: C
5. Where do you select the validation type in the Data Validation dialog box?
A. Under the View tab
B. On the Home ribbon
C. In the Settings tab
D. In the Review tab
Correct Answer: C
6. What validation type is used to allow only numbers between 1 and 100?
A. Decimal
B. List
C. Whole number
D. Text length
Correct Answer: C
7. What values must be set for minimum and maximum in this example?
A. 10 and 50
B. 1 and 100
C. 0 and 200
D. 5 and 500
Correct Answer: B
8. What happens if a user enters 101 in a cell validated for values between 1 and 100?
A. It will be accepted
B. It will be rounded
C. It will be rejected
D. It will be converted to 100
Correct Answer: C
9. What type of validation is used to create a drop-down list?
A. Custom
B. List
C. Decimal
D. Text length
Correct Answer: B
10. How can you define the list values in a drop-down?
A. Type values separated by commas or select a cell range
B. Insert hyperlinks
C. Use the Insert tab
D. Use the formula bar
Correct Answer: A
11. What appears in the cell after applying list validation?
A. A red border
B. A tooltip
C. A drop-down arrow
D. A pop-up window
Correct Answer: C
12. Which validation type allows only specific dates to be entered?
A. Whole number
B. List
C. Date
D. Text length
Correct Answer: C
13. If a date outside the allowed range is entered, what will happen?
A. It will be highlighted in red
B. It will be rejected
C. It will be converted
D. It will trigger a macro
Correct Answer: B
14. Which validation type is used to limit the number of characters in a cell?
A. Whole number
B. Text length
C. Custom
D. List
Correct Answer: B
15. What is the maximum character limit set in this example?
A. 5
B. 8
C. 10
D. 15
Correct Answer: C
16. What happens if the user types 12 characters in the validated cell?
A. The text is accepted
B. The text is cut off
C. The input is rejected
D. The font is reduced
Correct Answer: C
17. What validation type is used to allow only even numbers?
A. Whole number
B. List
C. Custom
D. Decimal
Correct Answer: C
18. Which formula is used in this example to allow only even numbers?
A. =E1*2
B. =ISNUMBER(E1)
C. =MOD(E1,2)=0
D. =E1=EVEN
Correct Answer: C
19. What happens if an odd number is entered in the validated cell?
A. It is accepted
B. It is rejected
C. It is rounded to the nearest even number
D. It is converted to zero
Correct Answer: B
20. What is the purpose of the “Error Alert” feature in Data Validation?
A. To protect the worksheet
B. To format the data
C. To display a message when invalid data is entered
D. To allow copying of invalid values
Correct Answer: C
21. Which tab allows you to set the error alert in the Data Validation dialog box?
A. Input Message
B. Settings
C. Error Alert
D. Formulas
Correct Answer: C
22. What must be checked to enable error alerts?
A. Allow all values
B. Highlight invalid cells
C. Show error alert after invalid data is entered
D. Enable macros
Correct Answer: C
23. Which style will completely prevent invalid data input?
A. Warning
B. Stop
C. Information
D. Notify
Correct Answer: B
24. Which error alert style allows the user to proceed with the input?
A. Stop
B. Reject
C. Information
D. Confirm
Correct Answer: C
25. What is the effect of using the “Warning” style in an error alert?
A. Rejects input immediately
B. Hides the invalid entry
C. Displays a warning but accepts the value
D. Locks the workbook
Correct Answer: C
26. What is shown in the 'Title' field of the Error Alert tab?
A. The input cell value
B. The name of the worksheet
C. The popup window title
D. The name of the user
Correct Answer: C
27. What is shown in the 'Error message' field?
A. The function result
B. A custom message to the user
C. A log file
D. The cell address
Correct Answer: B
28. Which style was selected in the error alert example for numbers between 1 and
100?
A. Warning
B. Stop
C. Information
D. Notify
Correct Answer: B
29. What is the result if a user enters '200' in the validated cell?
A. It is accepted silently
B. A custom popup message appears
C. It is converted to 100
D. The workbook closes
Correct Answer: B
30. What does the custom error message in the example advise the user to do?
A. Enter text instead of numbers
B. Enter only numbers between 1 and 100
C. Restart Excel
D. Use formulas instead
Correct Answer: B
