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.
- Paste skipping hidden/filtered cells and rows with only selecting visible cells
- Paste skipping hidden/filtered cells and rows with Kutools for Excel
- Sum/Count/Average visible cells only
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 ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 years 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.· 1 years 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.· 3 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.· 5 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.