Ms Office Tune Up

What-if analysis is perhaps the most basic method for understanding worksheet data. With what-if analysis, you first calculate a formula F, based on the input from variables X, Y, and Z. You then say, “What if I change variable X? What if I increase Y? What if I decrease Z? What happens to the result?”

For example, Figure 1 shows a worksheet that cal­culates the monthly payment for a loan or mortgage based on three variables: the interest rate, the term, and the initial principal. Cell C8 shows the result of the PMT() function. Now the questions begin: What if the interest rate is 8 percent? What if the term is 25 years? What if the principal is $125,000? Or $150,000? Answering these questions is a straight­forward matter of changing the appropriate vari­ables and watching the effect on the result.

Figure 1 The most basic form of what-if analysis involves changing worksheet vari­ables and watching the result.

Changing the value of one cell and watching its effect on the values of one or more other cells is straightforward. However, what if the cells you want to watch reside in another worksheet or workbook? You can arrange the windows accordingly, but Excel has an easier method: the Paste Picture Link command. This command takes a copy of a specified range and turns it into a snapshot: a picture of the original range that you can add to any work­sheet. The snapshot is “live,” however, so any changes that occur in the original range will also appear automatically in the snapshot. (The snapshot itself is just a Picture object, so you can’t edit the data it displays.)

Follow these steps to use Paste Picture Link to create a range snapshot:

1. Select the range you want to watch.
2. Choose Home, Copy (or press Ctrl+C).
3. Switch to the worksheet and select the cell in which you want the snapshot to display.
4. Choose Home, pull down the Paste menu, and then choose As Picture, Paste Picture Link. Excel pastes the snapshot and sets up a link to the original range.

Technorati Tags: range snapshot, watch a cell value, excel 2007

Popularity: 2% [?]

If you liked this post, would you please buy me a twelve-ounce lattes for only $2