How do I copy and paste after Filter in Excel?

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,

How do I copy and paste after Filter in Excel?

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

Paste skipping hidden/filtered cells and rows with only selecting visible cells

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.


Paste skipping hidden/filtered cells and rows with Kutools for Excel

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 cant help you to solve it. Here I recommend the Paste to Visible Range utility of Kutools for Excel.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

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.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


Demo: paste skipping hidden/filtered cells and rows in Excel
Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!


Sum/Count/Average visible cells only in a specified range with ignoring hidden or filtered cells/rows/columns

The normally SUM/Count/Average function will count all cells in the specified range on matter cells are hidden/filtered or not. While the Subtotal function can only sum/count/average with ignoring hidden rows. However, Kutools for Excel SUMVISIBLE/COUNTVISIBLE/AVERAGEVISIBLE functions will easily calculate the specified range with ignoring any hidden cells, rows, or columns. Full Feature Free Trial 30-day!

ad sum count average visible cells only

Paste data to visible or filtered cells only


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, chartsand anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Read More... Free Download... Purchase...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom

Read More... Free Download... Purchase...Oldest FirstSort comments byOldest FirstNewest FirstComments (7)No ratings yet. Be the first to rate!

NIRMAL SHRESTHA

NIRMAL SHRESTHAabout 6 years ago#15008This comment was minimized by the moderator on the siteI 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.Reply

ZUZU

ZUZUabout 6 years ago#15873This comment was minimized by the moderator on the siteHi! 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! :DReply

Abdul

Abdulabout 5 years ago#20830This comment was minimized by the moderator on the siteSteps to easily paste on visible cells only in excel without any macro or additional software is as follows:
Suppose original data is:
A
E 200
G
N 400
K

You have hidden two rows for E and N, now the visible data is:
A
G
K

You want to paste 100 for A, 300 for G and 500 for K to get the following results:
A 100
E 200

G 300
N 400

K 500

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:
A
G
K

3. Paste the data you want to paste, in new excel sheet. You will get
A 100
G 300
K 500

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:
A 100
E 200
G 300
N 400
K 500Reply

suresh2cctns

suresh2cctnsabout 4 years ago#24197This comment was minimized by the moderator on the siteCopy and paste in filtered mode (Alt + ; and Ctrl + Enter). Very Useful. Thanks a lot.Reply

Ivo

Ivoabout 3 years ago#28075This comment was minimized by the moderator on the siteHello,

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?ReplyReport

mahmoud saber

mahmoud saberabout 2 years ago#30682This comment was minimized by the moderator on the siteThank you so much
its help me in workReplyReport

Kamil

Kamilabout 2 years ago#31001This comment was minimized by the moderator on the siteKutools you are amazing, around 2 weeks I am using Kutools and it was fixed a lot of issues
You deserve your feeReplyThere are no comments posted here yetLeave your comments

Guest

Posting as GuestLogin Username PasswordLogin to my account     Name (Required) Email (Required)×Rate this post:Reset0  CharactersSuggested Locations   I agree to the terms and conditionCancel Submit Comment

Video liên quan