Excel Paste Special: shortcuts to copy values, comments, column width, etc.

This tutorial unlocks the power of Excel's Paste Special feature, showing you how to significantly boost your efficiency with paste special shortcuts. Learn to paste values, formulas, comments, formats, column widths, and more, all while avoiding common copy-paste pitfalls.

Everyone knows Ctrl C and Ctrl V, but Paste Special offers granular control, letting you paste only specific attributes of a cell. This makes pasting smoother, allowing you to retain or remove formatting as needed, or paste only values or formulas.

- Understanding Excel's Paste Special

• Mastering Paste Special in Excel
• Paste Special Options and Keyboard Shortcuts
• Practical Paste Special Examples:
  • Copying Comments
  • Copying Values
  • Copying Column Width
  • Copying Column Width and Contents Simultaneously
  • Performing Arithmetic Operations During Pasting
• Troubleshooting Paste Special Issues

Understanding Excel's Paste Special

When standard copy-paste isn't ideal, Paste Special provides options to paste specific cell elements or perform mathematical operations on copied data. For instance, paste only calculated values from formula-driven data, copy column widths, or transpose ranges (rows to columns, or vice versa). The image below shows all available options:

Paste Special works within a worksheet, across sheets, and even between workbooks.

Mastering Paste Special in Excel

Using Paste Special is straightforward:

1. Copy the source cell(s) (Ctrl C is quickest).
2. Select the destination cell(s).
3. Access the Paste Special dialog (the shortcut is fastest).
4. Choose your paste option and click OK (or press Enter).

Three Ways to Access Paste Special

Excel offers multiple ways to access Paste Special:

1. Ribbon: On the Home tab, in the Clipboard group, click Paste > Paste Special.

2. Right-Click Menu: Right-click the destination cell and select Paste Special. A quick-access menu shows six common options: paste all, values, formulas, transpose, formatting, and link. Hovering over "Paste Special…" reveals fourteen more. Live preview shows the effect before pasting.

3. Keyboard Shortcut:
   • Excel 365 - 2007: Ctrl Alt V
   • All Excel versions: Alt E, then S

Paste Special Keyboard Shortcuts

After opening the Paste Special dialog (Ctrl Alt V), use these single-letter shortcuts:

Shortcut	Operation	Description
A	All	Paste contents and formatting.
F	Formula	Paste formulas only.
V	Values	Paste values, not formulas.
T	Formats	Paste formatting only.
C	Comments	Paste comments only.
N	Data Validation	Paste data validation settings.
H	All using source theme	Paste all with source theme formatting.
X	All except borders	Paste all, excluding borders.
W	Column width	Paste column width only.
R	Formulas and number formats	Paste formulas and number formats.
U	Values and number formats	Paste values and number formats.
D	Add	Add copied data to destination data.
S	Subtract	Subtract copied data from destination data.
M	Multiply	Multiply copied data by destination data.
I	Divide	Divide copied data by destination data.
B	Skip blanks	Skip blank cells in the copied range.
E	Transpose	Transpose rows and columns.
L	Link	Create links to the source data.

Practice these shortcuts for increased efficiency. Remember, the underlined letter in the Paste Special dialog indicates the shortcut key.

Practical Paste Special Examples

Let's explore common uses:

• Copying Comments: Copy comments without values or formatting using Ctrl Alt V, then C.

• Copying Values: Replace formulas with calculated values using Ctrl Alt V, then V. Use Ctrl Alt V, then U to retain number formats.

• Transposing Data: Quickly transpose a table using Ctrl Alt V, then E.

• Copying Column Width: Copy a column's width to others using Ctrl Alt V, then W.

• Copying Column Width and Contents: Right-click, hover over Paste Special, and select "Keep Source Column Width".

• Arithmetic Operations: Multiply amounts by percentages using Ctrl Alt V, then M. This can also be used to remove hyperlinks by multiplying by 1.

Troubleshooting Paste Special Issues

If Paste Special is missing or malfunctioning:

• Disabled Feature: Check File > Options > Advanced > Cut, copy and paste and enable "Show Paste Options button".

• Conflicting Add-ins: Run Excel in Safe Mode (hold Ctrl while launching). If Paste Special works, re-enable add-ins one by one to identify the culprit.

Mastering Paste Special enhances your Excel skills, saving time and increasing efficiency.

The above is the detailed content of Excel Paste Special: shortcuts to copy values, comments, column width, etc.. For more information, please follow other related articles on the PHP Chinese website!