Figure 40: Pasting formulas only

3.2.B PASTING ONLY FORMATS

Choose the option “Formats” in the area “Paste” of the dialog associated

with the menu “EDIT/PASTE SPECIAL use the “Format Painter” icon. I

prefer using the icon.

Refer to Volume 1: Excel For Beginners for a discussion on the format

painter.

55

Statistical Analysis with Excel

3.2.C PASTING DATA VALIDATION SCHEMES

Pick the option “Validation” in the area “Paste” of the dialog associated

with the menu “EDIT/PASTE SPECIAL.” Data validation schemes are

discussed in Volume 4: Managing & Tabulating Data in Excel. This

option can be very useful in standardizing data entry standards and rules

across an institution.

3.2.D PASTING ALL BUT THE BORDERS

Choose the option “All except borders” in the area “Paste” of the dialog

associated with the menu “EDIT/PASTE SPECIAL.” All other formatting

features, formulae, and data are pasted. This option is rarely used.

3.2.E PASTING COMMENTS ONLY

Pick the option “Comments” in the area “Paste” of the dialog associated

with the menu “EDIT/PASTE SPECIAL.” Only the comments are pasted.

The comments are pasted onto the equivalently located cell. For example,

a comment on the cell that is in the third row and second column that is

copied will be pasted onto the cell that is in the third row and second

column of the “pasted on” range. This option is rarely used.

56

Chapter 3: Paste Special

PERFORMING AN ALGEBRAIC “OPERATION” WHEN

3.3

PASTING ONE COLUMN/ROW/RANGE ON TO

ANOTHER

3.3.A MULTIPLYING/DIVIDING/SUBTRACTING/ADDING ALL CELLS

IN A RANGE BY A NUMBER

Assume your data is expressed in millions. You need to change the units

to billions” that is, divide all values in the range by 1000. The complex

way to do this would be to create a new range with each cell in the new

range containing the formula “cell in old range/1000.” A much simpler

way is to use PASTE SPECIAL. On any cell in the worksheet, write the

number 1000. Click on that cell and copy the number. Choose the range

whose cells need a rescaling of units. Go to the menu option EDIT/PASTE

SPECIAL and choose “Divide” in the area Options. The range will be

replaced with a number obtained by dividing each cell by the copied cells

value!

The same method can be used to multiply, subtract or add a number to all

cells in a range

Figure 41: You can multiply (or add/subtract/divide) all cells in the “pasted on” range by

(to/by/from) the value of the copied cell

57

Statistical Analysis with Excel

3.3.B MULTIPLYING/DIVIDING THE CELL VALUES IN CELLS IN

SEVERAL “PASTED ON” COLUMNS WITH THE VALUES OF THE

COPIED RANGE

You can use the same method to add/subtract/multiply/divide one

column™s (or row™s) values to the corresponding cells in one or several

“pasted on” columns (or rows).

Copy the cells in column E and paste special onto the

cells in columns C and D choosing the option “Add” in the area

“Operation” of the paste special dialog. (You can use EDIT/UNDO to

restore the file to its old state.)

SWITCHING ROWS TO COLUMNS

3.4

Choose any option in the “Paste” and “Operations” areas and choose the

option “Transpose.” If pasting a range with many columns and rows you

may prefer to paste onto one cell to avoid getting the error “Copy and

Paste areas are in different shapes.”

58

Page for Notes

Statistical Analysis with Excel

CHAPTER 4

INSERTING FUNCTIONS