You deserve your fee
How to paste skipping hidden/filtered cells and rows in Excel?
For example, you have filtered a table in Excel, but now you need to copy a range and paste into this filtered table, do you know how to paste skipping the hidden/filtered cells and rows? Several easy ways can help you.
- 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.
If the range you will paste to has the same filter as copied range, for example you will copy the filtered prices and pasted into filtered Column G, you can easily paste with skipping the filtered cells and rows by only selecting the visible cells then using formula. And you can do as follows:
1. Select the filtered Range G3:G24, and press Alt + ; at the same time to select only visible cells.
2. In the formula bar type =C3 (C3 is the first cell with filtered price), and press the Ctrl + Enter key simultaneously to fill all selected cells. Then you will see the filtered prices are pasted to the specified range with ignoring the hidden cells and rows.
In most cases, the range you will paste to has been filtered, but the copied range has not been filtered, or they contain different filters. Therefore, the first method can’t help you to solve it. Here I recommend the Paste to Visible Range utility of Kutools for Excel.
1. Select the range you will copy, and then click Kutools > Range > Paste to Visible Range.
Note: You can also click the Enterprise > Paste to Visible Range.
2. In the coming Paste to Visible Range dialog box, select the cell where you will paste skipping filtered cells and rows, and click the OK button.
Then you will see copied data are only pasted to the specified filtered range as the below two screen shots shown.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 6 months agoKutools you are amazing, around 2 weeks I am using Kutools and it was fixed a lot of issues
You deserve your fee
- To post as a guest, your comment is unpublished.· 7 months agoThank you so much
its help me in work
- To post as a guest, your comment is unpublished.· 1 years agoHello,
The "Paste to Visible range" function doesn't work for me either. I see only blank cells and nothing pasted. I'm using Excel 2016 on Windows 10. Could you please advise?
- To post as a guest, your comment is unpublished.· 2 years agoCopy and paste in filtered mode (Alt + ; and Ctrl + Enter). Very Useful. Thanks a lot.
- To post as a guest, your comment is unpublished.· 2 years agoSteps to easily paste on visible cells only in excel without any macro or additional software is as follows:
Suppose original data is:
You have hidden two rows for E and N, now the visible data is:
You want to paste 100 for A, 300 for G and 500 for K to get the following results:
The procedure to get this result easily is given below:
1. Copy the visible cells from the excel sheet
2. Paste the copied cells in a new excel sheet. You will get:
3. Paste the data you want to paste, in new excel sheet. You will get
4. Use VLOOKUP formula in your original excel file on visible cells, with reference to data in new excel file. Then remove the filter from original file to see all hidden cells. You will get the following results:
- To post as a guest, your comment is unpublished.· 4 years agoHi! I've tried the paste to visible range function and it doesn't work for me at all. I followed the steps above, but all I get are blank cells. Anyone knows what's wrong? Could use some help here. Thanks! :D
- To post as a guest, your comment is unpublished.· 4 years agoI Tried using
Paste skipping hidden/filtered cells and rows with Kutools for Excel
I downloaded your Kutools and installed but while doing it is not working.