en uk

Data Validation Microsoft Excel 2007

    Because formulas are only as accurate as the data they receive, it's important that your spreadsheet contains only valid data. Examples of invalid data might be a negative number (such as -9) for a price or a decimal number (such as 4.39) for the number of items a customer bought.

    To keep your spreadsheet from accepting invalid data, you can define a cell to accept only certain types of data, such as numbers that fall between 30 and 100. The moment someone tries to type invalid data into a cell, Excel immediately warns you, as shown in Figure 8-17.

    Data Validation Microsoft Excel 2007

    Figure 8-17:

    Excel warns you if you type invalid data in a cell.

    To define valid types of data for a cell, follow these steps:

    1. Click a cell that contains data used by a formula.

    2. Click the Data tab.

    3. Click the Data Validation icon in the Data Tools group.

    The Data Validation dialog box appears, as shown in Figure 8-18.

    Data Validation Microsoft Excel 2007

    Figure 8-18:

    Define the type and range of acceptable data allowed in a cell.

    4. Click the Allow list box and choose one of the following:

    Any Value: The default value accepts anything the user types

    Whole Number: Accepts only whole numbers, such as 47 and 903

    Decimal: Accepts whole and decimal numbers, such as 48.01 or 1.00

    List: Allows you to define a list of valid data

    Date: Accepts only dates

    Time: Accepts only times

    Text length: Defines a minimum and maximum length for text

    Custom: Allows you to define a formula to specify valid data

    Depending on the option you choose, you may need to define Minimum and Maximum values and whether you want the data to be equal to, less than, or greater than a defined limit.

    5. Click the Input Message tab in the Data Validation dialog box, as shown in Figure 8-19.

    6. Click in the Title text box and type a title.

    Data Validation Microsoft Excel 2007

    Figure 8-19:

    The Input Message tab lets you display a message explaining the type of valid data a cell can

    Hold.

    7. Click in the Input Message text box and type a message you want to display when someone selects this particular cell.

    8. Click the Error Alert tab in the Data Validation dialog box, as shown in Figure 8-20.

    9. Click the Style list box and choose an alert icon, such as Stop or Warning.

    10. Click in the Title text box and type a title for your error message.

    Data Validation Microsoft Excel 2007

    Figure 8-20:

    Define an error message to show if the user types invalid data into the cell.

    11. Click in the Error Message text box and type the message to appear if the user types invalid data into the cell.

    12. Click OK.

    After you define data validation for a cell, you can always remove it later. To remove validation for a cell, follow these steps:

    1. Click in the cell that contains data validation.

    2. Click the Data tab.

    3. Click the Data Validation icon in the Data Tools group.

    The Data Validation dialog box appears (refer to Figure 8-18).

    4. Click Clear All and then click OK.

    Excel clears all your data validation rules for your chosen cell.

    Charting and Analyzing Data

    If you stare at an Excel spreadsheet long enough, you may ask yourself, "What do these numbers really mean?"

    To help you analyze and understand what rows and columns of numbers might mean, Excel offers the ability to convert your data into a variety of charts such as pie charts, bar charts, and line charts. By letting you visualize your data, Excel helps you quickly understand what your data means so you can spot trends and patterns.




BACK NEXT TOP

Site is a private collection of materials and is an amateur informational and educational resource. All information is obtained from public sources. The administration does not apply for authorship of the materials used. All rights belong to their owners.