Microsoft Office Tutorials and References
In Depth Information
Cutting and pasting
You’ll find the MonthlyClaims.xlsx file with the other examples on the companion website.
The other options in the Operation area of the Paste Special dialog box combine the
contents of the copy and paste ranges using the appropriate operators. Just remember that
the Subtract option subtracts the copy range from the paste range, and the Divide option
divides the contents of the paste range by the contents of the copy range. Also note that
if the copy range contains text entries and you use Paste Special with an Operation option
(other than None), nothing happens.
Select the Values option when you use any Operation option. As long as the entries in the
copy range are numbers, you can use All, but if the copy range contains formulas, you’ll get
“interesting” results. As a rule, avoid using the Operation options if the paste range contains
Excel assigns the value 0 to blank spaces in the copy and paste ranges, regardless of
which Operation option you select.
The Paste Link button in the Paste Special dialog box, shown in Figure 8-4, is a handy way
to create references to cells or ranges. Although the Paste Special dialog box offers more
options, using the Paste Link command on the Paste menu on the Home tab is more
convenient. When you click Paste Link, Excel enters an absolute reference to the copied cell in
the new location. For example, if you copy cell A3, select cell B5, click the Paste menu, and
then click Paste Link, Excel enters the formula =$A$3 in cell B5.
If you copy a range of cells, Paste Link enters a similar formula for each cell in the copied
range to the same-sized range in the new location.
For more information about absolute references, see “Understanding relative, absolute, and
mixed references” in Chapter 12, “Building formulas.”
Skipping blank cells
The Paste Special dialog box contains a Skip Blanks check box you can select when you
want Excel to ignore any blank cells in the copy range. If your copy range contains blank
cells, Excel usually pastes them over the corresponding cells in the paste area. As a result,
empty cells in the copy range overwrite the contents, formats, and comments in
corresponding cells of the paste area. When you select Skip Blanks, however, the corresponding
cells in the paste area are unaffected by the copied blanks.