How to Copy Filtered Excel Data Keeping Formulas and Formatting
Question details
The user needs to copy a filtered dataset in a spreadsheet and paste it into a new location without losing the original formulas and visual formatting.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Moving or duplicating specific filtered rows to another worksheet or workbook while retaining all original data attributes.
- Observed behavior
- Standard copying and pasting of filtered rows typically preserves the values and visual formatting but converts the underlying formulas into static values.
Ensure your data is properly filtered and that the destination worksheet has enough empty rows to accommodate the pasted data without overwriting existing content.
Use the Two-Step Paste Method to Keep Formulas and Formatting
This approach uses a normal paste command to transfer values and formats, followed immediately by a 'Paste Formulas' command to restore the underlying calculations.
When you copy filtered data, most spreadsheet applications default to pasting only the visible values and formats to avoid referencing errors from hidden rows. By splitting the action into two specific paste operations on the same destination cells, you bypass this limitation and retain everything.
Select the filtered rows or columns you wish to copy. Right-click the selection and choose 'Copy', or press 'Ctrl + C' on your keyboard.
Navigate to the destination cell in your new worksheet. Press 'Ctrl + V' (or right-click and select 'Paste'). This transfers the visual formatting and static values of the visible cells.
With the original filtered data still saved to your clipboard, right-click the exact same destination cell. Navigate to 'Paste Special' and select 'Formulas' (often represented by an 'f(x)' icon). This step overwrites the static values with the original formulas while leaving the previously pasted formatting intact.

Manage Filtered Data Easily with WPS Spreadsheet
WPS Office provides robust spreadsheet tools that handle complex data filtering, copying, and special pasting flawlessly. You can seamlessly manage formulas and cell formats across different sheets using intuitive tools.
- 1. Open Your Spreadsheet: Launch WPS Spreadsheet and open your .xlsx file. Apply your desired data filters.
- 2. Copy Visible Cells: Highlight the filtered rows and copy them by pressing 'Ctrl + C'.
- 3. Use Paste Special: Go to the destination sheet, right-click the target cell, and hover over 'Paste Special'.
- 4. Select Desired Attributes: Choose 'Formulas' to paste the calculations, and utilize 'Keep Source Formatting' if you want a one-click visual transfer.

Frequently Asked Questions
Why does copying filtered data normally remove my formulas?
When you copy a filtered range, spreadsheets often default to pasting only the visible values and formats to prevent referencing errors caused by the hidden rows. Using 'Paste Special' overrides this default behavior.
Can I use a keyboard shortcut to paste formulas?
Yes, after pasting the data normally with Ctrl + V, you can open the Paste Special dialog box by pressing Ctrl + Alt + V, selecting 'Formulas', and hitting Enter.
How do I ensure I only select visible cells before copying?
You can press 'Alt + ;' (semicolon) on your keyboard to explicitly highlight only the visible cells in your current selection, ignoring any hidden rows, before pressing 'Ctrl + C'.
Will cell references in the formulas adjust automatically when pasted?
Yes, relative cell references will automatically adjust based on the new paste location. If you need the formulas to point to the exact same original cells, you must use absolute references (with $ signs, e.g., $A$1) in your original data.




