Fix Excel Table Losing Formulas and Formatting When Pasting Rows in WPS Spreadsheets
Question details
The user needs to paste report data into an Excel-compatible table without overwriting formulas in calculated columns or losing table formatting.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Copying rows from an Excel worksheet or SSRS report into a table that has data-entry columns and separate formula columns.
- Observed behavior
- Pasting data from Excel or an SSRS report overwrites formula cells in calculated columns with blank values, causing formulas and formatting to disappear.
Before pasting, identify the columns that contain formulas and the columns that are meant for pasted data. If possible, arrange the table so all data-entry columns are on the left and formula columns are on the right.
Paste Only the Report Data Columns and Keep Formula Columns Separate
Use this when you paste rows from Excel or an SSRS report into a table that has calculated formula columns. It prevents blank cells from overwriting formulas.
When blank cells from the source report are pasted into formula columns, they replace the existing formulas and can remove the calculated results or formatting. Keeping formula columns outside the paste range protects them.
Click the table in your worksheet and identify the formula columns, for example yellow calculated columns. Note which columns should receive pasted report data.
Select the formula column header, press Ctrl+X to cut it, right-click the destination column header to the right of the data columns, and choose Insert Cut Cells.
In the source Excel or SSRS report, select only the columns that should fill the table data columns, do not include blank columns that correspond to formula columns, and press Ctrl+C.
Click the first blank data cell under the table data columns, then press Ctrl+V or right-click and choose Paste. Do not select or paste into the formula columns.
Check that the calculated columns still show the expected results and that the table formatting has not been overwritten.
Use Paste Special Values in a Temporary Range Before Filling the Table
Use this when the source layout does not match the table, or when you need to clean up imported data before it enters the table.
Keep Table Formulas and Formatting Intact When Pasting Rows in WPS Spreadsheets
Use WPS Spreadsheets to restructure your table so data columns and formula columns are separated, then paste only the report data columns. This keeps calculated columns and formatting intact without losing Excel compatibility.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the affected .xlsx workbook containing the Excel table.
- 2. Identify and move formula columns: In the table, locate formula columns and move them to the right of all data-entry columns using cut and Insert Cut Cells.
- 3. Copy the report data columns only: Open the source Excel or SSRS report, select the required data columns only, and press Ctrl+C.
- 4. Paste into the table data columns: Return to WPS Spreadsheets, click the first blank data cell in the table, and press Ctrl+V. Avoid selecting formula columns.
- 5. Check the table: Confirm that formula columns still calculate and formatting remains applied to the pasted rows.
Frequently Asked Questions
Why does pasting rows into an Excel table remove formulas from calculated columns?
When the pasted range includes blank cells, those blanks overwrite the formula cells in calculated columns. WPS Spreadsheets keeps formulas intact if you paste only into data-entry columns and leave formula columns outside the paste range.
Can I paste an entire SSRS report into a WPS table without losing formulas?
Avoid pasting entire report ranges if they contain blank columns that match formula columns. Copy only the report data columns, or paste values into a temporary range first and then fill the table data columns manually.
How do I move a formula column to the right in WPS Spreadsheets?
Select the column header, press Ctrl+X, right-click the target column header to the right, and choose Insert Cut Cells. Then paste data only into the columns on the left.
Does Paste Special Values protect table formatting in WPS Spreadsheets?
Paste Special > Values pastes data without source formatting, which helps preserve the destination table formatting. However, you still need to paste only into data columns so that formula columns are not overwritten.
