WPS Spreadsheet: Copy an Excel Table While Preserving Formatting
Question details
The user needs to copy an Excel table or cell range to another location without losing its values, number formats, and cell formatting.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is transferring a table or specially formatted cell range and wants the destination to match the source.
- Observed behavior
- The values may copy successfully, but the original number formats or cell formatting do not transfer correctly.
Check whether the source is an Excel table or a normal cell range, and confirm that the destination has enough empty rows and columns for the copied content. Select the entire source range, including any headers or formatting that must be preserved.
Use Paste Special twice to preserve values and formatting
Use this method when the destination needs the source values, number formats, and cell formatting but should not inherit every table property.
Paste the data and formatting in two stages without copying the source again. First transfer the values and number formats, then apply the source formatting to the same destination range.
In WPS Spreadsheet, select the complete table or cell range, then choose Home > Copy or press Ctrl+C.
Select the destination cell, open Home > Paste > Paste Special, choose Values and Number Formats, and click OK.
Keep the source selection copied and select the same destination range. Open Home > Paste > Paste Special, choose Formats, and click OK without using Copy again.
Duplicate the worksheet to retain the complete table layout
Use this option when the table is on its own worksheet and you want to preserve table styles, widths, formulas, and sheet-level formatting together.
Use Format Painter after copying the table values
Use this quick method when the values are already in the destination and you only need to match the source cell formatting.
Copy Excel tables with their formatting in WPS Spreadsheet
WPS Spreadsheet provides Paste Special, Format Painter, and worksheet-copy tools to move Excel tables while retaining values, number formats, borders, fills, and other cell styles. It also supports familiar Excel workflows for smooth file migration.
- 1. Open the workbook: Launch WPS Office, select Spreadsheet, and open the XLS or XLSX workbook containing the table.
- 2. Select and copy the table: Highlight the source table or cell range and choose Home > Copy.
- 3. Open Paste Special: Select the destination cell, choose Home > Paste > Paste Special, and apply Values and Number Formats.
- 4. Apply the source formatting: With the source still copied, select the destination range and choose Home > Paste > Paste Special > Formats.
Frequently Asked Questions
Why does my copied Excel table lose its formatting in WPS Spreadsheet?
A standard Paste operation may transfer the values and formulas without all source styles. Use Home > Paste > Paste Special and select Formats, or use the two-stage Values and Number Formats followed by Formats method.
Can I copy only the formatting without replacing the destination values?
Yes. Select the original range, choose Home > Format Painter, and drag across the destination range. You can also use Paste Special > Formats to apply styles without replacing the destination data.
What is the best way to copy a complete table to another worksheet?
If the table has its own worksheet, right-click the worksheet tab, choose Move or Copy Sheet, enable Create a copy, and click OK. This preserves the sheet layout and table formatting more completely than copying cells alone.
Do I need to copy the table again before using Paste Special > Formats?
No. After copying the source, first use Paste Special > Values and Number Formats, then select the destination and use Paste Special > Formats without repeating the Copy command.
Will these methods preserve Excel table features such as filters?
Duplicating the worksheet is the safest option for retaining the complete table structure, including table styles and filters. Paste Special mainly transfers cell content or formatting and may not reproduce every structured-table property.
