How to Use Paste Special in Microsoft Excel 2013

How to use Paste Special in Microsoft Excel 2013

Microsoft Excel 2013 normally copies all the information in the range of cells you select when you paste the data.   Use Excel’s Paste Special command to specify other options, such as pasting only the cell contents (without the formatting) or only the formatting (without the cell contents).   Other options such as Paste Values – remove a formula and leaves just the result, and transpose which switches your data around.

To use Paste Special

Figure – Paste Special is great for getting different results.

1. Go to the Home tab on the ribbon
2. Go to the Clipboard group
3. Click on the Paste button (1)
4. Select from the icon choices depending on what you wish to do Or
   Click on Paste Special (2)
5. Select the type of Paste Special from the dialog box (3)
6. Click OK

Figure – The Paste Special dialog box

Which Paste Special option do I choose?

• All to paste all the stuff in the cell selection (formulas, formatting, you name it).   This is what happens when you paste normally.
• Formulas to paste all the text, numbers, and formulas in the current cell selection without their formatting.
• Values to convert formulas in the current cell selection to their calculated values.
• Formats to paste only the formatting from the current cell selection, without the cell entries.
• Comments to paste only the notes that you attach to their cells (like electronic self-stick notes).
• Validation to paste only the data validation rules into the cell range that you set up with the Data Validation command.
• All Using Source Theme to paste all the information plus the cell styles applied to the cells.
• All Except Borders to paste all the stuff in the cell selection without copying any borders you use there.
• Column Widths to apply the column widths of the cells copied to the Clipboard to the columns where the cells are pasted.
• Formulas and Number Formats to include the number formats assigned to the pasted values and formulas.
• Values and Number Formats to convert formulas to their calculated values and include the number formats you assigned to all the copied or cut values.
• All merging conditional formats paste conditional formatting into the selected range
• Skip Blanks: Select this check box when you want Excel to paste only from the cells that aren’t empty.
• Transpose: Select this check box when you want Excel to change the orientation of the pasted entries.   For example, if the original cells’ entries run down the rows of a single column of the worksheet, the transposed pasted entries will run across the columns of a single row.
• Paste Link: Click this button when you want to establish a link between the copies you’re pasting and the original entries.   That way, changes to the original cells automatically update in the pasted copies.

Operations

When you paste, you can also perform some simple math calculations based on the value(s) in the copied or cut cell(s) and the value in the target cell(s):

• None: Excel performs no operation between the data entries you cut or copy to the Clipboard and the data entries in the cell range where you paste.   This is the default setting.
• Add: Excel adds the values you cut or copy to the Clipboard to the values in the cell range where you paste.
• Subtract: Excel subtracts the values you cut or copy to the Clipboard from the values in the cell range where you paste.
• Multiply: Excel multiplies the values you cut or copy to the Clipboard by the values in the cell range where you paste.
• Divide: Excel divides the values you cut or copy to the Clipboard by the values in the cell range where you paste.

Transpose Columns and Rows

Figure – Transpose – really useful when you start your worksheet and realize it is the wrong way around

Suppose you created a table of data and then realized that you would prefer it if the data was arranged a different way.   Excel allows you to Transpose the columns to the rows and the rows to the columns.   In the image above I switched the data around so that the years are in the first column instead of the top row and the months are along the top instead of the first column.

To do this:

1. Select the data to transpose including the headings
2. Click Copy
3. Click into a cell where you want the data to appear (do not try and paste over the selected data as it will not work!)
4. Click the Drop down Arrow under the Paste Icon
5. Select the icon for Transpose or press T

Has this tip helped you? Do let us know how – leave a comment in the comment box.  I love to hear from my readers about how the tips are helpful.

This topic has been taken from my free eBook on Microsoft Excel 2013 Core : Intro which can be downloaded here:

To find out more about our training and to discuss your organisation’s learning needs, give us a call on 020 8203 1774 or email us enquiries@theittrainingsurgery.com – we would love to chat.