51

Statistical Analysis with Excel

PASTING THE RESULT OF A FORMULA, BUT NOT

3.1

THE FORMULA

Sometimes one wants the ability to copy a formula (for example, “=C223 +

D223)”) but paste only the resulting value. (The example that follows will

make this clear.)

Select the range “F223:F235” on worksheet ““main.”

Choose the menu option FILE/NEW and open a new file. Go to any cell in

this new file and choose the menu option EDIT/PASTE SPECIAL.

In the area “Paste,” choose the option “Values” as shown in Figure 37.

Figure 37: The PASTE SPECIAL dialog in Excel versions prior to Excel XP

52

Chapter 3: Paste Special

In Excel XP, the “Paste

Special” dialog has three

additional options:

• Paste Formulas

Figure 38: “Paste Special” dialog In Excel XP,

and number

formats (and not

other cell

formatting like

font, background

color, borders, etc)

• Paste Values and

number formats

(and not other cell

formatting like

font, background

color, borders, etc)

• Paste only

“Column widths.”

In Excel XP, the “Paste” icon

provides quick access to some

Figure 39: The pasting options can be accessed by

types of “Paste Special.” The

clicking on the arrow to the right of the “Paste” icon

options are shown in the next

figure.

The calculated values in the

“copied” cells are pasted. The

formula is not pasted. Try

the same experiment using

EDIT/PASTE instead of

EDIT/PASTE SPECIAL. The

usefulness of the former will

53

Statistical Analysis with Excel

In Excel XP, the “Paste

Special” dialog has three

additional options:

• Paste Formulas

Figure 38: “Paste Special” dialog In Excel XP,

and number

formats (and not

other cell

formatting like

font, background

color, borders, etc)

• Paste Values and

number formats

(and not other cell

formatting like

font, background

color, borders, etc)

• Paste only

“Column widths.”

be apparent.

54

Chapter 3: Paste Special

OTHER SELECTIVE PASTING OPTIONS

3.2

3.2.A PASTING ONLY THE FORMULA (BUT NOT THE FORMATTING

AND COMMENTS)

Choose the option “Formulas” in the area “Paste” of the dialog (user-input

form) associated with the menu “EDIT/PASTE SPECIAL.” This feature

makes the pasted values free from all cell references. The “pasted on”

range will only contain pure numbers. The biggest advantage of this

option is that it enables the collating of formula results in different

ranges/sheets/workbooks onto one worksheet without the bother of

maintaining all the referenced cells in the same workbook/sheet as the

collated results.