CONTENTS

Keep Excel Column Widths When Pasting — The Safest Way to Preserve Column Sizes

Have you ever copied and pasted data only to have the column widths change enough to ruin the entire table? This guide explains how to solve the “keep Excel column widths when pasting” problem quickly and repeat the process safely in real-world work. It covers Ribbon paths, keyboard shortcuts, practical examples, and even VBA automation.

Quick Fix: Do It in 10 Seconds

1) Paste Values or Formulas Only (Do Not Change Column Widths)

Keyboard shortcut: Ctrl + Alt + V → V (Values) or F (Formulas) → Enter
Ribbon: Home → Paste ▼ → Paste Special → “Values” or “Formulas”
Key point: Because only values or formulas are pasted, column widths remain unchanged.
Avoid mistakes: Do not copy an entire column by clicking its column header. Copy only the data range.

2) Paste Formats Only (Excluding Column Widths)

Keyboard shortcut: Ctrl + Alt + V → T (Formats) → Enter
Ribbon: Home → Paste ▼ → Paste Special → “Formats”
Key point: This transfers cell fonts, borders, fills, and more, but does not transfer column width settings.
Use case: Useful when you want to improve the appearance without disrupting the layout.

Quick alternative: Format Painter — Home → Format Painter (paintbrush icon) → drag over the target range. It does not copy column widths, so it is safe.

Why Do Column Widths Change? (Understanding the Basics)

Copying Entire Columns vs. Copying a Range

Copying entire columns: If you click column headers (A, B, C) and copy, column properties, including widths, are more likely to be transferred.
Copying a range: If you copy only a cell range, such as A2:D100, column widths are generally unaffected.

The “Keep Source Column Widths” Option

When pasting between files or worksheets, selecting “Keep Source Column Widths” in the Paste Options button applies the source widths to the destination. Since you want to preserve the destination’s existing column widths, the key is not to use this option.

Solutions by Work Scenario

Scenario A: Importing Data from Another File While Keeping Destination Column Widths

When copying: Do not click column headers; drag to copy only the data range (Ctrl+C).
When pasting: Click the destination’s top-left cell → Ctrl+Alt+V → V (Values) or F (Formulas) → Enter.
If needed, paste formats only separately: Ctrl+Alt+V → T (Formats) → Enter.
Do not select “Keep Source Column Widths” in the Paste Options button.

Scenario B: Importing Table Data Including Formatting Without Changing Only the Column Widths

Two-step strategy
1. First, paste values or formulas only.
2. Then, paste formats only, excluding column widths.
You can also quickly align colors and borders using Format Painter.

Scenario C: Repeated Large-Scale Work (Thousands of Rows) — Automate with a Macro

The macro below saves the destination column widths before pasting, then restores the original widths after the paste.

Sub PasteKeepDestColumnWidths()
    Dim ws As Worksheet
    Dim rngDest As Range
    Dim col As Range
    Dim colWidths() As Double
    Dim i As Long, firstCol As Long, lastCol As Long

    ' 1) Define the destination range: record widths starting from the top-left of the selection
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select the destination range first.", vbExclamation
        Exit Sub
    End If
    Set rngDest = Selection
    Set ws = rngDest.Worksheet

    firstCol = rngDest.Columns(1).Column
    lastCol = rngDest.Columns(rngDest.Columns.Count).Column

    ReDim colWidths(firstCol To lastCol)
    For i = firstCol To lastCol
        colWidths(i) = ws.Columns(i).ColumnWidth
    Next i

    ' 2) Paste values or formulas only (modify if needed)
    On Error Resume Next
    rngDest.PasteSpecial Paste:=xlPasteValues
    If Err.Number <> 0 Then
        ' If there are no values on the Clipboard, perform a regular paste and then remove formatting
        Err.Clear
        rngDest.PasteSpecial Paste:=xlPasteAll
        rngDest.ClearFormats
    End If
    On Error GoTo 0

    ' 3) Restore column widths
    For i = firstCol To lastCol
        ws.Columns(i).ColumnWidth = colWidths(i)
    Next i
End Sub

Practical Example (Reproducible Sample)

Source data (A1:D6):

Product Code	Product Name	Unit Price	Quantity
P-001	USB-C Cable	9,900	3
P-002	Mouse	19,800	2
P-003	Keyboard	45,000	1
P-004	Hub	29,900	4
P-005	Stand	24,900	2

Destination worksheet column widths (example): A=12, B=30, C=10, D=8

Drag to select source range A1:D6 → Ctrl+C
Click destination cell A1 → Ctrl+Alt+V → V → Enter
(Optional) Press Ctrl+Alt+V → T to apply formats only, or use Format Painter.
Confirm that the widths of columns A through D on the destination worksheet remain unchanged.

Alternative Methods / Precautions / Checklist

Do not copy entire columns: Copying by clicking a column header may also transfer column widths.
Check Paste Options: Do not select “Keep Source Column Widths.”
Apply formatting separately: Use the order Values/Formulas → Formats.
Use a macro for bulk work: Improves consistency and speed.

Troubleshooting

Issue	Cause	Solution
Column widths become wider or narrower after pasting	Copied entire columns or selected “Keep Source Column Widths”	Copy only the range + Ctrl+Alt+V → V/F, and check Paste Options
Formatting is lost	Values were pasted without subsequently applying formats	As a second step, paste formats only or use Format Painter
Numbers are pasted as text	The source is text	Data → Text to Columns (General), or multiply the values by 1
Column widths sometimes change	Inconsistent work methods	Standardize with a macro
Table headers are misaligned	The wrong starting cell was selected	Specify the top-left destination cell precisely

Wrap-Up & Related Posts

You can now quickly solve the problem of keeping Excel column widths when pasting. For day-to-day work, the safest approach is the two-step strategy of pasting values/formulas only → pasting formats only, plus macro standardization for large-volume work.

FAQ — Keep Excel Column Widths When Pasting

Here are answers to frequently asked questions about keeping Excel column widths when pasting. Review how to paste values or formulas only, paste formats only, avoid Paste Options mistakes, and automate with macros.

Why do column widths change after I paste?

This usually happens because you copied entire columns by clicking their headers or selected “Keep Source Column Widths” in Paste Options. Copy only the data range and use Paste Values or Paste Formulas to preserve the destination worksheet’s existing column widths.

What is the fastest way to paste while keeping destination column widths?

Press Ctrl+Alt+V → V (Values) or F (Formulas), then press Enter. If necessary, apply T (Formats only) separately afterward. This safely keeps Excel column widths unchanged when pasting.

How can I keep formatting without changing only the column widths?

Use a two-step process: ① first, paste values or formulas only; ② then, paste formats only or use Format Painter. The column widths remain unchanged while only fonts, borders, and fills are applied.

Can I preserve column widths when copying between files?

Yes. Do not copy entire columns by their headers. Copy only the data range, then use Paste Values or Paste Formulas in the destination worksheet. The key is not to select “Keep Source Column Widths” in the Paste Options button.

How can I reduce column-width mistakes during large-volume work?

Standardizing with a macro is the most reliable option. VBA that saves destination column widths in an array before pasting and restores them afterward keeps column widths consistent even when people use different work methods.

IFS 함수란? IFS 함수 사용 예제

Keep Excel Column Widths When Pasting | The Safest Way to Preserve Column Sizes