Subscribe Us

Enter your email address:

Loading

Create Pivot Tables in Microsoft Excel 2007

Labels: , , ,

An introductory video (youtube) by bytebodger describing the use and creation of pivot tables in Excel 2007.

'



Apply data validation to cells

Labels: , , , ,

You use data validation to control the type of data or the values that users enter into a cell. For example, you may want to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that only positive whole numbers are entered.
This article describes how data validation works in Excel and outlines the different data validation techniques available to you. It does not cover cell protection, which is a feature that lets you "lock" or hide certain cells in a worksheet so that they can't be edited or overwritten.

What is data validation?

Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. You can configure data validation to prevent users from entering data that is not valid. If you prefer, you can allow users to enter invalid data but warn them when they try to type it in the cell. You can also provide messages to define what input you expect for the cell, and instructions to help users correct any errors.
For example, in a marketing workbook, you can set up a cell to allow only account numbers that are exactly three characters long. When users select the cell, you can show them a message such as this one:
Selected cell and input message
If users ignore this message and type invalid data in the cell, such as a two-digit or five-digit number, you can show them an actual error message.
In a slightly more advanced scenario, you might use data validation to calculate the maximum allowed value in a cell based on a value elsewhere in the workbook. In the following example, the user has typed $4,000 in cell E7, which exceeds the maximum limit specified for commissions and bonuses.
Invalid data and warning message
If the payroll budget were to increase or decrease, the allowed maximum in E7 would automatically increase or decrease with it.
Data validation options are located in the Data Tools group.
Excel Ribbon Image
You configure data validation in the Data Validation dialog box.
Data Validation dialog box

When is data validation useful?

Data validation is invaluable when you want to share a workbook with others in your organization, and you want the data entered in the workbook to be accurate and consistent.
Among other things, you can use data validation to do the following:
  • Restrict data to predefined items in a list    For example, you can limit types of departments to Sales, Finance, R&D, and IT. Similarly, you can create a list of values from a range of cells elsewhere in the worksheet.
Drop-down list created by using data validation
  • Restrict numbers outside a specified range    For example, you can specify a minimum limit of deductions to two times the number of children in a particular cell.
  • Restrict dates outside a certain time frame    For example, you can specify a time frame between today's date and 3 days from today's date.
  • Restrict times outside a certain time frame     For example, you can specify a time frame for serving breakfast between the time when the restaurant opens and 5 hours after the restaurant opens.
  • Limit the number of text characters    For example, you can limit the allowed text in a cell to 10 or fewer characters. Similarly, you can set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1), plus 10 characters.
  • Validate data based on formulas or values in other cells    For example, you can use data validation to set a maximum limit for commissions and bonuses of $3,600, based on the overall projected payroll value. If users enter more than $3,600 in the cell, they see a validation message.

Data validation messages

What users see when they enter invalid data into a cell depends on how you have configured the data validation. You can choose to show an input message when the user selects the cell. This type of message appears near the cell. You can move this message, if you want to, and it remains until you move to another cell or press ESC.
Input message
Input messages are generally used to offer users guidance about the type of data that you want entered in the cell.
You can also choose to show an error alert that appears only after users enter invalid data.
Error alert with custom warning message
You can choose from three types of error alerts:
Icon
Type
Use to
Stop icon
Stop
Prevent users from entering invalid data in a cell. A Stop alert message has two options: Retry or Cancel.
Warning icon
Warning
Warn users that the data they entered is invalid, without preventing them from entering it. When a Warning alert message appears, users can click Yes to accept the invalid entry, No to edit the invalid entry, or Cancel to remove the invalid entry.
Information icon
Information
Inform users that the data they entered is invalid, without preventing them from entering it. This type of error alert is the most flexible. When an Information alert message appears, users can click OK to accept the invalid value or Cancel to reject it.
You can customize the text that users see in an error alert message. If you choose not to do so, users see a default message.
Input messages and error alerts appear only when data is typed directly into the cells. They do not appear under the following conditions:

How to handle a data validation alert

When you try to enter or change data in a worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) cell, you see a data validation error alert. This alert signifies that the owner of the workbook applied data validation (data validation: An Excel feature that you can use to define restrictions on what data can or should be entered in a cell, and to display messages that prompt users for correct entries and notify users about incorrect entries.) to the cell to prevent users from entering invalid data, and implemented the error alert to let you know that the data you entered is invalid.
You can enter only valid data in cells that have data validation applied. If you are not clear about the validity of the data that you can enter, you should contact the owner of the workbook.
If you inherited the workbook, you can modify or remove the data validation unless the worksheet is protected with a password that you do not know. If possible, you can contact the previous owner to help you unprotect the worksheet. You can also copy the data to another worksheet, and then remove the data validation.

Tips for working with data validation

In the following list, you will find tips and tricks for working with data validation in Excel.
  • If you plan to protect (protect: To make settings for a worksheet or workbook that prevent users from viewing or gaining access to the specified worksheet or workbook elements.) the worksheet or workbook, protect it after you have finished specifying any validation settings. Make sure that you unlock any validated cells before you protect the worksheet. Otherwise, users will not be able to type any data in the cells.
  • If you plan to share the workbook, share it only after you have finished specifying data validation and protection settings. After you share a workbook, you won't be able to change the validation settings unless you stop sharing, but Excel will continue to validate the cells that you have designated while the workbook is being shared.
  • You can apply data validation to cells that already have data entered in them. However, Excel does not automatically notify you that the existing cells contain invalid data. In this scenario, you can highlight invalid data by instructing Excel to circle it on the worksheet. Once you have identified the invalid data, you can hide the circles again. If you correct an invalid entry, the circle disappears automatically.
Red circle around invalid data
  • To quickly remove data validation for a cell, select it, and then open the Data Validation dialog box (Data tab, Data Tools group). On the Settings tab, click Clear All.
  • To find the cells on the worksheet that have data validation, on the Home tab, in the Editing group, click Find & Select, and then click Data Validation. After you have found the cells that have data validation, you can change, copy, or remove validation settings.
  • When creating a drop-down list, you can use the Define Name command (Formulas tab, Defined Names group) to define a name for the range that contains the list. After you create the list on another worksheet, you can hide the worksheet that contains the list and then protect the workbook so that users won't have access to the list.
If data validation isn't working, make sure that:
Users are not copying or filling data     Data validation is designed to show messages and prevent invalid entries only when users type data directly in a cell. When data is copied or filled, the messages do not appear. To prevent users from copying and filling data by dragging and dropping cells, clear the Enable fill handle and cell drag-and-drop check box (Excel Options dialog box, Advanced options), and then protect the worksheet.
Manual recalculation is turned off     If manual recalculation is turned on, uncalculated cells can prevent data from being validated correctly. To turn off manual recalculation, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic.
Formulas are error free     Make sure that formulas in validated cells do not cause errors, such as #REF! or #DIV/0!. Excel ignores the data validation until you correct the error.
Cells referenced in formulas are correct     If a referenced cell changes so that a formula in a validated cell calculates an invalid result, the validation message for the cell won't appear.





Add data validation to a cell or range

In the next sections, you will learn the different techniques for adding validation to cells in a worksheet.


Restrict data entry to values in a drop-down list

It is not possible to change the font or font size for items in a list.
  1. Select one or more cells to validate.
  1. On the Data tab, in the Data Tools group, click Data Validation.
Excel Ribbon Image
An Excel table might be linked to a SharePoint site     You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.
You might currently be entering data     The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering data, press ENTER or ESC.
The worksheet might be protected or shared     You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.
  1. In the Data Validation dialog box, click the Settings tab.
  1. In the Allow box, select List.
  2. Click the Source box and then type the list values separated by the Microsoft Windows list separator character (commas by default).
For example:
  • To limit entry to a question, such as "Do you have children?", to two choices, type Yes, No.
  • To limit a vendor's quality reputation to three ratings, type Low, Average, High.
You can also create the list entries by referring to a range of cells elsewhere in the workbook.
 Note   The width of the drop-down list is determined by the width of the cell that has the data validation. You might need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.
  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.
 Note   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.
  1. Optionally, display an input message when the cell is clicked.
How to display an input message
  1. Click the Input Message tab.
  2. Make sure the Show input message when cell is selected check box is selected.
  3. Fill in the title and text for the message.
  1. Specify how you want Microsoft Office Excel to respond when invalid data is entered.
How to specify a response to invalid data
  1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
 Note   If you want to allow users to type entries that are not in the list, clear the Show error alert after invalid data is entered check box instead.
  1. Select one of the following options for the Style box:
    • To display an information message that does not prevent entry of invalid data, select Information.
    • To display a warning message that does not prevent entry of invalid data, select Warning.
    • To prevent entry of invalid data, select Stop.
  2. Fill in the title and text for the message (up to 225 characters).
 Note   If you don't enter a title or text, the title uses the default string "Microsoft Excel" and the message is (by default): "The value you entered is not valid. A user has restricted values that can be entered into this cell."
  1. Test the data validation to make sure that it is working correctly.
Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.
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, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Restrict data entry to a whole number within limits

  1. Select one or more cells to validate.
  1. On the Data tab, in the Data Tools group, click Data Validation.
Excel Ribbon Image
Issue: The Data Validation command is unavailable
An Excel table may be linked to a SharePoint site     You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.
You may be currently entering data     The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.
The worksheet might be protected or shared     You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.
  1. In the Data Validation dialog box, click the Settings tab.
  1. In the Allow box, select Whole number.
  2. In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.
  3. Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value.
For example, to set a minimum limit of deductions to two times the number of children in cell F1, select greater than or equal to in the Data box and enter the formula, =2*F1, in the Minimum box.
  1. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
 Note   If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values 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, setting the Ignore blank check box allows any values to be entered in the validated cell.
  1. Optionally, display an input message when the cell is clicked.
How to display an input message
  1. Click the Input Message tab.
  2. Make sure the Show input message when cell is selected check box is selected.
  3. Fill in the title and text for the message.
  1. Specify how you want Microsoft Office Excel to respond when invalid data is entered.
How to specify a response to invalid data
  1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
  2. Select one of the following options for the Style box:
    • To display an information message that does not prevent entry of invalid data, select Information.
    • To display a warning message that does not prevent entry of invalid data, select Warning.
    • To prevent entry of invalid data, select Stop.
  3. Fill in the title and text for the message (up to 225 characters).
 Note   If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."
  1. Test the data validation to make sure that it is working correctly.
Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.
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, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Restrict data entry to a decimal number within limits

  1. Select one or more cells to validate.
  1. On the Data tab, in the Data Tools group, click Data Validation.
Excel Ribbon Image
Issue: The Data Validation command is unavailable
An Excel table may be linked to a SharePoint site     You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.
You may be currently entering data     The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.
The worksheet might be protected or shared     You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.
  1. In the Data Validation dialog box, click the Settings tab.
  1. In the Allow box, select Decimal.
  2. In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.
  3. Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value.
For example, to set a maximum limit for commissions and bonuses of 6% of a salesperson's salary in cell E1, select less than or equal to in the Data box and enter the formula, =E1*6%, in the Maximum box.
 Note   To let a user enter percentages, for example 20%, select Decimal in the Allow box, select the type of restriction that you want in the Data box, enter the minimum, maximum, or specific value as a decimal, for example .2, and then display the data validation cell as a percentage by selecting the cell and clicking Percent Style Button imagein the Number group on the Home tab.
  1. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
 Note   If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values 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, setting the Ignore blank check box allows any values to be entered in the validated cell.
  1. Optionally, display an input message when the cell is clicked.
How to display an input message
  1. Click the Input Message tab.
  2. Make sure the Show input message when cell is selected check box is selected.
  3. Fill in the title and text for the message.
  1. Specify how you want Microsoft Office Excel to respond when invalid data is entered.
How to specify a response to invalid data
  1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
  2. Select one of the following options for the Style box:
    • To display an information message that does not prevent entry of invalid data, select Information.
    • To display a warning message that does not prevent entry of invalid data, select Warning.
    • To prevent entry of invalid data, select Stop.
  3. Fill in the title and text for the message (up to 225 characters).
 Note   If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."
  1. Test the data validation to make sure that it is working correctly.
Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.
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, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.




Restrict data entry to a date within a time frame

  1. Select one or more cells to validate.
  1. On the Data tab, in the Data Tools group, click Data Validation.
Excel Ribbon Image
Issue: The Data Validation command is unavailable
An Excel table may be linked to a SharePoint site     You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.
You may be currently entering data     The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.
The worksheet might be protected or shared     You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.
  1. In the Data Validation dialog box, click the Settings tab.
  1. In the Allow box, select Date.
  2. In the Data box, select the type of restriction that you want. For example, to allow dates after a certain day, select greater than.
  3. Enter the start, end, or specific date to allow. You can also enter a formula that returns a date.
For example, to set a time frame between today's date and 3 days from today's date, select between in the Data box, enter =TODAY() in the Minimum box, and enter =TODAY()+3 in the Maximum box.
  1. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
 Note   If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values 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, setting the Ignore blank check box allows any values to be entered in the validated cell.
  1. Optionally, display an input message when the cell is clicked.
How to display an input message
  1. Click the Input Message tab.
  2. Make sure the Show input message when cell is selected check box is selected.
  3. Fill in the title and text for the message.
  1. Specify how you want Microsoft Office Excel to respond when invalid data is entered.
How to specify a response to invalid data
  1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
  2. Select one of the following options for the Style box:
    • To display an information message that does not prevent entry of invalid data, select Information.
    • To display a warning message that does not prevent entry of invalid data, select Warning.
    • To prevent entry of invalid data, select Stop.
  3. Fill in the title and text for the message (up to 225 characters).
 Note   If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."
  1. Test the data validation to make sure that it is working correctly.
Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.
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, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Restrict data entry to a time within a time frame

  1. Select one or more cells to validate.
  1. On the Data tab, in the Data Tools group, click Data Validation.
Excel Ribbon Image
Issue: The Data Validation command is unavailable
An Excel table may be linked to a SharePoint site     You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.
You may be currently entering data     The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.
The worksheet might be protected or shared     You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.
  1. In the Data Validation dialog box, click the Settings tab.
  1. In the Allow box, select Time.
  2. In the Data box, select the type of restriction that you want. For example, to allow times before a certain time of day, select less than.
  3. Enter the start, end, or specific time to allow. You can also enter a formula that returns a time.
For example, to set a time frame for serving breakfast between the time when the restaurant opens (the value in cell H1) and five hours after that, select between in the Data box, enter =H1 in the Minimum box, and then enter =H1+"5:00" in the Maximum box.
  1. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
 Note   If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values 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, setting the Ignore blank check box allows any values to be entered in the validated cell.
  1. Optionally, display an input message when the cell is clicked.
How to display an input message
  1. Click the Input Message tab.
  2. Make sure the Show input message when cell is selected check box is selected.
  3. Fill in the title and text for the message.
  1. Specify how you want Microsoft Office Excel to respond when invalid data is entered.
How to specify a response to invalid data
  1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
  2. Select one of the following options for the Style box:
    • To display an information message that does not prevent entry of invalid data, select Information.
    • To display a warning message that does not prevent entry of invalid data, select Warning.
    • To prevent entry of invalid data, select Stop.
  3. Fill in the title and text for the message (up to 225 characters).
 Note   If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."
  1. Test the data validation to make sure that it is working correctly.
Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.
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, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Restrict data entry to text of a specified length

  1. Select one or more cells to validate.
  1. On the Data tab, in the Data Tools group, click Data Validation.
Excel Ribbon Image
Issue: The Data Validation command is unavailable.
An Excel table may be linked to a SharePoint site     You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.
You may be currently entering data     The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.
The worksheet might be protected or shared     You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.
  1. In the Data Validation dialog box, click the Settings tab.
  1. In the Allow box, select Text Length.
  2. In the Data box, select the type of restriction that you want. For example, to allow up to a certain number of characters, select less than or equal to.
  3. Enter the minimum, maximum, or specific length for the text. You can also enter a formula that returns a number value.
For example, to set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1) plus 10, select less than or equal to in the Data box and enter =SUM(LEN(A1),LEN(B1),10) in the Maximum box.
  1. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
 Note   If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values 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, setting the Ignore blank check box allows any values to be entered in the validated cell.
  1. Optionally, display an input message when the cell is clicked.
How to display an input message
  1. Click the Input Message tab.
  2. Make sure the Show input message when cell is selected check box is selected.
  3. Fill in the title and text for the message.
  1. Specify how you want Microsoft Office Excel to respond when invalid data is entered.
How to specify a response to invalid data
  1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
  2. Select one of the following options for the Style box:
    • To display an information message that does not prevent entry of invalid data, select Information.
    • To display a warning message that does not prevent entry of invalid data, select Warning.
    • To prevent entry of invalid data, select Stop.
  3. Fill in the title and text for the message (up to 225 characters).
 Note   If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."
  1. Test the data validation to make sure that it is working correctly.
Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.
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, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.

Calculate what is allowed based on the content of another cell

  1. Select one or more cells to validate.
  1. On the Data tab, in the Data Tools group, click Data Validation.
Excel Ribbon Image
Issue: The Data Validation command is unavailable
An Excel table may be linked to a SharePoint site     You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.
You may be currently entering data     The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.
The worksheet might be protected or shared     You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.
  1. In the Data Validation dialog box, click the Settings tab.
  1. In the Allow box, select the type of data that you want.
  2. In the Data box, select the type of restriction that you want.
  3. In the box or boxes below the Data box, click the cell that you want to use to specify what is allowed.
For example, to allow entries for an account only if the result won't go over the budget in cell E4, select Decimal for Allow, select less than or equal to for Data, and in the Maximum box, enter =E4.
  1. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
 Note   If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values 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, setting the Ignore blank check box allows any values to be entered in the validated cell.
  1. Optionally, display an input message when the cell is clicked.
How to display an input message
  1. Click the Input Message tab.
  2. Make sure the Show input message when cell is selected check box is selected.
  3. Fill in the title and text for the message.
  1. Specify how you want Microsoft Office Excel to respond when invalid data is entered.
How to specify a response to invalid data
  1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
  2. Select one of the following options for the Style box:
    • To display an information message that does not prevent entry of invalid data, select Information.
    • To display a warning message that does not prevent entry of invalid data, select Warning.
    • To prevent entry of invalid data, select Stop.
  3. Fill in the title and text for the message (up to 225 characters).
 Note   If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."
  1. Test the data validation to make sure that it is working correctly.
Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.
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, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.



Use a formula to calculate what is allowed

  1. Select one or more cells to validate.
  1. On the Data tab, in the Data Tools group, click Data Validation.
Excel Ribbon Image
Issue: The Data Validation command is unavailable.
An Excel table may be linked to a SharePoint site     You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.
You may be currently entering data     The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.
The worksheet might be protected or shared     You cannot change data validation settings if your workbook is shared or protected. To learn how to stop sharing or protecting a workbook, see the links in the See Also section.
  1. In the Data Validation dialog box, click the Settings tab.
  1. In the Allow box, select Custom.
  2. In the Formula box, enter a formula that calculates a logical value (TRUE for valid or FALSE for invalid entries). For example:
To make sure that
Enter this formula
The cell for the picnic account (B1) can only be updated if nothing is budgeted for the discretionary account (D1) and the total budget (D2) is less than the $40,000 allocated.
=AND(D1=0,D2<40000)
The cell that contains a product description (B2) only contains text.
=ISTEXT(B2)
For the cell that contains a projected advertising budget (B3), the subtotal for subcontractors and services (E1) must be less than or equal to $800, and the total budget amount (E2) must also be less than or equal to $97,000.
=AND(E1<=800,E2<=97000)
The cell that contains an employee age (B4) is always greater than the number of full years of employment (F1) plus 18 (the minimum age of employment).
=IF(B4>F1+18,TRUE,FALSE)
All the data in the cell range A1:A20 contains unique values.
=COUNTIF($A$1:$A$20,A1)=1
You must enter the formula in the data validation for cell A1, and then fill the cells A2 though A20 so that the data validation for each cell in the range has a similar formula, but the second argument to the COUNTIF will match the current cell.
The cell that contains a product code name (B5) always begins with the standard prefix of ID- and is at least 10 characters long.
=AND(LEFT(B5, 3) ="ID-",LEN(B5) > 9)
  1. To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
 Note   If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values 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, setting the Ignore blank check box allows any values to be entered in the validated cell.
  1. Optionally, display an input message when the cell is clicked.
How to display an input message
  1. Click the Input Message tab.
  2. Make sure the Show input message when cell is selected check box is selected.
  3. Fill in the title and text for the message.
  1. Specify how you want Microsoft Office Excel to respond when invalid data is entered.
How to specify a response to invalid data
  1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
  2. Select one of the following options for the Style box:
    • To display an information message that does not prevent entry of invalid data, select Information.
    • To display a warning message that does not prevent entry of invalid data, select Warning.
    • To prevent entry of invalid data, select Stop.
  3. Fill in the title and text for the message (up to 225 characters).
 Note   If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."
  1. Test the data validation to make sure that it is working correctly.
Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.
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, open the Data Validation dialog box, and then select the Apply these changes to all other cells with the same settings check box on the Settings tab.


'

Insert or delete a drop-down list

Labels: ,

To make data entry easier in Excel, or to limit entries to certain items that you define, you can create a drop-down list of valid entries that is compiled from cells elsewhere in the workbook. When you create a drop-down list for a cell, it displays an arrow in that cell. To enter information in that cell, click the arrow, and then click the entry that you want.




 
To create a drop-down list from a range of cells, use the Data Validation command in the Data Tools group on the Data tab.
  1. To create a list of valid entries for the drop-down list, type the entries in a single column or row without blank cells. For example:

A
1
Sales
2
Finance
3
R&D
4
IT
2.       Note   You may want to sort the data in the order that you want it to appear in the drop-down list.
  1. If you want to use another worksheet, type the list on that worksheet, and then define a name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) for the list.
How to define a name
  1. Select the cell, range of cells, or nonadjacent selections (nonadjacent selection: A selection of two or more cells or ranges that don't touch each other. When plotting nonadjacent selections in a chart, make sure that the combined selections form a rectangular shape.) that you want to name.
  2. Click the Name box at the left end of the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.).
Name box

  1. Type the name for the cells, for example, ValidDepts.
  2. Press ENTER.
 Note   You cannot name a cell while you are changing the contents of the cell.
  1. Select the cell where you want the drop-down list.
  2. On the Data tab, in the Data Tools group, click Data Validation.
Excel Ribbon Image
Issue: The Data Validation command is unavailable.
An Excel table may be linked to a SharePoint site     You cannot add data validation to an Excel table that is linked to a SharePoint site. To add data validation, you must unlink the Excel table or convert the Excel table to a range.
You may be currently entering data     The Data Validation command is not available on the Data tab while you are entering data in a cell. To finish entering, press ENTER or ESC.
The worksheet might be protected or shared     You cannot change data validation settings if your workbook is shared or protected.
  1. In the Data Validation dialog box, click the Settings tab.
  2. In the Allow box, click List.
  3. To specify the location of the list of valid entries, do one of the following:
  • If the list is in the current worksheet, enter a reference to your list in the Source box.
  • If the list is on a different worksheet, enter the name that you defined for your list in the Source box.
In both cases, make sure that the reference or name is preceded with an equal sign (=). For example, enter =ValidDepts.
  1. Make sure that the In-cell dropdown check box is selected.
  2. To specify whether the cell can be left blank, select or clear the Ignore blank check box.
  3. Optionally, display an input message when the cell is clicked.
How to display an input message
  1. Click the Input Message tab.
  2. Make sure that the Show input message when cell is selected check box is selected.
  3. Type the title and text for the message (up to 225 characters).
  1. Specify how you want Microsoft Office Excel to respond when invalid data is entered.
How to specify a response to invalid data
  1. Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
  2. Select one of the following options for the Style box:
    • To display an information message that does not prevent entry of invalid data, click Information.
    • To display a warning message that does not prevent entry of invalid data, click Warning.
    • To prevent entry of invalid data, click Stop.
  3. Type the title and text for the message (up to 225 characters).
 Note   If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."
 Notes 
  • To delete a drop down list, select the cell with the list. Click the Data tab, and then in the Data Group, click Data Validation. In the Data Validation dialog box, click the Settings tab, and then click Clear All.
  • The width of the drop-down list is determined by the width of the cell that has the data validation. You may need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list.
  • The maximum number of entries that you can have in a drop-down list is 32,767.
  • If the validation list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet.

'

Styles in Excel

Labels: , , , ,

This article has also been published on Microsoft's MSDN site:
Using Styles to Dress Up Your Worksheets in Excel 2007

Introduction

This article explains how you can use styles to ease maintenance of your spreadsheet models.
Microsoft has made it very easy to dress up your worksheets with all sorts of fill patterns, borders and other frills. Because formatting of cells is often done in an ad-hoc manner, many spreadsheet pages look messy.
By consistently using cell styles (instead of changing parts of the cell's formatting) you will be forced to think about the structure of your work. Religiously using styles may even force you to reconsider the overall structure of the entire spreadsheet model: The quality of the computational model itself may be positively affected.
I therefore consider Styles as being underused, underestimated and under exposed.

How styles work

A style is just a set of cell formatting settings which has been given a name. All cells to which a style has been applied look the same formatting-wise. When you change a part of a style, all cells to which that style has been applied change their formatting accordingly.
Use of styles takes some getting accustomed to, but may bring you great advantage. Imagine showing your nicely formatted sheet to your boss. Then your boss asks you if you could please change all input cells to having a light-yellow background fill, instead of a dark yellow one. For a large model, this may imply a huge amount of work. Would you have used styles, then it would have been a matter of seconds.
Styles are in fact an addition. Cell formatting is the sum of the applied style and all modifications to individual formatting elements on top of that style. What parts of the formatting options are included in a style is determined during the definition of the style (See screenshot below). Access the style dialog by choosing Format, Style... from Excel 97-2003's menu. In Excel 2007 you may access the style dialog from the Home Tab, Styles group, Cell Styles button.
Dialoogvenster opmaakprofiel

The Styles dialog screen for Excel 97-2003
Excel 2007 has a slightly different screen:
Opmaakprofielen venster voor Excel 2007
Style window for Excel 2007
When you apply a style to a cell followed by another style, the end result will be an addition of the selected parts of both styles. What the end result of such an addition of styles will be, depends on which elements of both styles have been selected as being part of the style (this will be discussed later). Theoretically, this would have enabled us to use cascading styles, but unfortunately Excel does not keep a record of the order of applied styles. Only the last style is remembered. Also, styles can not be derived from other styles whilst maintaining a link to the parent style. Changes to the "original" style are not reflected in the "child" styles.

Creating styles

A convenient method to create a new style is by selecting a cell which has all formatting options in place which you want to incorporate in the new style. Select the cell and choose Format, Style... from the menu (for Excel 2007: Click the Home tab, click the Cell Styles button in the Styles group).
Styles dialog
The Styles dialog screen.
To create a new style, simply type its name in the box "Style name". By default, all formatting elements are checked. Remove the checkmarks for the formatting elements you want to omit from the style you are creating (The dialog shown above has the Number and Alignment elements turned off).
Use the "Modify..." button to adjust the elements to your needs. Excel will show the standard "Format cells " dialog screen:
Format cells dialog screen
The format cells dialog screen, as shown after clicking Modify... on the Style dialog.
Note, that the elements in the Style dialog are identical to the tabs on the Format Cells dialog.
Note: As soon as you change a formatting element on a tab that was not selected on the Style dialog, Excel will automatically check that element for you; it will become part of that style.
In Excel 2003, after modifying all settings you have two options to "save" the modifications to the style:
  • By clicking "OK"; The currently selected (or added) style will be modified/created and the currently selected cells will have that style applied.
  • By clicking "Add" and subsequently clicking Close: The style will be updated/added, but not applied to the current selection.
The cells that already have this style applied will of course be updated regardless whether method 1 or 2 is used.In short: If you want to modify a style and apply it to the current selection, click OK. Otherwise, click Add and then Close.
Excel 2007 defaults to the second option: the style dialog will update/add the style, but the style will not be applied to the selected cells.

Applying styles

There are two methods to apply a style to a cell.
The first one is by using the menu: "Format", "Style…". Select the style you need from the dropdown list and click OK. Or in Excel 2007: Home tab, Styles group, Expand the Cell Styles gallery and click a style.
In Excel 97-2003 you can save yourself some time (in future) by adding the Style dropdown to a toolbar, like this:

TIP: Adding the style dropdown to a toolbar.

  • Rightclick any toolbar or menu and select "Customize...". The dialog screen shown below will appear (after you click the "Commands" tab):
The Customize dialog screen
The Customize dialog screen.
  • Below "Categories", click the item called "Format". Now drag the Style dropdown to one of your toolbars (see below)
Drag the Style control to a toolbar and let go
Dragging the Style control to a toolbar and let go.
Applying a style to some cells now takes nothing more than picking the right one from your new style dropdown. If the format of any of the selected cells deviates from the selected style then Excel (Excel 97 - 2003) will ask you whether or not to update the style to match the formatting of the selected cells (Excel 2007 does not ask you this question!):
Confirm to Excel how it must apply the style
Confirm to Excel how it must apply the style
This dialog needs some explaining to understand what happens when you click a button.

  • When you click Yes:
    Excel will check all selected cells and add only those elements to the style which coincide for all cells. So if you for example changed a font characteristic for one cell, then Excel will remove the Font settings from the style. Of course you can go back to the style using the menu (format, styles) and modify the style to include that setting again. Note however, that the one cell you changed a font thing to will remain being different from the others.
  • If you click No:
    The selected style will be applied to all cells. Any formatting in those cells will be replaced with the selected formatting elements if the style you chose. Any deviations from a style will be removed. This is the choice Excel 2007 makes for you if you select a style from the styles gallery.



Deviate from a style

If you have applied a style to a set of cells and you change a formatting element of one of those cells, then modifications to that particular element of the style will no longer be applied to the modified cell.
So after changing a font attribute (like Bold) of a cell, changing the font attributes of the style will update all cells, except the one you just modified:
Series of cells with one style, 1 cell deviates from that style.
Series of cells with one style, 1 cell deviates from that style.
You can restore the style to a deviating cell by selecting the cell and choosing the style from the style dropdown and clicking "No" on the subsequent question Excel 97 - 2003 will ask you (See below) . Note that Excel 2007 never asks you this question and performs the same action as when you click "No" . See the previous page to find out what clicking Yes or no causes Excel to do.
Confirm to Excel how it must apply the style
Confirm to Excel how it must apply the style

Tips for using styles

Managing styles

If you like to keep an overview of what styles are available in your file I'd advise you to add a special worksheet to your workbook. Put the names of the styles in column A and an example output in column B:
Table with styles in a worksheet
Table with styles in a worksheet
If you need to adjust a style, select the cell in column B and adjust the style settings from there.
Creating a new style based on an existing one is easy now: Just copy the applicable row and insert it anywhere in the table. Select the cell in column B of the newly inserted row and choose "Format", "Style...". Enter the name of the new style and click Add. Then click Modify to change the style details. Don't forget to update the name in column A too.

Using styles

I advise you to use styles as strictly as you can. Avoid modifying one formatting element of a cell with a style. Instead, consider if it is worth the effort to add a new style. If for instance you have a style for percentage with 2 decimal places and you have a cell which requires three, then add a style for that purpose. You can thank me later.
Adapting this method will likely trigger you to think about what cell styles your document will need. By doing this your Excel models will gradually improve. You'll gain in consistency and loose the ad-hoc (often messy) formatting jungle.

Use functional sets of styles

By looking at your Excel model you will likely be able to categorise your workbook cells into various categories:
  • Input cells
    Cells that are the main input to your model
  • Parameter cells
    Cells that contain constants for your model, such as boundaries.
  • Output cells
    Cells in an area that is meant for output, such as printing or presenting the results of a calculation on screen.
  • Calculation cells
    The cells where the actual calculation work is performed
  • Boundary cells
    By shading otherwise empty cells you can easily make areas with differing functions stand out from other areas.
Consider creating styles for each of these cell functions, each (e.g.) having its own fill color. Don't forget to make decisions on whether or not a style's locked property needs to be on or off. If you use a system like this, it becomes very easy for you to maintain your file. Imagine how easy it now becomes to change a cell from an input to an output cell: you change its style. Done.

VBA examples and tools

The little VBA routines shown below will greatly easy your work with styles. As an important side effect, these also show you how the style object works in VBA.

Find cells with a certain style

This routine find cells with a style containing "demo" in its name:
Sub FindaStyle()
    Dim oSh As Worksheet
    Dim oCell As Range
    For Each oSh In ThisWorkbook.Worksheets
        For Each oCell In oSh.UsedRange.Cells
            If oCell.Style Like "*demo*" Then
                Application.GoTo oCell
                Stop
            End If
        Next
    Next
End Sub
As soon as a cell is encountered with a style that matches that name filter, the code stops (Stop) and you can check out the cell in detail.

Creating a list of styles

This sub adds a table of your styles on a worksheet named "Config - Styles":
Sub ListStyles()
    Dim oSt As Style
    Dim oCell As Range
    Dim lCount As Long
    Dim oStylesh As Worksheet
    Set oStylesh = ThisWorkbook.Worksheets("Config - Styles")
    With oStylesh
        lCount = oStylesh.UsedRange.Rows.Count + 1
        For Each oSt In ThisWorkbook.Styles
            On Error Resume Next
            Set oCell = Nothing
            Set oCell = Intersect(oStylesh.UsedRange, oStylesh.Range("A:A")).Find(oSt.Name, _
                oStylesh.Range("A1"), xlValues, xlWhole, , , False)
            If oCell Is Nothing Then
            lCount = lCount + 1
            .Cells(lCount, 1).Style = oSt.Name
            .Cells(lCount, 1).Value = oSt.NameLocal
            .Cells(lCount, 2).Style = oSt.Name
            End If
        Next
    End With
End Sub

Clear all formatting of cells and re-apply their styles

The code below removes all formatting of all cells and subsequently re-applies their style to them.
Watch out: if you have not adhered to using styles strictly, you will loose all formatting in your file!!!
Sub ReApplyStyles()
'Resets styles of cells to their original style (resets all formatting done on top of ANY style)
    Dim oCell As Range
    Dim oSh As Worksheet
    If MsgBox("Proceed with care:" & vbNewLine & vbNewLine & _
              "This routine will erase all formatting done on top of the existing cell styles." & vbNewLine & _
              "Continue?", vbCritical + vbOKCancel + vbDefaultButton2, GSAPPNAME) = vbOK Then
        For Each oSh In ActiveWindow.SelectedSheets
            For Each oCell In oSh.UsedRange.Cells
                If oCell.MergeArea.Cells.Count = 1 Then
                    oCell.Style = CStr(oCell.Style)
                End If
            Next
        Next
    End If
End Sub

Replace one style with another

The code below uses a list with two columns. The column on the left contains the names of existing styles. The column to its immediate right contains the names of the style you want to replace them with.
The code will run through the selected cells in the left column and check if the style name in the column to its right differs. If so, it will prompt you with the alternative name. Clicking OK will cause the code to update ALL cells to which the old style was applied to the new style. Before running this sub you need to select the cells in the left hand column.
Sub FixStyles()
'-------------------------------------------------------------------------
' Procedure : FixStyles
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 4-10-2007
' Purpose   : Replaces styles with the replacement style as defined by a two column list.
'             column 1 should contain the existing style, col 2 the replacing style
'-------------------------------------------------------------------------
    Dim sOldSt As String
    Dim sNewSt As String
    Dim oSh As Worksheet
    Dim oCell As Range
    Dim oSourceCell As Range
    Set oSourceCell = ActiveCell
    While oSourceCell.Value <> ""
        sOldSt = oSourceCell.Value
        sNewSt = InputBox("Please enter replacement style for:" & sOldSt, "Style changer", oSourceCell.Offset(, 1).Value)
        If sNewSt = "" Then Exit Sub
        If sNewSt <> "" And sNewSt <> sOldSt Then
            For Each oSh In ThisWorkbook.Worksheets
                For Each oCell In oSh.UsedRange
                    If oCell.Style = sOldSt Then
                        Application.GoTo oCell
                        On Error Resume Next
                        oCell.Style = sNewSt
                    End If
                Next
            Next
        End If
        Set oSourceCell = oSourceCell.Offset(1)
    Wend
End Sub

Conclusion

There is a lot to gain by using styles in your Excel work. To name but a few:
  • Consistent formatting of your models
  • Ease of maintenance
  • A strict use of styles leads to a structured way of working
  • Less problems with your file (There is a limit on how many different cell formats Excel can handle).
With this article I have tried to give insight in the use of styles in Excel. If you have comments, suggestions or questions, please don't hesitate to use the comment form below each page!
Download a pdf version of this article


'