All of the methods described above to move or copy data create identical copies of the original cells in the new location. This means that both the contents and the formatting of the original selection are copied to the new location and also means that any data present in the new location are deleted. It is frequently important either to alter the data before it is pasted or to merge the data in the new location with the data being pasted. The Paste Special... command enables this.
The Paste Special... command can act exactly like the Paste command or may selectively paste the cell contents, the cell formats, the calculated values of the original cells rather than their contents. described in the previous section copies both contents and formatting of the original selection. If you need more options, use Paste Special... command.
The Paste Special... command can be accessed, after a selection has been cut or copied as explained above, in one of two ways. First, the location where the pasting should happen must be selected. The easiest way to do this is to select the single cell which will be at the top left hand corner of the region of pasted cells. Alternatively, the exact region into which the cells will be copied can be selected. After the selection is made, the Paste Special... command can be chosen from the Edit menu or the context menu obtained by clicking with the right mouse button in the cell area of the spreadsheet.
The Paste Special... menu item opens a dialog with three categories. By default, Paste Special... acts as if it were the Paste menu item.
The first set of choices allow the user to control the data pasted.The user can chose to limit the pasting to only the cell contents (no cell formatting is copied) or the opposite only cell formats copied (no contents). Furthermore, the user can insert the selection while transforming all the contents into values only. Formulas will be replaced by their values.
A second set of choices allows the user to perform simple mathematical operations during the paste. These operations use the current contents of the cells in the paste range and the contents of the clipboard. For example, if you are pasting a cell containing number 5 to a cell that currently contains number 10 and choose option Divide, the result will be 10/5=2. Choosing option None will just replace the previous contents of the cells by the contents of the clipboard buffer (this is the default behavior).
The final choices contains the following options: