Excel Paste Special
Paste Special gives you precise control over what gets pasted when you copy cells in Excel. A regular Ctrl+V pastes everything — values, formulas, formatting, borders, and comments. Paste Special lets you paste only the part you actually need, which prevents formatting conflicts and formula errors.
Opening Paste Special
Copy a cell or range first using Ctrl+C. Then use one of these methods to open Paste Special:
Press Ctrl + Alt + V — the Paste Special keyboard shortcut. Or right-click the destination cell and choose Paste Special. Or go to Home → Paste → Paste Special.
The Paste Special Dialog
PASTE SPECIAL DIALOG +----------------------------------------+ | PASTE: | | ● All ○ Values | | ○ Formulas ○ Formats | | ○ Comments ○ Validation | | ○ All using source theme | | ○ All except borders | | ○ Column widths | | ○ Formulas and number formats | | ○ Values and number formats | +----------------------------------------+ | OPERATION: | | ● None ○ Add ○ Subtract | | ○ Multiply ○ Divide | +----------------------------------------+ | □ Skip blanks □ Transpose | +----------------------------------------+
Paste Options Explained
| Option | What Gets Pasted | When to Use |
|---|---|---|
| All | Everything — same as Ctrl+V | General copying |
| Values | Numbers and text only — no formulas | Freeze formula results as fixed numbers |
| Formulas | Formulas only — no formatting | Copy logic without copying styles |
| Formats | Formatting only — no values | Apply the look of one range to another |
| Comments | Cell comments only | Transfer notes without changing data |
| Validation | Data validation rules only | Apply dropdown rules to a new range |
| All except borders | Everything except cell borders | Avoid overriding table borders in destination |
| Column widths | Column width settings only | Match column widths to a copied layout |
| Values and number formats | Numbers + their format (currency, %, date) | Copy currency totals without formulas |
The Most Important Paste: Values Only
Pasting values removes the formula and keeps only the result. This is useful when you want to share data without exposing formulas, or when you need a permanent snapshot of calculated results.
Values Paste Diagram
SOURCE (A1): =B1*C1 → displays 5000 PASTE ALL into D1: D1 = =B1*C1 (formula still active, links to B and C) PASTE VALUES into D1: D1 = 5000 (fixed number, no formula, no dependency)
Shortcut for paste values only: Ctrl+Alt+V → V → Enter. Press Ctrl+Alt+V to open Paste Special, press V to select Values, press Enter. Three keystrokes, no mouse needed.
Paste Operations — Arithmetic on Paste
The Operation section lets you perform a calculation while pasting. This changes the destination cell's current value using the copied value.
Operation Example: Adding a Fixed Amount to a Range
Task: Increase all prices in B2:B10 by 500. Step 1: Type 500 in any empty cell, say D1. Step 2: Copy D1 (Ctrl+C). Step 3: Select B2:B10. Step 4: Open Paste Special (Ctrl+Alt+V). Step 5: Choose Values, then choose Add. Step 6: Click OK. Result: 500 is added to every value in B2:B10. No helper column needed.
| Operation | Effect | Example (copied value = 10) |
|---|---|---|
| None | Replaces the destination value | Cell had 50 → becomes 10 |
| Add | Adds the copied value to each destination cell | Cell had 50 → becomes 60 |
| Subtract | Subtracts the copied value from each cell | Cell had 50 → becomes 40 |
| Multiply | Multiplies each cell by the copied value | Cell had 50 → becomes 500 |
| Divide | Divides each cell by the copied value | Cell had 50 → becomes 5 |
Multiply Operation Use Case: Convert Currency
Task: Convert all USD values in column B to INR (rate: 83). Step 1: Type 83 in an empty cell. Step 2: Copy that cell. Step 3: Select all USD values in column B. Step 4: Paste Special → Values → Multiply → OK. Every value in column B is multiplied by 83. The conversion happens in-place.
Transpose — Flip Rows and Columns
Transpose rotates data so rows become columns and columns become rows.
Transpose Diagram
ORIGINAL (horizontal layout): +------+------+------+------+ | Jan | Feb | Mar | Apr | +------+------+------+------+ | 1000 | 1200 | 900 | 1500 | +------+------+------+------+ AFTER TRANSPOSE (vertical layout): +------+------+ | Jan | 1000 | +------+------+ | Feb | 1200 | +------+------+ | Mar | 900 | +------+------+ | Apr | 1500 | +------+------+
Select and copy your original data. Click the destination cell. Open Paste Special. Check the Transpose box. Click OK.
Skip Blanks
Skip Blanks prevents empty cells in the copied range from overwriting values in the destination range.
COPIED RANGE (A1:A4): DESTINATION (B1:B4): RESULT (with Skip Blanks): 100 500 100 (blank) 200 200 ← not overwritten 300 800 300 (blank) 400 400 ← not overwritten
Without Skip Blanks, the blank cells in the source would erase 200 and 400 in the destination. Skip Blanks preserves any existing value in the destination wherever the source is empty.
Paste Special Quick Reference
| Task | Paste Special Setting |
|---|---|
| Remove formulas, keep numbers | Values |
| Copy only the visual style | Formats |
| Apply a calculation to a range | Values + Add/Subtract/Multiply/Divide |
| Flip rows and columns | Transpose |
| Paste without overwriting existing values | Skip Blanks |
| Copy dropdown rules to another range | Validation |
Paste Special is one of those features that experienced Excel users rely on daily. Once you know the keyboard shortcut Ctrl+Alt+V, it becomes faster than using the mouse. It eliminates the need for helper columns, manual arithmetic, and reformatting after every paste.