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, as shown in Figure below.

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

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

1. Click a cell that contains data used by a formula.
2. Click the Data tab.
3. Click the Data Validation icon in the Data Tools group.
   The Data Validation dialog box appears, as shown in Figure below.
   
   
   
   Define the type and range of acceptable data allowed in a cell.
   
4. Click the Allow list box and choose one of the following:
Any Value: The default value accepts anything the user types
Whole Number: Accepts only whole numbers, such as 47 and 903
Decimal: Accepts whole and decimal numbers, such as 48.01 or 1.00
List: Allows you to define a list of valid data
Date: Accepts only dates
Time: Accepts only times
Text length: Defines a minimum and maximum length for text
Custom: Allows you to define a formula to specify valid data

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

5. Click the Input Message tab in the Data Validation dialog box, as shown in Figure shown below.
6. Click in the Title text box and type a title.
   
   
   
   The Input Message tab lets you display a message explaining the type of valid data a
   cell can hold.
   
7. Click in the Input Message text box and type a message you want to display when someone selects this particular cell.
8. Click the Error Alert tab in the Data Validation dialog box, as shown in Figure below.
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.
    
    
    
    Define an error message to show if the user types invalid data into the cell.
    
11. Click in the Error Message text box and type the message to appear if the user types invalid data into the cell.
12. Click OK.

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

1. Click in the cell that contains data validation.
2. Click the Data tab.
3. Click the Data Validation icon in the Data Tools group. The Data Validation dialog box appears.
4. Click Clear All and then click OK.
5. Excel clears all your data validation rules for your chosen cell.

[Back] [Contents]

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, as shown in Figure shown below.
4. Click the Remove Arrows button to make the auditing arrows go away.



Excel draws arrows that trace precedent cells that feed data into a formula.

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:
Click any cell that contains data (not a formula).

1. Click the Formulas tab.
2. Click Trace Dependents.
   Excel draws an arrow that points to a cell that contains a formula, as shown in Figure shown below. 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.
3. Click the Remove Arrows icon in the Formula Auditing group to make the arrows go away.
   
   
   
   Excel can identify which formulas a particular cell can change.

[Back] [Contents] [Next]

Auditing Your Formulas

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 is not 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 did not 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

1. Make sure your formulas are using data from the correct cells.
2. Find out instantly whether a formula could go haywire if you change a cell reference.

[Back] [Contents] [Next]

Viewing a scenario summary

If you have multiple scenarios, it can be hard to switch back and forth between different scenarios and still understand which numbers are changing. To help you view the numbers that change in all your scenarios, you can create a scenario summary.

A scenario summary displays your original data along with the data stored in each scenario in a table. By viewing a scenario summary, you can see how the values of your spreadsheet can change depending on the scenario, as shown in Figure below.



A scenario summary compares your original data with all the data from your scenarios in an easy to read chart.

To create a scenario summary on a separate sheet in your workbook, follow these steps:

1. Click the Data tab.
2. Click the What-If Analysis icon in the Data Tools group.
   A pull-down menu appears.
3. Choose Scenario Manager.
   The Scenario Manager dialog box appears.
4. Click Summary.
   The Scenario Summary dialog box appears, as shown in Figure shown below.
5. Select the Scenario Summary radio button.
6. Click in the Result Cells text box and then click in a cell that contains a formula that your scenario affects.
   
   
   
   Define the type of summary to create.
7. Click OK.

[Back] [Contents] [Next]

Viewing a scenario

After you create one or more scenarios, you can view them and see how they affect your data.

To view a scenario, follow these steps:

1. Click the Data tab.
2. Click the What-If Analysis icon in the Data Tools group.
   A pull-down menu appears.
3. Choose Scenario Manager.
   The Scenario Manager dialog box appears.
4. Click the name of the scenario you want to view.
5. Click Show.
   Excel shows the values in the cells defined by your chosen scenario.
6. Click Close.

Editing a scenario

After you create a scenario, you can always change it later by defining new data. To edit a scenario, follow these steps:

1. Click the Data tab.
2. Click the What-If Analysis icon in the Data Tools group.
   A pull-down menu appears.
3. Choose Scenario Manager.
   The Scenario Manager dialog box appears.
4. Click the name of the scenario you want to edit and click Edit.
   The Edit Scenario dialog box appears, as shown in Figure below.
   
   
   
   Define new cells to modify and new data to appear in those cells.
5. (Optional) Edit the name of the scenario.
6. Click in the Changing Cells text box.
   Excel displays dotted lines around all the cells that the scenario will change.
7. Press Backspace to delete cells, or hold down the Ctrl key and click additional cells to include in your scenario.
8. Click OK.
   The Scenario Values dialog box appears.
9. Type new values for your cells and click OK when you are done.
   The Scenario Manager dialog box appears again.
10. Click Show to view your scenario, or click Close to make the Scenario
    Manager dialog box disappear.

[Back] [Contents] [Next]

Creating Multiple Scenarios

Spreadsheets show you what happened in the past. However, you can also use a spreadsheet to help predict the future by typing in data that represents your best guess of what might happen.

When you use a spreadsheet as a prediction tool, you may create a best-case scenario (where customers flood you with orders) and a worst-case scenario (where hardly anybody buys anything). You could type in different data to represent multiple possibilities, but then you'd wipe out your old data. For a quick way to plug different data in the same spreadsheet, Excel offers scenarios.

A scenario lets you define different data for multiple cells. That way, you can choose a scenario to plug in one set of data, and then switch back to your original data without retyping everything.

Creating a scenario

Before you can create a scenario, you must first create a spreadsheet with data and formulas. Then you can create a scenario to define the data to plug into one or more cells.

To create a scenario, follow these steps:

1. Click the Data tab.
2. Click the What-If Analysis icon in the Data Tools group.
   A pull-down menu appears.
3. Click Scenario Manager.
   The Scenario Manager dialog box appears.
4. Click Add.
   The Add Scenario dialog box appears, as shown in Figure shown below.
   
   
   
   Define a scenario name, the cells you want to change, and any comments you want to include.
5. Click in the Scenario Name text box and type a descriptive name for your scenario, such as Worst-case or Best-case.
6. Click in the Changing Cells text box.
7. Click a cell in your spreadsheet that you want to display different data. If you want to choose multiple cells, hold down the Ctrl key and click multiple cells.
8. Click in the Comment text box and type any additional comments you want to add to your scenario, such as any assumptions your scenario made.
9. Click OK.
   The Scenario Values dialog box appears, as shown in Figure below.
   
   
   
   Type in new values for your selected cells.
10. Type a new value for each cell.
11. Click OK.
    The Scenario Manager dialog box appears, as shown in Figure shown below.



The Scenario Manager dialog box lets you view, edit, or delete your different scenarios.

12. Click Show.
    Excel replaces any existing data with the data you typed in Step 10.
13. Click Close.
    The data from your scenario remains in the spreadsheet.

[Back] [Contents] [Next]

Goal Seeking

Usually after you can create a formula, you can type in new data to see how the formula calculates a new result. However, Excel also offers a feature known as Goal Seeking. With Goal Seeking, you specify the value you want a formula to calculate, and then Excel changes the data in the formula’s cell references to tell you what values you need to achieve that goal.

For example, suppose you have a formula that calculates how much money you make every month by selling a product such as cars. Change the number of cars you sell, and Excel calculates your monthly commission. But if you use Goal Seeking, you can specify you want to earn $5,000 for your monthly commission, and Excel will work backward to tell you how many cars you need to sell. As its name implies, Goal Seeking lets you specify a goal and see what number, in a specific cell, needs to change to help you reach your goal.

To use Goal Seeking, follow these steps:

1. Click in the cell that contains a formula.
2. Click the Data tab.
3. Click the What-If Analysis icon in the Data Tools group.
   A pull-down menu appears, as shown in Figure below.
   
   
   
   The What-If Analysis icon displays the Goal Seek command.
4. Click Goal Seek.
   The Goal Seek dialog box appears, as shown in Figure shown below.
   
   
   
   Define your goal in a cell containing a formula.
5. Click in the To Value text box and type a number that you want to appear in the formula stored in the cell that you clicked in Step 1.
6. Click in the By Changing Cell text box and click one cell that contains data used by the formula you chose in Step 1.
   Excel displays your cell reference, such as B5, in the Goal Seek dialog box.
7. Click OK.
   The Goal Seek Status dialog box changes the data in the cell you chose
   in Step 6, as shown in Figure shown below.
   
   
   
   The Goal Seek Status dialog box changes the data to reach your desired goal.
8. Click OK (to keep the changes) or click Cancel (to display the original values your spreadsheet had before you chose the Goal Seek command).

[Back] [Contents] [Next]

Editing a Formula

After you create a formula, you can always edit it later. You can edit a formula in two places:

1. In the Formula bar
2. In the cell itself

To edit a formula in the Formula bar, follow these steps:

1. Select the cell that contains the formula you want to edit.
   Excel displays the formula in the Formula bar.
2. Click in the Formula bar and edit your formula using the Backspace and Delete keys.

To edit a formula in the cell itself, follow these steps:

1. Double-click in the cell that contains the formula you want to edit.
   Excel displays a cursor in the cell you selected.
2. Edit your formula using the Backspace and Delete keys.

Because formulas display their calculations in a cell, it can be hard to tell the difference between cells that contain numbers and cells that contain formulas. To make formulas visible, press Ctrl+' (an accent grave character, which appears on the same key as the ~ symbol).

[Back] [Contents] [Next]

Using recently used functions

Digging through all the different function library menus can be cumbersome, so Excel tries to make your life easier by creating a special Recently Used list that contains (what else?) a list of the functions you’ve used most often. From this menu, you can just see a list of your favorite functions and ignore the other hundred functions that you may never need in a million years.

To use the list of recently used functions, follow these steps:

1. Click the cell where you want to store a function.
2. Click the Formulas tab.
3. Click the Recently Used icon in the Function Library group.
   A pull-down menu appears, as shown in Figure shown below.
4. Choose a function.

The Recently Used menu lists the functions you have used most often.

The more functions you use, the more your list will vary from what you see in Figure shown above.

[Back] [Contents] [Next]

Using the AutoSum command

One of the most useful and commonly used command is the AutoSum command. The AutoSum command uses the SUM function to add two or more cell references without making you type those cell references yourself. The most common use for the AutoSum function is to add a column or row of numbers.

To add a column or row of numbers with the AutoSum function, follow these steps:

1. Create a column or row of numbers that you want to add.
2. Click at the bottom of the column or the right of the row.
3. Click the Formulas tab.
4. Click the AutoSum icon in the Function Library group.
   Excel automatically creates a SUM function in the cell you chose in Step 2 and highlights all the cells where it will retrieve data to add, as shown in Figure below. (If you accidentally click the downward-pointing arrow under the AutoSum icon, a pull-down menu appears. Just choose Sum.)
5. Press Enter.
   Excel automatically sums all the cell references

The AutoSum command automatically creates cell references for the SUM function.

The AutoSum icon also appears on the Home tab in the Editing group.

[Back] [Contents] [Next]

Using Functions

Creating simple formulas is easy, but creating complex formulas is hard. To make complex formulas easier to create, Excel comes with prebuilt formulas called functions. Lists shown some of the many functions available.

Excel literally provides hundreds of functions that you can use by themselves or as part of your own formulas. A function typically uses one or more cell references:

1. Single cell references, such as =ROUND(C4,2), which rounds the number found in cell C4 to two decimal places
2. Contiguous (adjacent) cell ranges, such as =SUM(A4:A9), which adds all the numbers found in cells A4, A5, A6, A7, A8, and A9
3. Noncontiguous cell ranges, such as =SUM(A4,B7,C11), which adds all the numbers found in cells A4, B7, and C11

To use a function, follow these steps:

1. Click in the cell where you want to create a formula using a function.
2. Click the Formulas tab.
3. Click one of the following function icons in the Function Library group:
   Financial: Calculates business-related equations, such as the amount of interest earned over a specified time period
   Logical: Provides logical operators to manipulate True and False (also known as Boolean) values
   Text: Searches and manipulates text
   Date & Time: Provides date and time information
   Lookup & Reference: Provides information about cells, such as their row headings
   Math & Trig: Offers mathematical equations
   More Functions: Provides access to statistical and engineering functions
4. Click a function category, such as Financial or Math & Trig.
   A pull-down menu appears, as shown in Figure below.
   
   
   
   Clicking a function library icon displays a menu of available functions you can use.
5. Click a function.
   The Function Arguments dialog box appears, as shown in Figure below.
   
   
   
   Specify which cell references contain the data your function needs to calculate a result.
6. Click the cell references you want to use.
7. Repeat Step 6 as many times as necessary.
8. Click OK.
   Excel displays the calculation of your function in the cell you selected in Step 1.

[Back] [Contents] [Next]

Copying formulas

In many spreadsheets, you may need to create similar formulas that use different data. For example, you may have a spreadsheet that needs to add the same number of cells in adjacent columns.

You could type nearly identical formulas in multiple cells, but that’s tedious and error-prone. For a faster way, you can copy a formula and paste it in another cell; then Excel automatically changes the cell references, as shown in Figure below.



Rather than type repetitive formulas over and over again, Excel can copy a formula but automatically change the cell references.

In above figuer you can see that cell B9 contains the formula =B3+B4+B5+ B6+B7+B8, which simply adds the numbers stored in the six cells directly above the cell that contains the formula (B9). If you copy this formula to another cell, that new formula will also add the six cells directly above it. Copy and paste this formula to cell C9, and Excel changes the formula to
=C3+C4+C5+C6+C7+C8.

To copy and paste a formula so that each formula changes cell references automatically, follow these steps:

1. Select the cell that contains the formula you want to copy.
2. Press Ctrl+C (or click the Copy icon under the Home tab).
   Excel displays a dotted line around your selected cell.
3. Select the cell (or cells) where you want to paste your formula.
   If you select multiple cells, Excel pastes a copy of your formula in each of those cells.
4. Press Ctrl+V (or click the Paste icon under the Home tab).
   Excel pastes your formula and automatically changes the cell references.
5. Press Esc or double-click away from the cell with the dotted line to make the dotted line go away.

[Back] [Contents] [Next]

Organizing formulas with parentheses

Formulas can be as simple as a single mathematical operator such as =D3*E4. However, you can also use multiple mathematical operators, such as

=A4+A5*C7/F4+D9

There are two problems with using multiple mathematical operators. First, they make a formula harder to read and understand. Second, Excel calculates mathematical operators from left to right, based on precedence, which means a formula may calculate results differently than you intended.

Precedence tells Excel which mathematical operators to calculate first, as listed in Table shown below. For example, Excel calculates multiplication before it calculates addition. If you had a formula like

=A3+A4*B4+B5

Excel first multiplies A4*B4 and then adds this result to A3 and B5.



(Remember): Typing parentheses around cell references and mathematical operators not only organizes your formulas but also tells Excel specifically how you want to calculate a formula. In the example =A3+A4*B4+B5, Excel multiplies A4 and B4 first. If you want Excel to first add A3 and A4, then add B4 and B5, and finally multiply the two results together, you have to use parentheses, like this:

=(A3+A4)*(B4+B5)

[Back] [Contents] [Next]

Creating a Formula

Formulas consist of three crucial bits of information:

1. An equal sign (=)
2. One or more cell references
3. The type of calculation to do on the data (addition, subtraction, and so on)

A cell reference is simply the unique row and column heading that identifies asingle cell, such as A4 or D9. The four common calculations that a formula can use are addition (+), subtraction (–), multiplication (*), and division (/).

Lists shown below other mathematical operators you can use in a formula.

A simple formula uses a single mathematical operator and two cell references such as:

=A4+C7

This formula consists of three parts:

1. The = sign: This identifies your formula. If you type just A4+C7 into a cell, Excel treats it as ordinary text.
2. Two cell references: In this example, A4 and C7.
3. The addition (+) mathematical operator.

To type a formula in a cell, follow these steps:

1. Click in the cell where you want to store the formula. You can also select a cell by pressing the arrow keys.
   Excel highlights your selected cell.
2. Type the equal sign (=).
   This tells Excel that you are creating a formula.
3. Type your formula that includes one or more cell references that identify cells that contain data, such as A4 or E8.
   For example, if you want to add the numbers stored in cells A4 and E8, you would type =A4+E8.
4. Press Enter.

Typing cell references can get cumbersome because you have to match the row and column headings of a cell correctly. As a faster alternative, you can use the mouse to click any cell that contains data; then Excel types that cell reference into your formula automatically.

To use the mouse to click cell references when creating a formula, follow these steps:

1. Click in the cell where you want to store the formula. (You can also select the cell by pressing the arrow keys.)
   Excel highlights your selected cell.
2. Type the equal sign (=).
   This tells Excel that anything you type after the equal sign is part of your formula.
3. Type any mathematical operators and click any cells that contain data, such as A4 or E8.
   If you want to create the formula =A4+E8, you would do the following:
   a. Type =.
   This tells Excel that you’re creating a formula.
   b. Click cell A4.
   Excel types the A4 cell reference in your formula automatically.
   c. Type +.
   d. Click cell E8.
   Excel types in the E8 cell reference in your formula automatically.
4. Press Enter.

After you finish creating a formula, you can type data into the cell references used in your formula to calculate a new result.

[Contents] [Next]

Getting to Know MS-Excel Formulas

Excel can manipulate your data by using formulas. Formulas can be as simple as adding two or more numbers together or as complicated as determining the calculation of a second-order differential equation.

Formulas use data, stored in other cells, to calculate a new result that appears in another cell. To create even more complicated spreadsheets, you can even make a formula use data from other formulas so that changes in a single cell can ripple throughout an entire spreadsheet.

Creating a Formula
Organizing Formulas with Parenthese
Copying Formulas
Using Functions
Using the AutoSum Command
Using Recently Used Functions
Editing a Formula
Goal Seeking
Creating Multiple Scenarios
Viewing a Scenarios
Viewing Scenarios Summary
Auditing your Formulas
Finding Where Formula Gets Its Data
Data Validation