Selecting Cells That Contain Formulas

To select cells containing Formulas in order to color, delete, or protect, use the Go To dialog box:
1.  Press <F5>.
OR
From the Edit menu, select Go To.
2. In the Go To dialog box, click Special.
3. Select Formulas, and then click OK.

5:32 AM | 0 comments | Read More

Using a Range Name in a Formula

To use a range Name in a formula:

1. Define the following range Names for ranges B2:B11, C2:C11, and D2:D11 respectively: Jan_2004, Feb_2004, and Mar_2004 (see the screenshot below).
2. Select a cell and type the formula =SUM.
3. Press <Ctrl+A>.
4. Select the first argument box and press <F3>.
5. Select the Name Jan_2004, and then click OK.
6. Paste the Names Febr_2004 and Mar_2004 in the next two argument boxes, and then click OK. The following formula has now been inserted into the cell:

=SUM(Jan_2004, Feb_2004, Mar_2004)

6:35 PM | 0 comments | Read More

Displaying Both Formulas and Values for Cells

To display both formulas and values for cells:

1. From the Window menu, select New Window.
2. From the Window menu, select Arrange.
3. Select the Horizontal option button and click OK.
4. Select one of the two windows and press <Ctrl+‘> (the key to the left of the number 1).

To move between windows, press <Ctrl+Tab> or <Ctrl+F6>.

5:27 AM | 0 comments | Read More

Displaying Formula Syntax

To display the syntax of all formulas in a sheet:

Press <Ctrl+‘> (the ‘ symbol is located to the left of the number 1 on the keyboard).

OR

From the Tools menu, select Options, the View tab, Formulas, and then click OK.

To return to the normal display, press <Ctrl+‘> again (this keyboard shortcut is a toggle).

Regular Display :

Displaying Formulas :

Technorati Tags: Working With Formulas

5:27 AM | 0 comments | Read More

Working With Formulas

This chapter contains fundamentals, shortcuts, tips, and techniques that are essential when working with Formulas & Functions. It includes the following sections:

• Inserting, Editing, & Copying Formulas : This section covers easy techniques on inserting, editing, and copying formulas, understanding the correct use of relative/absolute references, and creating power formulas.
• Selecting, Displaying, Printing & Pasting Formulas: In this section you will find techniques on displaying formula syntax, displaying formulas and values, selecting cells containing formulas, pasting values, adding comments to formulas, and printing formula syntax. Array Formulas, page 12: This section provides an explanation of the concept of an Array and how Excel uses Arrays in formulas, You will also learn how to create an Array formula. Using Range Names in Formulas, page 13: In this section, learn about range name rules, how to define range names, and how to use range Names in formulas.
• Auditing Formulas: In this section, find essential shortcuts and techniques on tracing and moving between precedent and dependent cells, stepping into formulas, and tracing errors in a formula.
• Protecting Formulas: In this section, you will learn how to protect formulas in both protected and unprotected sheets.

3:17 AM | 0 comments | Read More

Copying Formulas from a Range of Cells without Changing the Absolute or Relative References

To copy/paste Formulas from a range of cells without changing the Absolute or Relative references:
1.  Select the range of cells containing the formulas and press <Ctrl+H>.
2.  In the Find what box, type the = sign.
3.  In the Replace with box, type the # symbol (to change the formulas to
text).
4.  Click Replace All, and then click Close.
5.  Copy and paste the cells to a new location.
6.  Repeat steps 1 through 3, reversing the # and = signs (to change the
text to formulas).

3:09 AM | 0 comments | Read More

Copying a Formula from a Cell While Keeping the Absolute Reference or Relative Reference

Avoid the nightmare of pressing <F4> multiple times when coping and pasting formulas.
To copy/paste a Formula without changing the Absolute or Relative references:

Option 1: Select a cell under the cell containing a formula and press <Ctrl+ ’>.
Option 2: Copy and paste the formula from the Formula Bar to a cell, instead of from a cell to another cell.
Example, cell C12 contains a formula:
1.  Select the formula string in the Formula Bar and press <Ctrl+C> to copy it.
2.  Leave the Formula Bar by clicking the Enter or Cancel icons to the left of the fx on the Formula Bar.
3.  Select another cell and press <Ctrl+V>.

3:06 AM | 0 comments | Read More