In: Categories » Computers and technology » Microsoft office » Data Validation in Excel 2007
Your spreadsheet results are only as good as the data you give it and the formulas you create. Feed a spreadsheet the wrong data, and it will obviously calculate the wrong result. More troublesome is when you feed a spreadsheet the right data but your formula is incorrect, which produces a misleading and incorrect result. Even if Excel appears to be calculating your formulas correctly, recheck your calculations just to make sure. Some common errors that can mess up your formulas include
Missing data: The formula isn’t using all the data necessary to calculate the proper result.
Incorrect data: The formula is getting data from the wrong cell.
Incorrect calculation: Your formula is incorrectly calculating a result.
If a formula is calculating data incorrectly, you probably didn’t type the formula correctly. For example, you may want a formula to add two numbers, but you accidentally typed in the formula to multiply two numbers instead. To check whether a formula is calculating data incorrectly, give it data that you already know what the result should be. For example, if you typed the numbers 4 and 7 into a formula that should add two numbers, but it returns 28 instead, you know it’s not calculating correctly.
If your formula is correct but it’s still not calculating the right result, chances are good it’s not getting the data it needs from the correct cells. To help you trace whether a formula is receiving all the data it needs, Excel offers auditing features that visually show you which cells supply data to which formulas. By using Excel’s auditing features, you can Make sure your formulas are using data from the correct cells. Find out instantly whether a formula could go haywire if you change a cell reference.
Finding where a formula gets its data
If a formula is retrieving data from the wrong cells, it’s never going to calculate the right result. By tracing a formula, you can see all the cells that a formula uses to retrieve data. Any cell that supplies data to a formula is a precedent. To trace a formula, follow these steps:
1. Click a cell that contains the formula you want to check.
2. Click the Formulas tab.
3. Click the Trace Precedents icon in the Formula Auditing group. Excel draws arrows that show you all the cells that feed data into the formula you chose in Step 1.
4. Click the Remove Arrows button to make the auditing arrows go away.
Finding which formula(s) a cell can change
Sometimes you may be curious how a particular cell might affect a formula stored in your worksheet. Although you could just type a new value in that cell and look for any changes, it’s easier (and more accurate) to identify all formulas that are dependent on a particular cell. Any formula that receives data is a dependent. To find one or more formulas that a single cell might affect, follow these steps:
1. Click any cell that contains data (not a formula).
2. Click the Formulas tab.
3. Click Trace Dependents. Excel draws an arrow that points to a cell that contains a formula. This tells you that if you change the data in the cell you chose in Step 1, it will change the calculated result in the cell containing a formula.
4. Click the Remove Arrows icon in the Formula Auditing group to make the arrows go away.
Data Validation
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. 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.
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.
6. Click in the Title text box and type a title.
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.
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.
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.
4. Click Clear All and then click OK. Excel clears all your data validation rules for your chosen cell.
legal notice
Our website is not responsible for the information contained by this article. Web-articles is a free articles resource.
Suggestion: If you need fresh, daily updated content for your website, feel free to use our service. Click here for more information.
Useful tools and features
If you like this article (tutorial), please link to it from your web page using the information above.
related articles
The whole purpose of Microsoft Word is to let you type text and make it look pretty so you can print or send it for other people to read. So the first step in using Microsoft Word is learning how to enter text in a Word file, called a document. In every document, Word displays a blinking cursor that points to where your text will appear if you type anything. To move the cursor, you can use the keyboard or the mouse. Moving the Cursor with the Mouse ...
2. Creating text in Word Office 2007
Text can consist of a single sentence, multiple sentences, or several paragraphs. Text always appears indented underneath a heading (or subheading). To create text, follow these steps: 1. Move the cursor to the end of a heading or subheading. This is the heading (or subheading) that your text will be attached to if you move the heading (or subheading). 2. Press Enter. Word creates a blank heading. 3. Click the Demote to Body Text button....
3. Proofreading Your Document Office 2007
Besides checking for spelling or grammatical mistakes, Word can also proofread your document to highlight other possible problems, such as misplaced commas or correctly spelled words that may be used incorrectly. To make Word proofread your document, follow these steps: 1. Click the Proofing icon at the bottom of the document window. Word highlights a possible error and displays a pop-up menu offering options. 2. Choose one of the following: • ...
4. Changing Colors and Justifying Text Alignment in Office 2007
Color can emphasize text. There are two ways to use color: Change the color of the text (Font color). Highlight the text with a different color (Text Highlight color). Changing the color of text When you change the color of text, you’re physically displaying a different color for each letter. Normally, Word displays text in black, but you can change the color to anything you want, such as bright red or dark green. If you choose a light color for your text, it may be hard to re...
5. Creating a new document from a template using Word Office 2007
The easiest time to use a template is before you’ve typed any text. To create a new document from a template, follow these steps: 1. Click the Office Button and then choose New. A New Document window appears. 2. Click one of the following in the left pane of the New Document window: • Installed Templates: Displays templates installed on your computer • Any category under the Microsoft Office Online heading, such as ...
6. Microsoft office 2007 Creating a table by highlighting rows and columns
Organizing Text in Tables Tables organize text into rows and columns, which can make it easy to type, edit, and format text while spacing it correctly in your document. Tables organize text in cells, where a cell is the intersection of a row and a column. Word provides four ways to create a table: Click the Insert tab, click the Table icon, and then highlight the number of rows and columns for your table (up to a maximum of eight rows and ten columns). Use the Inse...
7. Resizing columns and rows. Table styles in Microsoft office 2007
Picking a table style By coloring rows or columns and adding borders, you can customize the appearance of your tables. However, for a faster method, you can just use a predesigned table style instead, which can automatically format your text, color rows, and add borders to your tables. To choose a table style, follow these steps: 1. Move the cursor inside the table you want to modify. 2. Click the Design contextual tools tab. 3. (...