Why Excel Paste Special Copies Formatting with Column Widths and How to Fix It
Question details
The user wants to understand why pasting data using the 'Keep Source Column Widths' option in Excel also brings over original fill and border formats, which prevents new table styles from displaying correctly.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Pasting data from an existing table using the 'Keep Source Column Widths' Paste Special option and then attempting to apply a new table style to the pasted data.
- Observed behavior
- The source fill and border formatting are preserved during the paste and remain visibly dominant, overriding the newly applied table style.
Before adjusting your data, determine whether you strictly need to retain the original column widths or if a simple values-only paste followed by manual column resizing would be more efficient for your formatting needs.
Manually Clear Unwanted Formatting
Since Excel's default behavior carries over the source formatting, clearing the existing formats manually allows your new table styles to apply correctly.
Excel considers fill and border settings as part of the source formatting preserved by the 'Keep Source Column Widths' option. Because explicit cell formatting overrides standard table styles, the pasted cells will ignore your new table style until the original formatting is cleared.
Highlight the range of cells where the original formatting is blocking your new table style.
Navigate to the Home tab on the Excel ribbon, click the Clear button (the eraser icon) in the Editing group, and select 'Clear Formats'.
Once the manual formatting is cleared, apply your desired table style from the Table Design tab or the 'Format as Table' menu.
Paste Values First, Then Adjust Column Widths
Avoid copying the source formatting entirely by separating the paste process into two distinct steps: pasting the raw values first, and then pasting the column widths.
Try WPS Office for Seamless Spreadsheet Formatting
If Microsoft Excel's default pasting behaviors and formatting quirks are slowing down your work, consider switching to WPS Office. As a free, lightweight, and highly compatible alternative, WPS Spreadsheet offers intuitive formatting controls and a familiar user interface, making data migration and styling effortless.
- 1. Download and Install WPS Office: Visit the official WPS website, download the free installation package, and follow the lightweight setup process.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and seamlessly open your existing Microsoft Excel documents.
- 3. Manage Formatting Effortlessly: Use intuitive Paste Special options to manage column widths and cell styling without disruptive formatting overlaps.
Frequently Asked Questions
Does Keep Source Column Widths always copy borders and fills?
Yes. In Excel, this specific Paste Special option is designed to preserve the source formatting, including cell colors and borders, alongside the column dimensions.
Why doesn't my new table style apply to pasted data in Excel?
When explicit formatting (like custom fills or borders) is present in a cell, it takes precedence over standard table styles. You must clear the original explicit formatting for the table style to become visible.
Can I copy only the column widths without the data or formatting?
Yes. Copy the source cells, right-click the destination, select Paste Special, choose 'Column widths', and click OK. This applies the width dimensions without pasting any data, colors, or borders.
