How to copy only visible cells in Excel?
When you set some rows and columns to be hidden in a worksheet and want to copy the only visible cells in Excel, can you just directly copy and paste it in Excel? No, there are some tricky things you should know for doing so. Please go through the following steps to know how you could only copy the visible cells in Excel.
|Copy data and paste to visible cells only:|
|Kutools for Excel’s Paste to Visible Range feature can help you copy and paste a range of cell values to filtered or visible cells only quickly and easily. Read more about this feature...|
Recommended Productivity SoftwareOffice Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010, 2013 and 2016 (includes Office 365).
In Excel, you can apply the Go To Special function to select the visible rows first and then copy and paste them to other location.
1. Select the visible rows that you want to copy.
2. Click Home > Find & Select > Go To Special, see screenshot:
3. In the Go To Special dialog box, select the Visible cells only option under the Select, see screenshot:
4. Then click OK button, and the visible cells will be selected only.
5. Then you can copy them and paste them to anywhere without the hidden rows or columns.
If you need to delete only the visible rows and leave the hidden rows, you can apply the above steps to select the visible rows first, after selecting the visible rows then press Delete key to remove them at once.
But, here, I can also introduce you an easy and powerful tool – Kutools for Excel, with its Delete Hidden (Visible) Rows & Columns, you can quickly delete the hidden or visible rows or columns only in a worksheet or whole workbook.
|Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 60 days. Get it Now.|
After installing Kutools for Excel, please do as follows:( Free Download Kutools for Excel Now )
1. Select the visible rows that you want to delete.
2. Then click Kutools > Delete > Delete Hidden (Visible) Rows & Columns, see screenshot:
3. In the Delete Hidden (Visible) Rows & Columns dialog box, choose In Selected Range from the Look in drop down, then select Rows in the Delete type section, and choose Visible rows under the Detailed type section, see screenshot:
4. Then click OK button, all the visible rows have been deleted immediately but leaving the hidden rows.
With this feature, you can also delete blank rows / columns and hidden rows / columns in a range / a worksheet or a workbook as you need.
Is your problem solved?
Recommended Productivity Tools
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
More than 120 powerful advanced functions which designed for Excel:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...