When you use paste special, you’ll be presented with a window of different options. To access the paste special window, first copy your desired cell or cells. This can be done by either highlighting the cell(s) and pressing Ctrl plus C or right clicking your selection and choosing Copy.
Select the destination cell. If you’re pasting multiple cells, select the starting cell. Right click the destination cell and choose Paste Special.
You have four different sections of options including: Selection, Operations, Options and Shift Cells.
If you only want to copy certain elements from a cell or cells, select what you want to copy from the Selection options. For instance, if you only want to paste the results of a formula, uncheck Paste all and choose Text. This copies just the text in the cell and not the formula itself. Be sure to uncheck any boxes for elements you do not want to copy.
The Operations section lets you apply an operation to your pasted section. You must already have numbers in the destination cell or cells. For instance, if I copied the cells in Column A to the cells in Column B (see the original below) -
and then applied the Add operation, Column B would be changed to the following.
Pasting using operations is great when you need to calculate one column or row based upon another, without having to insert formulas for each cell.
Outside of the Selection section, the most used paste special options are in the Options section. With this section, you can skip any empty cells, which is great if you have large data sets to paste, but want to leave out any blank or empty cells. Transpose lets you turn any column into a row and vice versa. See the example below of the transpose option. Link lets you copy a cell or cells to a new location, which updates every time you change the original cells. For instance, if you changed a name in the original cell, the pasted cell would automatically change to the new name.
In this example, I copied the cells in Column A and used the transpose option to copy the cells to Row 7.
If you need to arrange cells as you paste, the options under Shift Cells works perfectly. Instead of having to insert cells to make room, simply choose to shift cells down or to the right as you paste. By default, no cells are shifted.
Whenever you use paste special in an OpenOffice Calc spreadsheet, any options you choose remain the same until you change them or open a new spreadsheet. For instance, if you choose to transpose cells as you paste, every time you use paste special, transpose is already selected. You must manually change your paste special options each time you want a new option.