Ms Office Tune Up

Modifying formula variables suffers from a serious drawback: You see only a single result at one time. If you want to study the effect that a variety of values has on the formula, you need to construct a data table. In the loan payment worksheet, for example, suppose that you want to see the payments with the principal varying between $100,000 and $150,000. You can just enter these values into a row or column, and then create the appropriate for­mulas. Setting up a data table, however, is much easier, as the following procedure shows:

1. Add to the worksheet the values you want to input into the formula. You have two choices for the placement of these values:
   • If you want to enter the values in a row, start the row one cell up and one cell to the right of the formula.
   • If you want to enter the values in a column start the column one cell down and one cell to the left of the cell containing the formula, as shown in Figure 1.
2. Select the range that includes the input values and the formula. (In Figure 1, this is B8:C14.)
3. Choose Data, What-If Analysis, Data Table. Excel displays the Data Table dialog box.
4. How you fill in this dialog box depends on how you set up your data table:
   • If you entered the input values in a row, use the Row Input Cell text box to enter the cell address of the input cell.
   • If the input values are in a column, enter the input cell’s address in the Column Input Cell text box. In the future value example, you enter C5 in the Column Input Cell, as shown in Figure 2. Read the rest of this entry »

Popularity: 6% [?]

Using a range snapshot is a useful and nifty trick, but it’s only practical if you want to watch one or two values. However, it’s not unusual for a large or complex worksheet model to have a large number of cells that require watching, particularly when you first build the model. If you need to keep an eye on a lot of cells, forget the Paste Picture Link command. Instead, use Excel’s more elegant solution: the Watch Window. This window enables you to keep tabs on both the value and the formula in any cell in any worksheet in any open workbook. Here’s how you set up a watch:

Calculating Multiple Solutions to a Formula

1. Activate the workbook that contains the cell or cells you want to watch.
2. Choose Formulas, Watch Window. Excel displays the Watch Window.
3. Click Add Watch. Excel displays the Add Watch dialog box.
4. Either select the cell you want to watch or type in a reference formula for the cell (for example, =A1). Note that you can select a range to add multiple cells to the Watch Window.
5. Click Add. Excel adds the cell or cells to the Watch Window, as shown in Figure 1.

Figure 1 Use the Watch Window to keep an eye on the values and formulas of unseen cells that reside in other worksheets or workbooks.

Technorati Tags: watch window, cell value, excel 2007

Popularity: 7% [?]

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.

Read the rest of this entry »

Popularity: 3% [?]

If you are constructing worksheets that other people will use for data entry, ensuring that data is entered as accurately as possible should be your main concern. No matter how solid the rest of the model is, if the data it relies upon is faulty, the results the model generates will be faulty as well. When constructing a worksheet, here are a few notes to bear in mind to help ensure accurate data entry:

• Format the worksheet text in a large, clear, legible font.
• Provide labels that indicate to the user the data type or units a cell requires. For exam­ple, if a cell requires a value in months, add the label months in the cell to the right of the input cell.
• Provide instructions that tell the user how to input the data. These instructions can be in a text box at the top of the worksheet or in comments that you insert into each input cell.
• Take advantage of Excel’s powerful data validation features to ensure the right types of data get entered.

Another useful technique for ensuring accurate data entry is to add an extra worksheet layer that gives the user an interface for entering the data. This interface takes the form of dialog box controls such as lists, check boxes, and option buttons. You associate certain values with these controls, which then ensure that the user can enter only the values you want. Read the rest of this entry »

Popularity: 4% [?]

If you often work with multiple workbooks at once, you may find yourself moving from one workbook to another, making changes to each one as you go. Unless you remember to save all along, you probably end up with some or all of your open workbooks with unsaved changes. Unfortunately, Excel doesn’t tell you which workbooks have unsaved changes, so you have no choice but to trudge through each open workbook and run the Save command.

You can avoid this drudgery by using the SaveAll procedure shown in Listing 1.

Listing 1 A VBA Procedure That Saves All Open Workbooks

Read the rest of this entry »

Popularity: 3% [?]

It’s often handy to be able to select the name range that contains the current cell (for exam­ple, to change the range formatting). If you know the name of the range, you need only select it from the Name box. However, in a large model or a workbook that you’re not familiar with, it may not be obvious which name to choose. Listing 1 shows a VBA func­tion and procedure that handles this chore for you.

Listing 1 A VBA Function and Procedure That Determines and Selects the Named Range Containing the Active Cell

The heart of Listing 1 is the GetRangeName function, which takes a range as an argument. The purpose of this function is to see if the passed range-r-is part of a named range and if so, to return the name of that range. The function’s main loop runs through each item in the active workbook’s Names collection. For each name, the RefersToRange property returns the associated range, which the function stores in the rtr variable. The Read the rest of this entry »

Popularity: 3% [?]

In the previous section, I showed you a VBA procedure that “reset” a workbook by select­ing cell A1 in all worksheets. However, it isn’t always the case that cell A1 is the appropriate starting point in a worksheet. For example, if other people work on data in a particular worksheet, it is best to have the first data entry cell selected when they open the file. Similarly, you might want to select a cell that contains a problem or an important result. In these situations, rather than selecting cell A1 on all the worksheets, you might prefer to select each of these “home cells.”

An easy way to do this is to first designate each worksheet’s home cell by adding a comment to each of those cells. This should be a uniform comment, such as Home Cell or something similar. You can then use the VBA procedure in Listing 1 to select each of these home cells. Read the rest of this entry »

Popularity: 3% [?]

One of Excel’s unique features is that it “remembers” where you left off in a workbook when you last closed it, and it then restores that position the next time you open the work­book. For example, if the active cell is D5 in Sheet3 when you close a workbook, the next time you open that file, Excel automatically activates Sheet3 and selects cell D5. Not only that, but Excel also remembers the most recently used cell in every worksheet. This also applies to ranges: If you have ranges selected on any worksheet when you close the file, Excel reselects those ranges the next time you open the workbook.

This is useful because in most cases, it enables you to continue working from the spot where you stopped earlier when you closed the file. One situation where this is not great behavior is when you distribute a workbook to other people. When the other users open the file, Excel might take them to a cell or range in a worksheet that’s buried deep in the workbook. It’s more likely that you’ll want the workbook to have no selected ranges and to have the first worksheet activated. A good way to do this is to select cell A1 on each work­sheet and to finish by activating the first worksheet. This is reasonable in a workbook with just a few sheets, but it’s a hassle in large workbooks. To eliminate that hassle, Listing 6.4 presents a VBA procedure that selects cell A1 in all of a workbook’s sheets. Read the rest of this entry »

Popularity: 5% [?]

Early versions of Excel used to give you 16 worksheets by default in each new workbook. Somebody at Microsoft must have realized this was an absurdly large number, so recent versions of Excel populate new workbooks with just three worksheets. That’s a reasonable number because most people probably just use a single worksheet in most of their work­books. However, it’s not uncommon to have workbooks with 10 or 20 sheets, depending on the application. Rather than adding the extra sheets by hand, Listing 1 presents a VBA procedure that prompts you for the number of sheets you want and then creates a new workbook populated with that many sheets.

TIP

You can change the number of worksheets that Excel adds by default to new workbooks. Choose Office, Excel Options to display the Excel Options dialog box. Click Popular and then use the Include This Many Sheets spin box to set the number of worksheets you want in each new workbook. Click OK to put the new setting into effect. Read the rest of this entry »

Popularity: 3% [?]

When you work in Excel, you may find that you always use a particular collection of work­books. In fact, you may spend the first few minutes after launching Excel opening those workbooks. A similar situation occurs when you have separate groups of related workbooks that you use at different times. For example, you might have one collection of budget workbooks, another collection of planning workbooks, and yet another collection for an ongoing project. In these cases, you waste precious time opening the individual files you need.

Excel helps you save most of that time by enabling you to specify workspaces of files. A workspace is a collection of workbooks that Excel stores in a special .xlw file. When you open a workspace file, Excel automatically opens all the workbooks that you’ve associated with it.

Earlier versions of Excel had a Save Workspace command on the File menu. In Excel 2007, you must first add this command to the Quick Access toolbar. Here are the steps to follow:

1. Pull down the Customize Quick Access Toolbar menu and then click More Commands. The Excel Options dialog box displays with the Customize page dis­played.
2. In the Choose Command From list, click All Commands.
3. In the list of commands, click Save Workspace.
4. Click Add. Read the rest of this entry »

Popularity: 5% [?]