Excel Formulas

Zooming In and Out

To quickly return to 100% (normal) magnification in the worksheet after selecting any another percentage, all you have to do is click the bar in the center of the Zoom slider on the Status bar or click the 100% button on the View tab of the Ribbon.

Splitting the Worksheet into Windows

To split a worksheet into two (upper and lower) horizontal panes, you simply position the cell pointer at the place in the worksheet where you want to split the worksheet and then click the Split button on the Ribbon’s View tab

Fixed Headings with Freeze Panes

Getting your comments in print

When printing a worksheet, you can print comments along with worksheet data by selecting either At End of Sheet or As Displayed on Sheet on the Comments drop-down list on the Sheet tab of the Page Setup dialog box. Open this dialog box by clicking the Dialog Box launcher in the lower-right corner of the Page Setup group on the Ribbon’s Page Layout tab (Alt+PSP).

Name that formula!

Cell names are not only a great way to identify and find cells and cell ranges in your spreadsheet, but they’re also a great way to make out the purpose of your formulas. For example, suppose that you have a simple formula in cell K3 that calculates the total due to you by multiplying the hours you work for a client (in cell I3) by the client’s hourly rate (in cell J3). Normally, you would enter this formula in cell K3 as =I3*J3 However, if you assign the name Hours to cell I3 and the name Rate to cell J3, in cell K3 you could enter the formula =Hours*Rate I don’t think there’s anyone who would dispute that the formula =Hours*Rate is much easier to understand than =I3*J3. To enter a formula using cell names rather than cell references, follow these steps (see Chapter 2 to brush up on how to create formulas):

1. Assign range names to the individual cells as I describe earlier in this section. For this example, give the name Hours to cell I3 and the name Rate to cell J3.

2. Place the cell cursor in the cell where the formula is to appear. For this example, put the cell cursor in cell K3.

3. Type = (equal sign) to start the formula.

4. Select the first cell referenced in the formula by selecting its cell (either by clicking the cell or moving the cell cursor into it). For this example, you select the Hours cell by selecting cell I3.

5. Type the arithmetic operator to use in the formula. For this example, you would type * (asterisk) for multiplication. (Refer to Chapter 2 for a list of the other arithmetic operators.)

6. Select the second cell referenced in the formula by selecting its cell (either by clicking the cell or moving the cell cursor into it). For this example, you select the Rate cell by selecting cell J3.

7. Click the Enter button or press Enter to complete the formula. In this example, Excel enters the formula =Hours*Rate in cell K3.

Naming constants

Certain formulas use constant values, such as an 8.25% tax rate or a 10% discount rate. If you don’t want to have to enter these constants into a cell of the worksheet in order to use the formulas, you create range names that hold their values and then use their range names in the formulas you create. For example, to create a constant called tax_rate (of 8.25%), follow these steps:

1. Click the Define Name button on the Ribbon’s Formulas tab or press Alt+MMD to open the New Name dialog box.

2. In the New Name dialog box, type the range name (tax_rate in this example) into the Name text box. Be sure to adhere to the cell range naming conventions when entering this new name.

3. (Optional) To have the range name defined for just the active worksheet instead of the entire workbook, click the name of the sheet on the Scope drop-down list. Normally, you’re safer sticking with the default selection of Workbook as the Scope option so that you can use your constant in a formula on any of its sheets. Only change the scope to a particular worksheet when you’re sure that you’ll use it only in formulas on that worksheet.

4. Click in the Refers To text box after the equal to sign (=) and replace (enter) the current cell address with the constant value (8.25% in this example) or a formula that calculates the constant.

5. Click OK to close the New Name dialog box.

After you assign a constant to a range name by using this method, you can apply it to the formulas that you create in the worksheet in one of two ways:

Type the range name to which you assign the constant at the place in the formula where its value is required.

Click the Use in Formula command button on the Formulas tab (or press Alt+MS) and then click the constant’s range name on the drop-down menu that appears.

When you copy a formula that uses a range name containing a constant, its values remain unchanged in all copies of the formula that you create with the fill handle. (In other words, range names in formulas act like absolute cell addresses in copied formulas — see Chapter 4 for more on copying formulas.) Also, when you update the constant by changing its value in the Edit Name dialog box — opened by clicking the range name in the Name Manager dialog box (Alt+MN) and then clicking its Edit button — all the formulas that use that constant (by referring to the range name) are automatically updated (recalculated) to reflect this change.

Create From Selection – to automatically name ranges

Research

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s