SPECIAL PASTE OPTIONS

2.10

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

AND COMMENTS)

Refer to page 56 in chapter 3.

2.10.B PASTING THE RESULT OF A FORMULA, BUT NOT THE

FORMULA ITSELF

Refer to page 53 in chapter 3.

47

Statistical Analysis with Excel

CUTTING AND PASTING FORMULAE

2.11

2.11.A THE DIFFERENCE BETWEEN “COPYING AND PASTING”

FORMULAS AND “CUTTING AND PASTING” FORMULAS

Click on cell F223, select the option EDIT/CUT, click on cell H224 and

choose the menu option EDIT/PASTE. The formula in the “pasted on” cell

is the same as was in the “cut from” cell. (The formula “=C223 + D223.”)

Therefore, there is no change in the cell references after cutting“and“

pasting. While copy“and“paste automatically adjusts for cell reference

differentials, cut“and“paste does not.

If you had used copy and paste, the formula in H224 would be “=D224 +

E224.”

Figure 35: Cut from cell F223

Figure 36: Paste into cell H223. Note that the cell references do not adjust.

After doing this, select the option EDIT/UNDO because I want to

maintain the formulas in F223” F235 (and not because it is required for

a cut and paste operation).

48

Chapter 2: Copying/Cutting and pasting formulae

CREATING A TABLE OF FORMULAS USING

2.12

DATA/TABLE

The menu option DATA/TABLE supposedly offers a tool for creating an X-

Y table of formula results. However, the method needs so much data

arrangement that it is no better than using a simple copy and paste

operation on cells!

SAVING TIME BY WRITING, COPYING AND PASTING

2.13

FORMULAS ON SEVERAL WORKSHEETS

SIMULTANEOUSLY

Refer to Volume 3: Excel“ Beyond The Basics to learn how to work with

multiple worksheets. The section will request you to follow our example

of writing a formula for several worksheets together.

49

Page for Notes

Chapter 3: Paste Special

CHAPTER 3

PASTE SPECIAL

This chapter teaches the following topics:

” PASTING THE RESULT OF A FORMULA, BUT NOT THE

FORMULA

” OTHER SELECTIVE PASTING OPTIONS

” PASTING ONLY THE FORMULA (BUT NOT THE FORMATTING

AND COMMENTS)

” PASTING ONLY FORMATS

” PASTING DATA VALIDATION SCHEMES

” PASTING ALL BUT THE BORDERS

” PASTING COMMENTS ONLY

” PERFORMING AN ALGEBRAIC “OPERATION” WHEN PASTING

ONE COLUMN/ROW/RANGE ON TO ANOTHER

” MULTIPLYING/DIVIDING/SUBTRACTING/ADDING ALL CELLS

IN A RANGE BY A NUMBER

” MULTIPLYING/DIVIDING THE CELL VALUES IN CELLS IN

SEVERAL “PASTED ON” COLUMNS WITH THE VALUES OF

THE COPIED RANGE

” SWITCHING ROWS TO COLUMNS

This less known feature of Excel has some great options that save time

and reduce annoyances in copying and pasting.