TEST 3 - Excel - Data Validation Tools

Data Validation allows you to stipulate what type of data is allowed within a cell. Which of the following is NOT a valid Validation Criteria type?

• Date and Time

• Whole Numbers

• Images or Shapes

• Months of the Year

Which of the following represent the three sections of Data Validation found within the Data Validation dialogue box?

• Settings, Input Message, Error Alert

• Dates & Times, Numbers and Text

• Data, Data Criteria, Data Validation

• Rows, Cells and Columns

Study the screenshot above. What is the message (circled in yellow) called?

• It is called a Comment.

• It is called a Cell Note.

• It is called an Error Alert Message.

• It is called an Input Message.

Where can I find the option to create a customized error message?

• Under the Insert tab on the Ribbon, in the Text group.

• Under the Review tab on the Ribbon, in the Changes group.

• Under the Data tab on the Ribbon, in the Data Tools group.

• All of the options listed above are correct.

Study the list circled in yellow in the screenshot above. How can you create this kind of dropdown list?

• Not easily, you will need to know computer programming languages to create this.

• It’s easy – type all the words you need into a cell, click on Insert and select List, to convert the words you typed into a list.

• It’s easy – in the Data Validation box, under Validation Criteria, select List. Insert the location for the list in the Source box.

• It’s not possible. Excel is an accounting program, not a data entry program.

To remove all data validation rules that have been applied to a worksheet, you must:

• Select the whole sheet and press the Delete button on your keyboard.

• Select the whole sheet and in the Data Validation dialogue box, select Clear All.

• Select the whole sheet, click the dropdown arrow next to the Data Validation button and select Clear Validation Circles.

• Select the whole sheet, right-click the sheet and select Clear Contents.

Study the screenshot above. Is it possible to change the wording of the error message?

• Yes, by typing the title and error message into the Error Alert section of the Data Validation dialogue box.

• Yes, by right-clicking the error message and selecting Edit from the menu list. Then type the relevant title and message into the box.

• No, this is a built-in function in Excel and cannot easily be edited.

• Yes, by creating an Error Message Text Box in the Text group under the Insert tab on the Ribbon.

Priscilla wants to ensure that in a Date column on her worksheet, dates that fall BEFORE 1 January 2015 cannot be entered. How can she achieve this?

• She should select Date as a Data Validation Criteria. It will automatically adjust the dates allowed to be after 1 January 2015.

• She should type 1 January 2015 into the first cell of the Date column and then select Date as a Data Validation Criteria.

• She should select Date as a Data Validation Criteria. Select Greater than or equal to in the Data box and insert 1 January 2015 as the Start Date

• She should select Date as a Data Validation Criteria. Select Less than or equal to in the Data box and insert 1 January 2015 as the Start Date.

Kirsty, an Office Manager, created a feedback form in Excel that everyone in the office will complete a copy of. She wants to prevent staff from writing more than 500 words in the comments box. How can she achieve this?

• She can insert an Input Message stating that no more than 500 characters are allowed.

• She can set Text Length to be Less than 500 characters.

• She can size the comment box so that users cannot enter more than 500 characters.

• She can insert an Error Message stating that no more than 500 characters are allowed.

To extend Data Validation, for example a Data Validation List, to cells that it is not applied to yet, I can:

• I. Recreate the Data Validation list by selecting the existing cells as well as the additional cells and reapplying the Data Validation Criteria to it.

• II. I can copy and paste a cell to which the Data Validation Criteria is applied, to additional cells.

• Both option I and II are correct

• Neither of the options listed are correct.