'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Copy Only Visible Cells and Unhide Rows in Excel
Question details
The user wants to know how to prevent hidden or filtered rows from being copied, or how to properly unhide them after copying and pasting data in an Excel worksheet.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Copying a range of data that includes hidden or filtered rows and pasting it into a new destination.
- Observed behavior
- Excel copies and pastes both the visible and the hidden/filtered cells by default, causing unwanted hidden data to appear in the destination area.
Before copying your data, verify whether the rows in your source range are hidden manually or minimized through an active filter, as this helps you confirm exactly which data you wish to exclude.
Use the "Select Visible Cells Only" Command Before Copying
The most effective way to avoid pasting hidden rows is to force Excel to select only the data that is currently visible on your screen.
By default, standard copy operations grab everything in the highlighted range, including rows you cannot see. By using the 'Go To Special' menu, you can isolate the visible cells and leave the hidden ones behind.
Click and drag your mouse to highlight the entire area of data you want to copy, encompassing both the visible and hidden rows.
Navigate to the 'Home' tab, click on 'Find & Select' in the Editing group, and choose 'Go To Special...'. Alternatively, press F5 on your keyboard and click the 'Special...' button.
In the Go To Special dialog box, select the radio button for 'Visible cells only' and click 'OK'. You will notice a subtle border shift highlighting only the non-hidden rows.
Press Ctrl + C to copy the visible cells. A moving dashed border will appear around the visible sections. Navigate to your destination cell and press Ctrl + V to paste.
Unhide Rows Manually After Pasting
If you have already pasted the data and the hidden rows carried over, you can manually reveal them in the new location.
Copy Visible Cells Easily with WPS Spreadsheet
WPS Spreadsheet offers an intuitive interface to manage hidden rows, apply filters, and copy only visible cells with a simple shortcut. It handles complex data seamlessly, making your daily spreadsheet tasks fast and error-free.
- 1. Highlight the data: Open your workbook in WPS Spreadsheet and select the range containing the visible data and hidden rows.
- 2. Select visible cells only: Press Alt + ; (semicolon) to automatically exclude the hidden rows from your selection.
- 3. Copy the data: Press Ctrl + C to copy the selected visible cells to your clipboard.
- 4. Paste to destination: Click on the destination cell where you want the data to appear and press Ctrl + V.
Frequently Asked Questions
Why does Excel copy hidden rows by default?
By default, Excel treats a selected range as a continuous block of data. It copies everything within the starting and ending boundaries of your selection, including rows or columns that have been manually hidden or filtered out.
What is the shortcut to select visible cells only in Excel?
After highlighting your data range, press Alt + ; (semicolon) on Windows, or Cmd + Shift + Z on Mac. This instantly selects only the visible cells, ignoring any hidden rows or columns.
How do I know if I have successfully selected only the visible cells?
Once you apply the 'Visible cells only' command, you will see a moving dashed border (sometimes called a 'marquee') wrapping around the distinct visible sections individually, rather than one large rectangle around the entire range.
Can I copy visible cells across multiple worksheets at once?
No, you must select and copy visible cells from one sheet at a time. Selecting visible cells across grouped sheets is not supported for standard copy-and-paste operations.
