Excel Copy and Paste Special

Beginner15 min readUpdated: July 2024

Beyond Basic Copy-Paste

Paste Special is Excel's Swiss Army knife for data manipulation. Learn to copy exactly what you need - values, formats, formulas, or combinations - and transform data with transpose, operations, and more.

1. Copy and Paste Basics

Standard Copy Methods

Keyboard

Copy: Ctrl + C

Cut: Ctrl + X

Paste: Ctrl + V

Right-Click

• Copy

• Cut

• Paste Options...

Ribbon

Home Tab →

Clipboard Group

Copy/Cut/Paste buttons

What Gets Copied?

Standard copy (Ctrl+C) includes everything:

  • Cell values or formulas
  • Number formats (currency, percentage, etc.)
  • Cell formatting (colors, borders, fonts)
  • Conditional formatting rules
  • Data validation rules
  • Comments and notes

2. Introduction to Paste Special

Why Use Paste Special?

Paste Special gives you precise control over what to paste. Need just the values without formulas? Only the formatting? Want to add values to existing data? Paste Special does it all.

Accessing Paste Special

Method 1: Keyboard Shortcut

Ctrl + Alt + V

Opens Paste Special dialog after copying

Method 2: Right-Click Menu

  1. Copy cells (Ctrl+C)
  2. Right-click destination
  3. Select "Paste Special..."

Quick Paste Options

After pasting with Ctrl+V, look for the small clipboard icon that appears. Click it for quick access to common paste special options without opening the full dialog.

3. Paste Special Options Explained

📋 Paste Options

All
Everything (same as Ctrl+V)
Formulas
Only formulas, no formatting
Values
Results only, converts formulas to static values
Formats
Only formatting (colors, borders, fonts)
Comments
Only cell comments/notes
Validation
Only data validation rules

🎨 Special Paste Options

All using Source theme
Paste with original theme colors
All except borders
Everything but border formatting
Column widths
Match source column widths
Formulas and number formats
Formulas + number formatting only
Values and number formats
Static values + number formatting

Most Common Uses

📊

Values Only

Remove formulas, keep results

🎨

Formats Only

Copy styling without data

🔄

Transpose

Switch rows and columns

4. Paste Operations

Mathematical Operations

Paste Special can perform mathematical operations between copied data and existing values. This is incredibly powerful for bulk calculations.

Available Operations

  • NoneNormal paste (default)
  • AddAdd copied values to existing
  • SubtractSubtract copied from existing
  • MultiplyMultiply existing by copied
  • DivideDivide existing by copied

Practical Examples

Increase prices by 10%:

Copy 1.1, Paste Special → Multiply

Apply bulk discount:

Copy 0.9, Paste Special → Multiply

Add shipping cost:

Copy shipping amount, Paste Special → Add

5. Transpose Data

What is Transpose?

Transpose switches rows and columns. Data arranged horizontally becomes vertical, and vice versa. This is perfect for reformatting reports and reorganizing data layouts.

How to Transpose

  1. Select and copy your data range (Ctrl+C)
  2. Click destination cell where transposed data should start
  3. Open Paste Special (Ctrl+Alt+V)
  4. Check "Transpose" checkbox at the bottom
  5. Click OK - your data is now transposed!

Transpose Examples

Before (Horizontal)

JanFebMar
100150200

After (Vertical)

Jan100
Feb150
Mar200

⚠️ Transpose Limitations

  • Formulas may need adjustment after transposing
  • Cannot transpose into the same range (overlap error)
  • Merged cells don't transpose well
  • Table formatting may be lost

6. Advanced Techniques

Skip Blanks Option

The "Skip blanks" checkbox prevents pasting empty cells over existing data:

  • Useful when copying sparse data
  • Preserves existing values where copied cells are blank
  • Great for merging datasets

Paste Link

Creates formulas that reference the original cells:

  1. Copy your data (Ctrl+C)
  2. Open Paste Special (Ctrl+Alt+V)
  3. Click "Paste Link" button
  4. Creates formulas like =Sheet1!A1
  5. Updates automatically when source changes

Values + Formats Combo

Two-step process for perfect copies without formulas:

  1. First: Paste Special → Values
  2. Second: Paste Special → Formats
  3. Result: Static values with original formatting
  4. Alternative: Use "Values and number formats" option

7. Shortcuts & Tips

Essential Keyboard Shortcuts

Ctrl + Alt + V

Open Paste Special dialog

Alt + E, S

Alternative Paste Special shortcut

Alt + E, S, V

Paste values directly

Alt + E, S, T

Paste formats directly

Alt + E, S, F

Paste formulas directly

Alt + E, S, E

Transpose directly

Pro Tips

  • 💡Double-click Format Painter for multiple applications
  • 💡Use F4 to repeat last paste special action
  • 💡Right-drag for instant paste special menu
  • 💡Paste to multiple areas by selecting before pasting

Common Scenarios

  • 📋Remove formulas before sharing: Paste Values
  • 📋Copy formatting between sheets: Paste Formats
  • 📋Reorganize data layout: Transpose
  • 📋Apply calculations in bulk: Paste Operations

Time-Saving Workflow

1️⃣

Copy Once

Ctrl + C

2️⃣

Paste Many

Multiple paste specials

3️⃣

Stay Copied

Until you copy again

Quick Reference Card

Most Used Options

  • • Values: Remove formulas, keep results
  • • Formats: Copy only appearance
  • • Formulas: Copy without formatting
  • • Transpose: Switch rows/columns
  • • Column widths: Match source columns

Operations

  • • Add: Existing + Copied
  • • Subtract: Existing - Copied
  • • Multiply: Existing × Copied
  • • Divide: Existing ÷ Copied
  • • Skip blanks: Preserve existing data

Ready for the Next Step?

Continue your Excel journey with: Excel Chart Types Guide

Next Tutorial
Previous
Excel Find and Replace Guide
10 of 25
Next
Excel Chart Types Guide