Copy and paste is the most frequently performed action in any Office application — yet most users only know Ctrl+C and Ctrl+V. Excel's copy-paste system is far richer than this. Paste Special alone offers 16 different paste modes that control exactly what is transferred from source to destination: values only, formats only, formulas only, column widths, comments, validation rules, and more. Mastering these tools eliminates a huge category of common errors and dramatically speeds up spreadsheet work.
When you copy a cell or range in Excel, the selection is surrounded by a moving dashed border (sometimes called "marching ants"). This signals that the clipboard is active and the content is ready to paste.
When you copy a cell, Excel copies everything about it:
A standard paste (Ctrl+V) transfers all of this. Paste Special lets you choose to paste only specific parts.
| Method | How |
|---|---|
| Keyboard (fastest) | Select cells → Ctrl+C |
| Right-click | Right-click the selection → Copy |
| Ribbon | Home → Clipboard group → Copy button |
| Drag-and-drop copy | Select cells → hold Ctrl → hover over the selection border until cursor shows a + arrow → drag to destination → release. No marching ants; clipboard is not used. |
| Method | How |
|---|---|
| Keyboard | Select cells → Ctrl+X |
| Right-click | Right-click → Cut |
| Drag-and-drop move | Select cells → hover over the selection border until cursor shows a four-arrow move icon → drag to the new location → release. The original cells are emptied. |
| Method | How |
|---|---|
| Keyboard (fastest) | Ctrl+V |
| Right-click | Right-click destination → Paste (or choose from Paste Options icons) |
| Ribbon | Home → Clipboard → Paste button (top part) |
| Enter key | After copying: navigate to destination → press Enter instead of Ctrl+V. This pastes once and clears the marching ants (same as a single Ctrl+V followed by Esc). Use when you only need one paste. |
After pasting with Ctrl+V, a small Paste Options button (Ctrl) appears at the bottom-right of the pasted range. Click it (or press Ctrl) to see quick paste format options without opening Paste Special:
Paste Special is the professional's paste tool. It lets you choose precisely what to transfer from the copied source — just the values, just the formats, just the column widths, or combinations. It also provides operations (Add, Subtract, Multiply, Divide) and the critical Transpose function.
| Option | What Is Pasted | Best Use Case |
|---|---|---|
| All | Everything — identical to a standard Ctrl+V paste | Default paste when you need everything transferred |
| Formulas | The formula (or value for non-formula cells) only — no formatting is transferred | Copying a formula to a different area that has its own formatting you want to preserve |
| Values ★ | The displayed value only — formulas are converted to their calculated result. No formatting transferred. | The most important Paste Special option. Used to: break a formula's dependency on other cells; create a static snapshot of calculated data; prevent circular references; remove formulas before sharing a file |
| Formats | Only the formatting — no values or formulas transferred. The destination cell keeps its existing values but gets new formatting. | Applying the same formatting from a well-formatted cell to other cells (alternative to Format Painter); applying number formats, borders, fill colours across a range |
| Comments and Notes | Only the comments/notes attached to the cell | Moving annotations to cells that have been relocated |
| Validation | Only the data validation rules (dropdown lists, input restrictions, error messages) | Applying the same data validation rules to additional columns or rows without recreating them |
| All Using Source Theme | All content and formatting, keeping the source workbook's theme colours | Pasting from a workbook with a different colour theme and wanting to preserve the original colours |
| All Except Borders | Everything except border formatting | Pasting into an area that has a border layout you want to preserve |
| Column Widths ★ | Only the column width settings — no values, formulas, or formatting | Copying a table's column width layout to a new sheet. Select the source columns → Copy → click destination columns → Paste Special → Column Widths. The destination columns are instantly resized to match. |
| Formulas and Number Formats | The formula (or value) plus the number format (currency, percentage, date, etc.) — but not font, fill, or border formatting | Copying calculations to an area with different visual formatting but where you need the number format (e.g., Rand currency) to transfer |
| Values and Number Formats | The displayed value plus the number format — no formulas, no visual formatting | Creating a static copy of calculated values that keeps the Rand format, percentage display, or date format of the source |
| All Merging Conditional Formats | All content, merging any conditional formatting rules from the source with those at the destination | Combining conditional formatting rules from multiple sources |
The Operation section of the Paste Special dialog applies arithmetic between the copied value and the existing destination value — without writing a formula. This is one of Excel's most powerful and least-known features.
Copy a cell with a value → select the destination range → Paste Special → choose an Operation:
| Operation | What It Does | Example |
|---|---|---|
| None | No arithmetic — standard paste (default) | — |
| Add | Adds the copied value to each destination cell value | Copy 500 → select B2:B20 (containing salaries) → Paste Special → Add. Every salary increases by R500 instantly — no formula needed. |
| Subtract | Subtracts the copied value from each destination cell value | Copy 100 → select a range of prices → Paste Special → Subtract. Reduces every price by R100. |
| Multiply | Multiplies each destination value by the copied value | Copy 1.15 → select a range of prices → Paste Special → Multiply. Increases all prices by 15% (VAT-inclusive) in one step. |
| Divide | Divides each destination value by the copied value | Copy 1000 → select a range of values in Rands → Paste Special → Divide. Converts all values from Rand to Thousands (R500,000 becomes 500). |
The Skip Blanks checkbox in Paste Special prevents blank cells in the copied range from overwriting non-blank cells at the destination:
Transpose rotates data 90 degrees — rows become columns and columns become rows. This is essential when data arrives in the wrong orientation or when a report layout requires restructuring.
If the source range contains formulas, you typically want to transpose the values (not the formulas), since formula references become incorrect after transposing. Tick both Values and Transpose in the Paste Special dialog.
For a live transposed view that updates when the source changes, use the TRANSPOSE worksheet function:
=TRANSPOSE(A1:L12), then press Ctrl+Shift+Enter to confirm as an array formulaPastes the copied range as a flat, non-editable image. The image cannot be filtered, sorted, or used in formulas. Useful for embedding a snapshot of a range in a dashboard or a different sheet.
A Linked Picture is a "live camera" of a source range — it looks like a picture but automatically updates whenever the source data changes. This is the foundation of many Excel dashboards.
The camera tool is not on the Ribbon by default but can be added to the QAT:
Fill commands copy the content of one cell into adjacent cells without using the clipboard. They are faster than copy-paste when filling adjacent ranges.
The standard Windows clipboard holds only one copied item at a time. The Office Clipboard stores up to 24 separately copied items — text, cells, images — from any Office application, allowing you to paste any of them in any order.
| Problem | Cause | Fix |
|---|---|---|
| Formula pasted but shows wrong result | Relative references shifted incorrectly when the formula moved to a new location | Use absolute references ($A$1) in the source formula where the reference should not shift. Or paste Values only to break the formula and keep the static result. |
| Pasting overwrites existing formatting | Standard paste transfers all formatting from the source | Use Paste Special → Values (to keep destination formatting) or Formulas (to paste the formula without any formatting). |
| "Copy area and paste area are not the same size" error | The destination selection is a different size from the source, OR the destination contains merged cells | Click a single cell as the destination (Excel pastes from that cell outward to match the source size). If merged cells are involved, unmerge the destination area first. |
| Pasted numbers are stored as text | Source had numbers formatted as text, or data was pasted from a web page or PDF | Select the affected cells → click the green triangle warning → "Convert to Number". Or: Data → Text to Columns → Finish (converts text-numbers to real numbers). |
| Pasting from web/Word brings unwanted formatting | Standard paste brings all the source application's formatting | After pasting, click the Paste Options button (Ctrl) → select Match Destination Formatting or Values Only. Or use Paste Special → Values before pasting. |
| Paste Special is greyed out or not available | The marching ants border has been cancelled (Esc was pressed, or another action was performed before pasting) | Copy the source range again (Ctrl+C) → immediately open Paste Special (Ctrl+Alt+V) without pressing anything else in between. |
| VLOOKUP or formula gives wrong result after copying | The lookup table range reference shifted when the formula was copied | Use absolute references for the lookup table: =VLOOKUP(A2, $F$2:$G$100, 2, 0). The $ signs lock the range so it does not shift when copied. |
| Shortcut | Action |
|---|---|
| Ctrl+C | Copy selected cells to clipboard |
| Ctrl+X | Cut selected cells |
| Ctrl+V | Paste (standard) |
| Ctrl+Alt+V | Open Paste Special dialog ★ |
| Enter (after copy) | Paste once and clear the clipboard (single-use paste) |
| Ctrl+D | Fill Down (copy top cell to selected cells below) |
| Ctrl+R | Fill Right (copy left cell to selected cells to the right) |
| Ctrl+Z | Undo paste (if you made a mistake immediately after pasting) |
| Esc | Cancel the copy (clears marching ants without pasting) |
| Ctrl+Alt+V → V → Enter | Paste Values only (fastest keyboard-only method for values paste) |
| Ctrl+Alt+V → T → Enter | Paste Formats only |
| Ctrl+Alt+V → W → Enter | Paste Column Widths only |
| Ctrl+Alt+V → E → Enter | Paste and Transpose |
Q1: You have a column of 100 prices calculated by complex formulas referencing other sheets. You need to email this data to a client, but you want to send only the final numbers — not the formulas or the dependency on other sheets. How do you convert the formulas to static values?
✓ Select the column of formula cells (e.g., B2:B101) → Ctrl+C to copy → immediately press Ctrl+Alt+V to open Paste Special → select Values in the Paste section → click OK. The formulas are replaced by their calculated values in the same cells — the formulas are gone and only the numbers remain. Alternatively: after copying, right-click the same selected cells → Paste Special → Values → OK. The file can now be shared without the underlying formulas or inter-sheet dependencies.
Q2: Your product catalogue has prices in column B (B2:B50). The VAT rate has changed to 15% and all prices must increase by 15%. You must change the values in place — no helper columns. What is the most efficient method?
✓ Type 1.15 in any empty cell (e.g., D1) → Copy it (Ctrl+C) → select B2:B50 → Ctrl+Alt+V to open Paste Special → in the Paste section select Values → in the Operation section select Multiply → click OK. All 49 prices are multiplied by 1.15 simultaneously — no formula column, no dragging, no manual editing. Delete the 1.15 from D1 afterwards. The entire operation takes about 10 seconds.
Q3: You have a table with data in 12 columns (January through December as headers) and 5 rows (product categories). You need to restructure it so months are rows (12 rows) and categories are columns (5 columns). How do you do this?
✓ Select the entire source table (including headers) → Ctrl+C → click a blank destination cell outside the current table (important: the destination must not overlap the source) → Ctrl+Alt+V to open Paste Special → tick Transpose → click OK. The table is pasted with rows and columns swapped — what was a 5-row × 12-column table becomes a 12-row × 5-column table. If the source contains formulas and you want static values in the transposed version, tick both Values AND Transpose before clicking OK.
Q4: You have formatted a table on Sheet1 with specific column widths (each carefully adjusted). You need to create an identical layout on Sheet2 with the same column widths but different data. How do you copy just the column widths?
✓ On Sheet1: select the columns you want to copy (click column A header → Shift+click the last column header) → Ctrl+C → navigate to Sheet2 → click the first column header in the destination area → Ctrl+Alt+V → in Paste Special select Column Widths → OK. The destination columns on Sheet2 are instantly resized to match the source column widths exactly. No data or formatting is changed on Sheet2 — only the column widths are updated.
Q5: After pasting data from a website into Excel, all the numbers appear left-aligned and show a green triangle warning in the corner. Formulas using these numbers return 0 or errors. What is the problem and how do you fix it?
✓ The numbers are stored as text — left-alignment and green triangle warnings are the key indicators. Excel cannot calculate with text-stored numbers. Fix option 1: select the affected cells → click the green triangle on any cell → from the warning dropdown choose Convert to Number. Fix option 2: type 1 in a blank cell → copy it → select the text-number cells → Paste Special → Values + Multiply operation → OK. Multiplying by 1 forces Excel to convert text to numbers. Fix option 3: select the column → Data tab → Text to Columns → click Finish immediately. This triggers Excel to re-evaluate the column and convert text-numbers to real numbers.
Q6: What is the difference between "Paste as Picture" and "Paste as Linked Picture", and when would you use each?
✓ Paste as Picture creates a completely static image snapshot of the copied range at the moment of pasting. The image never changes even if the source data changes. Use it for: inserting a screenshot of data into a report or presentation where you want a permanent visual record of data at a specific point in time. Paste as Linked Picture (Camera) creates a live image that automatically updates whenever the source data changes. The image always reflects the current state of the source range. Use it for: dashboards where you want a formatted view of data from another sheet embedded in the dashboard layout; when the source data will change frequently and the image must always be current. The Linked Picture updates automatically without any manual intervention.