Excel 2016: Paste Data into Visible Rows Without Overwriting Hidden Rows
Question details
The user needs to paste copied data into only the visible rows of a filtered Excel 2016 worksheet without changing hidden rows.
- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- Transferring copied cell values into a destination worksheet with an active filter.
- Observed behavior
- Pasting updates hidden destination rows as well as visible rows. The user reports that earlier pastes affected only visible rows, and the current behavior risks overwriting hidden data.
Check whether the source, destination, or both are filtered, and count the visible destination rows available for the copied data. Test the method on a duplicate worksheet, and verify that the destination columns are visible and contain no merged cells.
Paste different values into visible destination rows one row at a time
Use this method for a small transfer when each source row contains different values and hidden destination rows must remain unchanged.
Ordinary paste is not a reliable way to target only visible destination rows. Excel generally writes into a contiguous destination range, which can include rows hidden by a filter.
Selecting Visible cells only does not make every multi-row paste safe. A filtered selection can contain separate areas, and Excel may reject the paste or fail to map the copied rows as intended.
Keep the destination filter active and use the row numbers at the left of the worksheet to identify visible rows. Confirm that there are enough visible destination rows for the source rows you intend to transfer.
Select the cells from a single source row that you want to transfer, then choose Home > Copy. If the source is filtered, choose a visible source row.
Click the first destination cell in the next visible row. Choose Home > Paste > Paste Special > Values to transfer values without source formatting, or use ordinary Paste if formulas and formatting are required. Keep the copied range within that single destination row.
Repeat the copy-and-paste process for each remaining source row, always choosing the next visible destination row. Afterward, use Data > Clear to remove the filter and inspect the previously hidden rows to confirm that they were not changed.
Fill only visible cells with the same value
Use this method when every visible destination cell should receive one identical value rather than a different copied value.
Work with Filtered Excel Workbooks in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for easy migration. Use WPS Spreadsheet to work with filtered workbooks, but test paste behavior on a duplicate sheet; switching apps does not repair Excel 2016 or guarantee visible-only pasting.
Frequently Asked Questions
Why does Excel paste into rows hidden by a filter?
Filtering hides rows from view; it does not remove them from the worksheet. An ordinary paste can write into consecutive destination rows, including hidden ones, so the visible layout is not a safe indication of which cells will change.
Does selecting Visible cells only make a multi-row paste safe?
Not in every situation. Home > Find & Select > Go To Special > Visible cells only creates a selection that may contain separate areas. Excel may reject a multi-area paste or not map source rows to visible destination rows as expected. Use single-row pastes or a tested row-by-row VBA macro for different values.
How can I put the same value into all filtered rows?
Select the destination cells, excluding the header, then choose Home > Find & Select > Go To Special > Visible cells only. Type the value and press Ctrl+Enter to fill the selected visible cells.
What should a VBA macro check before copying filtered rows?
A suitable macro should obtain the visible source rows using SpecialCells(xlCellTypeVisible), prompt for the destination, skip hidden destination rows, and confirm there is enough destination space. It should also handle no visible records and avoid copying the header unintentionally. Test it on a duplicate worksheet before using it on the original.
What should I provide if I think this is an Excel 2016 defect?
Record the Excel build shown under File > Account > About Excel. Create a sample workbook without sensitive data and record the filter, source selection, destination selection, and paste result. Submit those reproduction details through your Microsoft support channel so the behavior can be investigated.
