Home: Microsoft Excel: Excel: VBA - Filter data, copy only visible cells after filter and move onto next filter if no data. We find ourselves in a situation where we need to copy data from only visible cells in excel numerous times. If you are working on a large database you gotta. Learn 4 ways to copy and paste only the visible cells in Excel.Use the keyboard shortcut Alt +;Use the quick access commandUse the Go To Special commandUse.
Copy and Paste Visible Cells Only (filtered data)
You have used one of several methods to hide some rows for filtered data, or created a table which auto applies filter icons for each column. Now you want to copy and paste just the visible data but discovered to your horror when you pasted to another location, it included the hidden rows!
By default, Excel copies hidden or filtered cells in addition to visible cells. If you want only visible rows, here’s the steps:
- Select the cell range that you want to copy.
- Click Home tab, Find & Select in the Editing group and choose Go To Special…
- Click Special… button in the dialog box.
- Click Visible cells only radio button and click OK.
- Click Copy in Clipboard group on Home tab (or press CTRL+C).
- Click the upper-left cell of the desired paste area and click Paste (or press CTRL+V).
You have now achieved Nirvana!
Tip: You can also use the F5 Function key at Step 2 to bring up the Go To… dialog box and click the Special… button to get the same results.
Add the Icon for Select Visible Cells to the Quick Access Toolbar
Make this great solution even easier and faster by utilizing the Quick Access Toolbar (QAT):
- Right click anywhere in the Ribbon and choose Customize Quick Access Toolbar… OR click the QAT dropdown arrow, and choose More Commands.
- From the Choose Commands From dropdown, choose All Commands.
- Scroll down and click Select Visible Cells.
- Click Add and then click OK to add to end of the QAT.
- If desired, use the arrow boxes to change the icon’s position on the toolbar.
Now all you do is select the range, click the Select Visible Cells icon on the QAT, Copy and Paste – One and done!
Copy Visible Cells In Microsoft Excel
Copy and Paste Only Subtotaled Rows
You’ve used the SUBTOTAL function to sum only filtered data and now want to copy and paste to another location. You assume the paste will include the visible subtotaled rows only – Surprise – not! You still need to use the Go To dialog box to accomplish this but if this is something you do often, apply shortcuts:
- Select the range you want to copy. (Excel is actually selecting the hidden rows as well but this will get taken care of in the next steps).
- Press F5 function key to display Go To dialog box.
- Click the Special… button at the bottom of the dialog box.
- Click Visible Cells Only to select only the visible cells in the selected range.
- Click OK (or just hit ENTER key as OK is already selected).
- Press CTRL C to copy the selected visible cells to the Clipboard.
- Select a destination cell (can be on the same sheet, a different sheet, or on a new workbook).
- Paste the range by pressing CTRL V. Excel copies only the subtotaled rows.
Now you can copy and paste only those cells or ranges YOU want.
Hope these tips have removed a little head-scratching from one of those features that we want to use on a regular basis but doesn’t always behave the way we expect!
Quickly copy data and paste into only visible cells / filtered list in Excel
Kutools for Excel
In Excel, when going to copy and paste some data into a range which has been filtered or contains hidden rows / columns, it will paste to those filtered or hidden cells too. Can we just paste data into only visible cells in Excel? Kutools for Excel's Paste to Visible will help you get it done quickly.
Paste To Visible Range Demo
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Click Kutools > Range > Paste to Visible > All / Only Paste Values. See screenshot:
Copy data and paste into filtered list only
If a range with enabling filtering feature as shown in following screenshot, and you need to copy some data and paste it into a range with enabling filtering feature but only the visible cells, you can solve this task as follows:
1. Select the data range that you want to paste to the filtered list.
Pasting all values with cell formatting into visible cells:
2. If you want to paste all values with cell formatting into visible cells, please click Kutools > Range > Paste to Visible > All. See screenshot:
Pasting only values into visible cells:
For pasting only values of selected cells into visible cells, please click Kutools > Range > Paste to Visible > Only Paste Values. See screenshot:
3. In the Paste to Visible Range prompt box, select a cell or a range that you want to paste the selected data with all cell formatting, and then click the OK button. See screenshot:
Then you can see the selected data has been pasted into the filtered rows only. See screenshots:
Paste all with cell formatting into visible cells:
Paste only values into visible cells:
Copy data and paste into visible cells only when there are hidden rows
Supposing, there are multiple hidden rows in your worksheet, to paste some data into the visible rows only, you can also use this Paste to Visible Range utility.
1. Select the data range that you want to paste into the visible rows only.
Pasting all values with cell formatting into visible rows:
2. If you want to paste all values with cell formatting into visible rows, please apply this utility by clicking Kutools > Range > Paste to Visible > All. See screenshot:
Pasting only values into visible rows:
For pasting only values of selected cells into visible rows, please click Kutools > Range > Paste to Visible > Only Paste Values. See screenshot:
3. In the Paste to Visible Range prompt box, select a cell or a range that you want to paste the selected data, and then click the OK button. See screenshot
Then you can see the selected data has been pasted into the visible rows only as below screenshots shown:
Paste all with cell formatting into visible rows:
Paste only values into visible rows:
Notes:
1. This utility supports undo (Ctrl + Z).
2. If there are both filtered rows and hidden rows in your worksheet, this feature also can help you to paste the selected data into the visible cells only successfully.
Demo: copy data and paste into only visible cells / filtered list in Excel
Kutools for Excel: with more than 300 handy features, free to try with no limitation in 30 days. Download and free trial Now!
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.
How To Copy Only Visible Cells In Filtered Excel
Kutools for Excel
The functionality described above is just one of 300 powerful functions of Kutools for Excel.
Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.