One app frequently used for data entry tasks is Microsoft Excel. With Microsoft Excel, you can create a data form, document information on the form, and use other tools in Excel to analyze the information. However, the analysis done on Microsoft Excel will only be as accurate as the data provided.
One way to ensure data accuracy and appropriateness is by using data validation. The data validation feature in Microsoft Excel restricts the information that can be provided in a cell. It can also give a user instructions on what to enter in a cell. Here’s how to use data validation in Microsoft Excel.
How to Setup a Data Validation Rule in Microsoft Excel
With data validation, you can limit the type of information you want in a cell. For example, you can limit a cell only to accept a date, a number, a decimal, etc. You can also create a drop-down using data validation. Here’s how to do so:
Step 1: Click the Start button on the Taskbar to open the Start menu and type excel to search for the app.
Step 2: From the results, click on the Microsoft Excel app or workbook to open it.
Step 3: On the Excel workbook, select the cell or cells you would like to introduce a data validation rule to.
Step 4: On the Excel Ribbon, click the Data tab.
Step 5: In the Data Tools group, click the Data Validation drop-down.
Step 6: From the drop-down, click Data Validation.
Step 7: On the Data Validation pop-up, click the Settings tab.
Step 8: Click the drop-down beneath the Allow menu.
Step 9: Select any of the following from the drop-down:
- Any value: No data validation criteria applied to the cells.
- Whole number: Allows a whole number between the minimum and maximum values set.
- Decimal: Allows a decimal between the minimum and maximum values set.
- List: Allows the creation of a drop-down list for users to choose values from.
- Date: Allows a data within the minimum and maximum values set.
- Time: Allows a time within the minimum and maximum values set.
- Text Length: Allows text with a specific character count.
- Custom: Allows users to set a custom formula.
If you selected List and Custom, skip to Step 10 after entering your List Source or Formula in the provided grid. If you selected options Whole Number, Decimal, Date, Time, and Text Length, proceed to Step 12.
Step 10: Click the drop-down beneath the Data menu and select any of these conditions:
- between: values allowed into the cell must be within the specified range.
- not between: values allowed into the cell must be outside the specified range,
- equal to: value entered into the cell must be exactly as specified.
- not equal to: value entered into the cell must be different from the specified limit.
- greater than: value entered into the cell must be above the specified minimum.
- less than: value entered into the cell must be below the specified maximum.
- greater than or equal to: value entered into the cell either must be above or equal to the specified minimum.
- less than or equal to: value entered into the cell must either be below or equal to the specified maximum.
Step 11: In the Minimum and/or Maximum fields, enter your data limits.
Step 12: Click OK to save your changes.
How to Add Input Messages for Data Validation in Microsoft Excel
Now that data validation is set in your Excel, the data provided must fit your specified limits. However, you can set up a display message when others click on the validation cells. This way, they know what type of data is needed. Here’s how you can configure an input message.
Step 1: On the Excel spreadsheet, select the cells where you want an input message to appear.
Step 2: On the Excel Ribbon, click the Data tab.
Step 3: In the Data Tools group, click the Data Validation drop-down.
Step 4: From the drop-down, click Data Validation.
Step 5: On the Data Validation pop-up, click the Input Message tab.
Step 6: Tick the box beside ‘Show input message when cell is selected’.
Step 7: In both the Text and Input message fields, enter your preferred text.
Step 8: Click OK to save your changes.
How to Add Error Messages for Data Validation in Microsoft Excel
When users enter the wrong information in a data validation cell, you can configure the error message that appears. Here’s how to do so:
Step 1: On the Excel spreadsheet, select the cells where you want an input message to appear.
Step 2: On the Excel Ribbon, click the Data tab.
Step 3: In the Data Tools group, click the Data Validation drop-down.
Step 4: From the drop-down, click Data Validation.
Step 5: On the Data Validation pop-up, click the Error Alert tab.
Step 6: Tick the box beside ‘Show error alert after invalid data is entered.
Step 7: Click the drop-down beneath the Style field and select the symbol you would like to show for the error message from the following options:
- Stop
- Warning
- Information
Step 8: In the Title and Error Message fields, enter your preferred text.
Step 9: Click OK to save your changes.
How to Remove Data Validation Rules in Microsoft Excel
Here’s how you can remove the data validation rule from a cell in Microsoft Excel:
Step 1: On the Excel spreadsheet, select the cells you would like to remove data validation rules from.
Step 2: On the Excel Ribbon, click the Data tab.
Step 3: In the Data Tools group, click the Data Validation drop-down.
Step 4: From the drop-down, click Data Validation.
Step 5: On the Data Validation pop-up, click the Settings tab.
Step 6: Click the Clear All button at the bottom of the pop-up to clear all entries.
Step 7: Click OK to save your changes.
Using Version History in Microsoft Excel
Unlike Microsoft Word, the track changes feature in Microsoft Excel isn’t quite detailed. This makes it hard to pinpoint changes made to an Excel by reviewing the change history. However, you can use the version history of your Excel file to compare and identify differences between files.
Categories: How to
Source: thpttranhungdao.edu.vn/en/
