You are here: Home / Archives for Validation

Data validation in Excel 2007 – Date range

September 10, 2009 By

If you want to validate a cell to restrict data entry to a date range then follow these steps:

  1. Select the cell you want to validate.
  2. On the Data tab, in the Data Tools group, click Data Validation.

    3. xl7validation

  3. The Data Validation dialog box will open.

    4. xl7validdate1

  4. In the Data Validation dialog box, click the Settings tab.
  5. Click on the Allow box then select Date from the drop-down list.
  6. Click the Data box and then select an option from the drop down list e.g. “Between” , “Greater than”, …etc.
  7. In the Start Date box enter a valid date compatible with your regional settings for the date.
  8. In the End Date box enter a valid date compatible with your regional settings for the date (this box is not applicable for some options).
  9. You can enter a formula that returns a date for Start Date or End Date, for example “=Today()+7″.

Notes:

  1. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
  2. If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.

More Validation options:

  1. Display an input message when the cell is clicked.
    1. Click the Input Message tab.
    2. Select Show input message when cell is selected check box.
    3. Fill in the Title and text for the Input message.
  2. Display an error message when wrong data is entered.
    1. Click the Error Alert tab.
    2. Select Show error alert after invalid data is entered check box.
    3. Fill in the Title and text for the Error message.
    4. Select one of the following options for the Style box:
      1. Information: Display an information message. Does not prevent entry of invalid data.
      2. Warning: Display a warning message. Does not prevent entry of invalid data.
      3. Stop : Prevent entry of invalid data.

Tip

If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so

  1. Open the Data Validation dialog box.
  2. Click the Settings tab.
  3. Select the Apply these changes to all other cells with the same settings check box.

Filed Under: Data, Excel 2007, Validation Tagged With: , , ,

Find cells that meet specific criteria in Excel 2007

September 6, 2009 By

To find cells that meet a specific criteria, for example cells with conditional formatting or cells which have data validation, proceed as follows:

  1. In the Home tab Editing group click Find and Select.

    2. xl7find

  2. Click on:
    1. Formulas : to find cells that contain formulas.
    2. Comments : to find cells that contain comments.
    3. Conditional Formatting : to find cells that have Conditional Formatting.
    4. Constants : to find cells that contain constants.
    5. Data Validation : to find cells that have data validation.
  3. More options are available if you click Go To Special. Then you will get the Go To Special dialogue where you can specify specific criteria for the cells to be found and selected.

Note:

You can search the entire sheet for the specified criteria or you can limit your search by selecting a range of cells.

Filed Under: Excel 2007, Formatting, Formulas, General, Validation Tagged With: , , , ,

Data validation in Excel 2007 – Drop down list

August 13, 2009 By

If you want to validate a cell to restrict data entry to values in a drop-down list follow these steps:

  1. Select the cell you want to validate.
  2. On the Data tab, in the Data Tools group, click Data Validation.

    3. xl7validation

  3. The Data Validation dialog box will open.

    4. xl7validation2

  4. In the Data Validation dialog box, click the Settings tab.
  5. Click on the Allow box then select List from the drop-down list.
  6. Click the Source box and then type the valid values separated by the appropriate list separator character depending on your installation( usually a comma “,” or semicolon “;”). For example if the cell is for a color of a car then you can limit the values by entering : Silver, Green, Blue.
  7. Instead of typing your list manually, you can also create the list entries by referring to a range of cells in the same worksheet or another worksheet in the workbook.
  8. To specify the location of the list of valid entries, do one of the following:
    1. If the list is in the current worksheet, enter a reference to your list in the Source box, for example enter: =$A$1:$A$6.
    2. If the list is on a different worksheet, define a name for your list then enter the name that you defined for your list in the Source box, for example, enter: =ValidProjects.

Notes:

  1. Make sure that the In-cell dropdown check box is selected. Otherwise, you won’t be able to see the drop-down arrow next to the cell.
  2. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
  3. If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.

More Validation options:

  1. Display an input message when the cell is clicked.
    1. Click the Input Message tab.
    2. Select Show input message when cell is selected check box.
    3. Fill in the Title and text for the Input message.
  2. Display an error message when wrong data is entered.
    1. Click the Error Alert tab.
    2. Select Show error alert after invalid data is entered check box.
    3. Fill in the Title and text for the Error message.
    4. Select one of the following options for the Style box:
      1. Information: Display an information message. Does not prevent entry of invalid data.
      2. Warning: Display a warning message. Does not prevent entry of invalid data.
      3. Stop : Prevent entry of invalid data.

Tip

If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so

  1. Open the Data Validation dialog box.
  2. Click the Settings tab.
  3. Select the Apply these changes to all other cells with the same settings check box.

Filed Under: Data, Excel 2007, Validation Tagged With: , ,

How to check spelling using the dictionary

February 21, 2009 By

Microsoft Excel cannot check spelling automatically as you type as you see in Microsoft Word for example. However you can check spelling all at once by selecting so from the Tools menu.

When you check spelling, Excel checks the entire active worksheet, including cell values, cell comments, embedded charts, text boxes, buttons, and headers and footers. However, Excel does not check protected worksheets, formulas, or text that results from a formula.

To check worksheet data for spelling errors, do one of the following:

1. Select the range you want to check:

  • To check the entire worksheet, click any cell.
  • To check a specific area of the worksheet, select the range of cells, rows, or columns that you want to check.
  • To check the contents of the formula bar only, activate the formula bar by pressing F2 (PF key 2).

2. On the Tools menu click Spelling.

3. When Excel finds a spelling error, it will display the Spelling dialog box. The suspected word will be shown and also a list of suggested words as an alternative to the word in error. You then have a number of options depending on which of these buttons you click:

spelling

  • Ignore Once: The current occurrence of the word will be ignored regarding spelling checking
  • Ignore All: All occurrences of the word will be ignored.
  • Add to Dictionary: The word will be added to the custom dictionary so that next time it will not be considered as an error.
  • Change: The word in error will be replaced with the suggested word you select from the list.
  • Change All: All occurrences of the word in error will be replaced with the suggested word you select from the list.
  • AutoCorrect: The word in error will be replaced with suggested word you select from the list. In addition to that, the two words will be added to the AutoCorrect list and from now own correction will be done automatically.

4. If you want to check the spelling of text in another language, in the Dictionary language box, click the language whose dictionary you want to use.

Applies to: Excel 2003

Filed Under: General, Validation Tagged With: ,

How to Find cells that have data validation

August 10, 2008 By

If you want to find cells in your worksheet that have data validation, then follow these steps:

1. On the Edit menu, click Go To. The Go To window will open.

2. Click Special. The Go To Special window will open.

gotospecialv

3. Click Data validation.
4. At the bottom of the window click on the radio button that says All.

All cells with data validation will be highlighted.

To find cells that match certain data validation settings then you follow similar steps with slight variations:

1. Click the cell that has the data validation settings for which you want to find matches.
2. On the Edit menu, click Go To. The Go To window will open.
3. Click Special. The Go To Special window will open.
4. Click Data validation.
5. At the bottom of the window click on the radio button that says Same.

All cells with matching data validation will be highlighted.

Filed Under: Data, Validation Tagged With: , ,

How to validate a cell based on the value of another cell

June 16, 2008 By

In a previous post I explained how to validate a cell using a fixed range of data. In this post we will see how to validate a cell based on the value of another cell.

Suppose we have a list of documents each with issue date and expiry date. We want to validate the expiry date so that it is always greater than the issue date.

Validation

 

To validate the expiry date in cell C2 follow these steps:

  1. Select the cell.
  2. On the Data menu select Validation.
  3. Select the Settings tab.
  4. In the Allow list box select Date.
  5. In the Data list box select Greater Than.
  6. In the Start Date: box type “=B2″. This refers to cell “B2″ which have the corresponding issue date.

Note:

You can put an error message in the Error Alert tab.

Applies to: Excel 2003

 

Filed Under: Data, Validation Tagged With:

How to validate a cell value

April 8, 2008 By

If you are designing an excel sheet for other users to enter data, or even for your own use, it is a good idea to use the data validation feature to insure that only correct data will be entered. hear is how you do it:

  1. Select the cell you want to validate.
  2. On the Data menu, click Validation, the Data Validation dialogue box will be shown.

  1. Click the Settings tab.
  2. Specify the type of validation you want. Suppose you have a numeric value and you want to allow only values between 1 and 99, then:
    1. In the Allow combo box select ‘Decimal’.
    2. In the Data combo box select ‘Between’.
    3. In the Minimum edit box enter ‘1’.
    4. In the Maximum edit box enter ‘99’.

To show an error message when an invalid data is entered:

  1. Click the Error Alert tab.
  2. Fill the Title and Error Message edit boxes with appropriate text. (See example).

To remove data validation, click the Clear All button in the Settings tab.

Applies to : Excel 2003

Filed Under: Data, Validation Tagged With: , , ,