- •About the Author
- •About the Technical Editor
- •Credits
- •Is This Book for You?
- •Software Versions
- •Conventions This Book Uses
- •What the Icons Mean
- •How This Book Is Organized
- •How to Use This Book
- •What’s on the Companion CD
- •What Is Excel Good For?
- •What’s New in Excel 2010?
- •Moving around a Worksheet
- •Introducing the Ribbon
- •Using Shortcut Menus
- •Customizing Your Quick Access Toolbar
- •Working with Dialog Boxes
- •Using the Task Pane
- •Creating Your First Excel Worksheet
- •Entering Text and Values into Your Worksheets
- •Entering Dates and Times into Your Worksheets
- •Modifying Cell Contents
- •Applying Number Formatting
- •Controlling the Worksheet View
- •Working with Rows and Columns
- •Understanding Cells and Ranges
- •Copying or Moving Ranges
- •Using Names to Work with Ranges
- •Adding Comments to Cells
- •What Is a Table?
- •Creating a Table
- •Changing the Look of a Table
- •Working with Tables
- •Getting to Know the Formatting Tools
- •Changing Text Alignment
- •Using Colors and Shading
- •Adding Borders and Lines
- •Adding a Background Image to a Worksheet
- •Using Named Styles for Easier Formatting
- •Understanding Document Themes
- •Creating a New Workbook
- •Opening an Existing Workbook
- •Saving a Workbook
- •Using AutoRecover
- •Specifying a Password
- •Organizing Your Files
- •Other Workbook Info Options
- •Closing Workbooks
- •Safeguarding Your Work
- •Excel File Compatibility
- •Exploring Excel Templates
- •Understanding Custom Excel Templates
- •Printing with One Click
- •Changing Your Page View
- •Adjusting Common Page Setup Settings
- •Adding a Header or Footer to Your Reports
- •Copying Page Setup Settings across Sheets
- •Preventing Certain Cells from Being Printed
- •Preventing Objects from Being Printed
- •Creating Custom Views of Your Worksheet
- •Understanding Formula Basics
- •Entering Formulas into Your Worksheets
- •Editing Formulas
- •Using Cell References in Formulas
- •Using Formulas in Tables
- •Correcting Common Formula Errors
- •Using Advanced Naming Techniques
- •Tips for Working with Formulas
- •A Few Words about Text
- •Text Functions
- •Advanced Text Formulas
- •Date-Related Worksheet Functions
- •Time-Related Functions
- •Basic Counting Formulas
- •Advanced Counting Formulas
- •Summing Formulas
- •Conditional Sums Using a Single Criterion
- •Conditional Sums Using Multiple Criteria
- •Introducing Lookup Formulas
- •Functions Relevant to Lookups
- •Basic Lookup Formulas
- •Specialized Lookup Formulas
- •The Time Value of Money
- •Loan Calculations
- •Investment Calculations
- •Depreciation Calculations
- •Understanding Array Formulas
- •Understanding the Dimensions of an Array
- •Naming Array Constants
- •Working with Array Formulas
- •Using Multicell Array Formulas
- •Using Single-Cell Array Formulas
- •Working with Multicell Array Formulas
- •What Is a Chart?
- •Understanding How Excel Handles Charts
- •Creating a Chart
- •Working with Charts
- •Understanding Chart Types
- •Learning More
- •Selecting Chart Elements
- •User Interface Choices for Modifying Chart Elements
- •Modifying the Chart Area
- •Modifying the Plot Area
- •Working with Chart Titles
- •Working with a Legend
- •Working with Gridlines
- •Modifying the Axes
- •Working with Data Series
- •Creating Chart Templates
- •Learning Some Chart-Making Tricks
- •About Conditional Formatting
- •Specifying Conditional Formatting
- •Conditional Formats That Use Graphics
- •Creating Formula-Based Rules
- •Working with Conditional Formats
- •Sparkline Types
- •Creating Sparklines
- •Customizing Sparklines
- •Specifying a Date Axis
- •Auto-Updating Sparklines
- •Displaying a Sparkline for a Dynamic Range
- •Using Shapes
- •Using SmartArt
- •Using WordArt
- •Working with Other Graphic Types
- •Using the Equation Editor
- •Customizing the Ribbon
- •About Number Formatting
- •Creating a Custom Number Format
- •Custom Number Format Examples
- •About Data Validation
- •Specifying Validation Criteria
- •Types of Validation Criteria You Can Apply
- •Creating a Drop-Down List
- •Using Formulas for Data Validation Rules
- •Understanding Cell References
- •Data Validation Formula Examples
- •Introducing Worksheet Outlines
- •Creating an Outline
- •Working with Outlines
- •Linking Workbooks
- •Creating External Reference Formulas
- •Working with External Reference Formulas
- •Consolidating Worksheets
- •Understanding the Different Web Formats
- •Opening an HTML File
- •Working with Hyperlinks
- •Using Web Queries
- •Other Internet-Related Features
- •Copying and Pasting
- •Copying from Excel to Word
- •Embedding Objects in a Worksheet
- •Using Excel on a Network
- •Understanding File Reservations
- •Sharing Workbooks
- •Tracking Workbook Changes
- •Types of Protection
- •Protecting a Worksheet
- •Protecting a Workbook
- •VB Project Protection
- •Related Topics
- •Using Excel Auditing Tools
- •Searching and Replacing
- •Spell Checking Your Worksheets
- •Using AutoCorrect
- •Understanding External Database Files
- •Importing Access Tables
- •Retrieving Data with Query: An Example
- •Working with Data Returned by Query
- •Using Query without the Wizard
- •Learning More about Query
- •About Pivot Tables
- •Creating a Pivot Table
- •More Pivot Table Examples
- •Learning More
- •Working with Non-Numeric Data
- •Grouping Pivot Table Items
- •Creating a Frequency Distribution
- •Filtering Pivot Tables with Slicers
- •Referencing Cells within a Pivot Table
- •Creating Pivot Charts
- •Another Pivot Table Example
- •Producing a Report with a Pivot Table
- •A What-If Example
- •Types of What-If Analyses
- •Manual What-If Analysis
- •Creating Data Tables
- •Using Scenario Manager
- •What-If Analysis, in Reverse
- •Single-Cell Goal Seeking
- •Introducing Solver
- •Solver Examples
- •Installing the Analysis ToolPak Add-in
- •Using the Analysis Tools
- •Introducing the Analysis ToolPak Tools
- •Introducing VBA Macros
- •Displaying the Developer Tab
- •About Macro Security
- •Saving Workbooks That Contain Macros
- •Two Types of VBA Macros
- •Creating VBA Macros
- •Learning More
- •Overview of VBA Functions
- •An Introductory Example
- •About Function Procedures
- •Executing Function Procedures
- •Function Procedure Arguments
- •Debugging Custom Functions
- •Inserting Custom Functions
- •Learning More
- •Why Create UserForms?
- •UserForm Alternatives
- •Creating UserForms: An Overview
- •A UserForm Example
- •Another UserForm Example
- •More on Creating UserForms
- •Learning More
- •Why Use Controls on a Worksheet?
- •Using Controls
- •Reviewing the Available ActiveX Controls
- •Understanding Events
- •Entering Event-Handler VBA Code
- •Using Workbook-Level Events
- •Working with Worksheet Events
- •Using Non-Object Events
- •Working with Ranges
- •Working with Workbooks
- •Working with Charts
- •VBA Speed Tips
- •What Is an Add-In?
- •Working with Add-Ins
- •Why Create Add-Ins?
- •Creating Add-Ins
- •An Add-In Example
- •System Requirements
- •Using the CD
- •What’s on the CD
- •Troubleshooting
- •The Excel Help System
- •Microsoft Technical Support
- •Internet Newsgroups
- •Internet Web sites
- •End-User License Agreement
Chapter 25: Using Data Validation
Creating a Drop-Down List
Perhaps one of the most common uses of data validation is to create a drop-down list in a cell. Figure 25.4 shows an example that uses the month names in A1:A12 as the list source.
FIGURE 25.4
This drop-down list (with an Input Message) was created using data validation.
To create a drop-down list in a cell
1.Enter the list items into a single-row or single-column range. These items will appear in the drop-down list.
2.Select the cell that will contain the drop-down list and then access the Data Validation dialog box (choose Data Data Tools Data Validation).
3.From the Settings tab, select the List option (from the Allow drop-down list) and specify the range that contains the list, using the Source control.
4.Make sure that the In-Cell Dropdown check box is selected.
5.Set any other Data Validation options as desired.
6.Click OK. The cell displays an input message (if specified) and a drop-down arrow when it’s activated. Click the arrow and choose an item from the list that appears.
Tip
If you have a short list, you can enter the items directly into the Source control of the Settings tab of the Data Validation dialog box. (This control appears when you choose the List option in the Allow drop-down list.) Just separate each item with list separators specified in your regional settings (a comma if you use the U.S. regional settings). n
New
In previous versions of Excel the range that contains the list must be on the same worksheet as the cell that contains the data validation. That restriction was removed in Excel 2010. The list range can be on any worksheet in the workbook. n
573
Part IV: Using Advanced Excel Features
Using Formulas for Data Validation Rules
For simple data validation, the data validation feature is quite straightforward and easy to use. The real power of this feature, though, becomes apparent when you use data validation formulas.
Note
The formula that you specify must be a logical formula that returns either TRUE or FALSE. If the formula evaluates to TRUE, the data is considered valid and remains in the cell. If the formula evaluates to FALSE, a message box appears that displays the message that you specify on the Error Alert tab of the Data Validation dialog box. Specify a formula in the Data Validation dialog box by selecting the Custom option from the Allow dropdown list of the Settings tab. Enter the formula directly into the Formula control, or enter a reference to a cell that contains a formula. The Formula control appears on the Setting tab of the Data Validation dialog box when the Custom option is selected. n
I have several examples of formulas used for data validation in the upcoming section “Data Validation Examples.”
Understanding Cell References
If the formula that you enter into the Data Validation dialog box contains a cell reference, that reference is considered a relative reference, based on the upper-left cell in the selected range.
The following example clarifies this concept. Suppose that you want to allow only an odd number to be entered into the range B2:B10. None of the Excel data validation rules can limit entry to odd numbers, so a formula is required.
Follow these steps:
1.Select the range (B2:B10 for this example) and ensure that cell B2 is the active cell.
2.Choose Data Data Tools Data Validation. The Data Validation dialog box appears.
3.Click the Settings tab and select Custom from the Allow drop-down list.
4.Enter the following formula in the Formula field, as shown in Figure 25.5:
=ISODD(B2)
This formula uses the ISODD function, which returns TRUE if its numeric argument is an odd number. Notice that the formula refers to the active cell, which is cell B2.
5.On the Error Alert tab, choose Stop for the Style and then type An odd number is required here as the Error Message.
6.Click OK to close the Data Validation dialog box.
Notice that the formula entered contains a reference to the upper-left cell in the selected range. This data validation formula was applied to a range of cells, so you might expect that each cell
574
Chapter 25: Using Data Validation
would contain the same data validation formula. Because you entered a relative cell reference as the argument for the ISODD function, Excel adjusts the formula for the other cells in the B2:B10 range. To demonstrate that the reference is relative, select cell B5 and examine its formula displayed in the Data Validation dialog box . You’ll see that the formula for this cell is
=ISODD(B5)
FIGURE 25.5
Entering a data validation formula.
Generally, when entering a data validation formula for a range of cells, you use a reference to the active cell, which is normally the upper-left cell in the selected range. An exception is when you need to refer to a specific cell. For example, suppose that you select range A1:B10, and you want your data validation to allow only values that are greater than C1. You would use this formula:
=A1>$C$1
In this case, the reference to cell C1 is an absolute reference; it will not be adjusted for the cells in the selected range — which is just what you want. The data validation formula for cell A2 looks like this:
=A2>$C$1
The relative cell reference is adjusted, but the absolute cell reference is not.
Data Validation Formula Examples
The following sections contain a few data validation examples that use a formula entered directly into the Formula control on the Settings tab of the Data Validation dialog box. These examples help you understand how to create your own Data Validation formulas.
575
Part IV: Using Advanced Excel Features
On the CD
All the examples in this section are available on the companion CD-ROM. The file is named data validation examples.xlsx.
Accepting text only
Excel has a data validation option to limit the length of text entered into a cell, but it doesn’t have an option to force text (rather than a number) into a cell. To force a cell or range to accept only text (no values), use the following data validation formula:
=ISTEXT(A1)
This formula assumes that the active cell in the selected range is cell A1.
Accepting a larger value than the previous cell
The following data validation formula enables the user to enter a value only if it’s greater than the value in the cell directly above it:
=A2>A1
This formula assumes that A2 is the active cell in the selected range. Note that you can’t use this formula for a cell in row 1.
Accepting nonduplicate entries only
The following data validation formula does not permit the user to make a duplicate entry in the range A1:C20:
=COUNTIF($A$1:$C$20,A1)=1
This is a logical formula that returns TRUE if the value in the cell occurs only one time in the A1:C20 range. Otherwise, it returns FALSE, and the Duplicate Entry dialog box is displayed.
This formula assumes that A1 is the active cell in the selected range. Note that the first argument for COUNTIF is an absolute reference. The second argument is a relative reference, and it adjusts for each cell in the validation range. Figure 25.6 shows this validation criterion in effect, using a custom error alert message. The user is attempting to enter 16 into cell B5.
576
Chapter 25: Using Data Validation
FIGURE 25.6
Using data validation to prevent duplicate entries in a range.
Accepting text that begins with a specific character
The following data validation formula demonstrates how to check for a specific character. In this case, the formula ensures that the user’s entry is a text string that begins with the letter A (uppercase or lowercase).
=LEFT(A1)=”a”
This is a logical formula that returns TRUE if the first character in the cell is the letter A. Otherwise, it returns FALSE. This formula assumes that the active cell in the selected range is cell A1.
The following formula is a variation of this validation formula. It uses wildcard characters in the second argument of the COUNTIF function. In this case, the formula ensures that the entry begins with the letter A and contains exactly five characters:
=COUNTIF(A1,”A????”)=1
Accepting dates by the day of the week
The following data validation formula ensures that the cell entry is a date, and that the date is a Monday:
=WEEKDAY(A1)=2
This formula assumes that the active cell in the selected range is cell A1. It uses the WEEKDAY function, which returns 1 for Sunday, 2 for Monday, and so on.
577
Part IV: Using Advanced Excel Features
Accepting only values that don’t exceed a total
Figure 25.7 shows a simple budget worksheet, with the budget item amounts in the range B1:B6. The planned budget is in cell E5, and the user is attempting to enter a value in cell B4 that would cause the total (cell E6) to exceed the budget. The following data validation formula ensures that the sum of the budget items does not exceed the budget:
=SUM($B$1:$B$6)<=$E$5
FIGURE 25.7
Using data validation to ensure that the sum of a range does not exceed a certain value.
Creating a dependent list
As I describe previously, you can use data validation to create a drop-down list in a cell (see “Creating a Drop-Down List”). This section explains how to use a drop-down list to control the entries that appear in a second drop-down list. In other words, the second drop-down list is dependent upon the value selected in the first drop-down list.
Figure 25.8 shows a simple example of a dependent list created by using data validation. Cell E2 contains data validation that displays a three-item list from the range A1:C1 (Vegetables, Fruits, and Meats). When the user chooses an item from the list, the second list (in cell F2) displays the appropriate items.
This worksheet uses three named ranges:
•Vegetables: A2:A15
•Fruits: B2:B9
•Meats: C2:C5
Cell F2 contains data validation that uses this formula:
=INDIRECT($E$2)
578
Chapter 25: Using Data Validation
Therefore, the drop-down list displayed in F2 depends on the value displayed in cell E2.
FIGURE 25.8
The items displayed in the list in cell F2 depend on the list item selected in cell E2.
579