In: Categories » » Microsoft office » Goal Seeking in Excel Office 2007
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.
4. Click Goal Seek.
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.
7. Click OK.
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).
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.
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
10. Type a new value for each cell.
11. Click OK.
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.
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.
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’re 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.
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. 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.
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.
7. Click OK.
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
Microsoft Office 2007 consists of five core programs: Word, Excel, PowerPoint, Access, and Outlook. Each of these core programs specializes in manipulating different data. Word manipulates words, sentences, and paragraphs; Excel manipulates numbers; PowerPoint manipulates text and pictures to create a slide show; Access manipulates data, such as inventories; and Outlook manipulates personal information, such as e-mail addresses and phone numbers. Although each Office 2007 program specializes in storing and mani...
2. Giving commands to Office 2007
To give a command to Office 2007, you need to follow these basic steps: 1. Select an item (text, picture, table, and so on) that you want to modify. 2. Click a tab that contains the command you want. 3. Click the command you want to use. Command icons work in one of three ways: Clickable icons: Clicking an icon immediately chooses a command to alter your data. The Bold and Italic icons are examples of icons that you click only once to choose them. ...
3. Sharing Data with Other Office 2007 Programs
Cutting, copying, and pasting data may be handy within the same file, but Office 2007 also gives you the ability to cut, copy, and paste data between different programs, such as copying a chart from Excel and pasting it into a PowerPoint presentation. Using the Office Clipboard When you cut or copy any data, Windows stores it in a special part of memory called the Clipboard. This Windows Clipboard can only hold one item at a time, so Office 2007 comes with its own Clipboard called t...
4. Typing Text in Word office 2007
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 ...
5. 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....
6. 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: • ...
7. 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...
8. 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 ...