How to Copy Excel Values and Conditional Formatting in Filtered Data in WPS Office
Question details
The user needs to copy values, formatting, and conditional formatting rules from a filtered Excel range to another range while controlling whether hidden rows are included.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working in a filtered worksheet and wants to transfer both data and formatting, including conditional formatting rules, to another range or sheet.
- Observed behavior
- Selecting and pasting filtered data can include hidden rows or lose conditional formatting unless the correct paste and selection options are used.
Make sure the source worksheet has the filter applied and the conditional formatting rules are visible under Home > Conditional Formatting > Manage Rules. Decide whether hidden rows should be copied or excluded before selecting the range.
Copy Visible Filtered Cells with Values and Source Formatting
Use this when you want to copy only the rows currently visible after filtering, preserving values and cell formatting.
When a filter is active, a normal copy includes hidden rows. WPS Spreadsheets lets you select visible cells only before copying so the pasted range contains exactly what you see.
If you need to paste formats and values separately, use Paste Special and choose Formats first, then Values.
Click and drag to select the filtered range in your WPS Spreadsheets worksheet.
Go to Home > Find & Select > Go To Special, choose Visible cells only, and click OK.
Press Ctrl+C or click Home > Copy.
Select the destination cell, then click Home > Paste > Keep Source Formatting to retain values and formatting. For separate control, use Home > Paste > Paste Special.
Copy Conditional Formatting Rules to Another Range with Manage Rules
Use this when you need to apply the same conditional formatting logic to a different range without copying cell values.
Use Format Painter to Copy Values, Formatting, and Conditional Formatting Rules
Use this when the source and destination ranges are similar in size and you want a quick way to transfer formatting together with conditional formatting rules.
Copy Filtered Excel Data and Conditional Formatting Directly in WPS Office Spreadsheets
Use WPS Office Spreadsheets to copy visible filtered cells, paste values with source formatting, and manage conditional formatting rules in one familiar interface without switching to Microsoft Excel.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, open the .xlsx file, and apply your filter from Data > AutoFilter.
- 2. Select visible cells and copy: Use Home > Find & Select > Go To Special > Visible cells only, then press Ctrl+C.
- 3. Paste to the destination: Click the target cell and choose Home > Paste > Keep Source Formatting.
- 4. Reuse conditional formatting: Open Home > Conditional Formatting > Manage Rules and adjust the Applies to range for the destination.
Frequently Asked Questions
How do I copy only visible rows from a filtered range in WPS Spreadsheets?
Select the range, go to Home > Find & Select > Go To Special, choose Visible cells only, click OK, and then copy. Paste with Keep Source Formatting to retain values and formatting.
Why does pasting filtered data include hidden rows?
A normal copy includes hidden rows. Use Go To Special > Visible cells only before copying to exclude them, or clear the filter before copying to include all rows.
Can I copy conditional formatting rules without copying the cell values?
Yes. Use Home > Conditional Formatting > Manage Rules, choose the rule, and edit the Applies to range to include the destination range.
Does Format Painter copy conditional formatting rules?
Yes. Format Painter copies conditional formatting rules along with standard formatting from the selected source range to the destination range.
Which paste option preserves Excel source formatting in WPS Office?
Use Home > Paste > Keep Source Formatting. It retains values, number formats, colors, and most cell formatting from the source range.
