You need to copy data in Excel but the results are wrong. Formulas turn into values, or formatting disappears. This happens because the standard paste command applies all elements from the copied cell. Excel offers specialized paste options for precise control. This article explains each paste command and when to use it.
Key Takeaways: Excel Paste Special Commands
- Paste Values (Alt + H, V, V): Pastes only the results of formulas, removing the formulas and source formatting.
- Paste Formulas (Alt + H, V, F): Pastes only the formulas, adopting the formatting of the destination cells.
- Paste Formats (Alt + H, V, R): Copies only the cell formatting like colors and borders, not the cell content.
Understanding Excel’s Paste Special Menu
The standard Ctrl + V paste copies everything: values, formulas, formatting, data validation, and column widths. This often creates errors. The Paste Special dialog provides separate commands for each element. You access it from the Home tab or with the right-click menu. Knowing what each icon and command does prevents data corruption and saves time fixing mistakes.
Core Paste Categories
Paste options fall into three groups. The Paste group inserts the copied data. The Paste Values group deals only with numbers and text. The Other Paste Options group handles formatting and links. Some commands, like Paste Formulas, ignore the destination’s number format. Others, like Keep Source Formatting, will overwrite existing cell styles.
Steps for Using Key Paste Options
Follow these steps to apply the most common and useful paste commands correctly.
- Copy the source cells
Select the cell or range you want to copy. Press Ctrl + C or right-click and choose Copy. The moving border appears around the copied cells. - Select the destination
Click the top-left cell where you want the pasted data to begin. You can select a single cell or a range matching the copy area’s size. - Open the Paste Special menu
Go to Home > Clipboard. Click the arrow under the Paste icon. Alternatively, right-click the destination cell and find the Paste Options icons, or press Alt + H, V, S for the full dialog. - Paste only values
In the Paste Special menu, click the Values (123) icon or press Alt + H, V, V. This pastes the results of formulas as static numbers. It uses the number format of the destination cells. - Paste only formulas
Click the Formulas (fx) icon or press Alt + H, V, F. This copies the formulas without any formatting. Relative cell references will adjust based on the new location. - Paste formatting
Click the Formatting (paintbrush) icon or press Alt + H, V, R. This applies the source cell’s font, fill, borders, and alignment to the destination cells, leaving their content unchanged.
Using Keyboard Shortcuts for Speed
After copying, use these sequences from the keyboard. Alt + H, V, V pastes values. Alt + H, V, F pastes formulas. Alt + H, V, E opens the Transpose option. Alt + H, V, K pastes with source column widths. These shortcuts bypass the mouse menu for faster work.
Common Paste Mistakes and How to Avoid Them
Using the wrong paste option can break your workbook. Here are frequent errors and their solutions.
Pasting Formulas That Return #REF! Errors
This occurs when you paste formulas that reference other sheets or workbooks, and those references break. To avoid this, check formula references before copying. For moving formulas within the same sheet, use Cut (Ctrl + X) and Paste instead of Copy. This preserves internal references.
Losing Number Formatting When Pasting Values
The standard Paste Values command uses the destination’s format. If you need to keep the source’s date or currency style, use Paste Values and Number Formatting. Press Alt + H, V, A. This pastes the results and the original number format together.
Accidentally Pasting Hidden Data or Column Widths
The regular paste can copy hidden rows or adjust column widths unexpectedly. To paste only visible cells, first use Alt + ; to select visible cells only, then copy. To prevent column width changes, avoid using the Keep Source Column Widths option unless needed.
Paste Special Operation Options Compared
| Item | Paste Values | Paste Formulas | Paste Formats |
|---|---|---|---|
| Primary Use | Create static data from formulas | Copy calculation logic | Apply visual styling |
| Keyboard Shortcut | Alt + H, V, V | Alt + H, V, F | Alt + H, V, R |
| Source Formatting | Discarded | Discarded | Copied exactly |
| Destination Formatting | Kept intact | Kept intact | Overwritten |
| Best For | Final reports, archiving | Template building, model replication | Standardizing sheet appearance |
You can now paste data in Excel with precision. Use Paste Values for final numbers and Paste Formulas to extend calculations. Try the Paste Link option to create dynamic connections between sheets. For advanced control, use the Paste Special dialog and select multiple attributes like Values and Formats together.