Document Editing Problems

How to Copy Filtered Excel Data Keeping Formulas and Formatting

Phi Hung VoPhi Hung Vo Oct 29, 2026 872 views

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.

How to Copy Filtered Excel Data While Keeping Formulas and 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.
Before you start

Ensure your data is properly filtered and that the destination worksheet has enough empty rows to accommodate the pasted data without overwriting existing content.

Solution 1Recommended

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.

1
Copy the Filtered Range

Select the filtered rows or columns you wish to copy. Right-click the selection and choose 'Copy', or press 'Ctrl + C' on your keyboard.

2
Paste Normally for Formatting

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.

3
Paste Special for Formulas

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.

Use the Two-Step Paste Method to Keep Formulas and Formatting
Verification: Click on any of the pasted cells that originally contained calculations to verify that the formula bar displays the active equation rather than just the static number.
Manage Data Better

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. 1. Open Your Spreadsheet: Launch WPS Spreadsheet and open your .xlsx file. Apply your desired data filters.
  2. 2. Copy Visible Cells: Highlight the filtered rows and copy them by pressing 'Ctrl + C'.
  3. 3. Use Paste Special: Go to the destination sheet, right-click the target cell, and hover over 'Paste Special'.
  4. 4. Select Desired Attributes: Choose 'Formulas' to paste the calculations, and utilize 'Keep Source Formatting' if you want a one-click visual transfer.
Accurately copy and paste filtered data without losing formulas100% compatible with Microsoft Excel (.xlsx, .xls) formatsAdvanced 'Paste Special' options for precise data managementLightweight application with a familiar, easy-to-use interface
microsoft office alternative - wps office

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.