Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 data into visible cells only ignoring any hidden or filtered rows/columns in Excel

Values are pasted into hidden rows or columns? Require to paste data into only visible cells? Kutools for Excel's Paste to Visible Range feature will solve this problem and paste data into visible cells only in Excel. Full Feature Free Trial 60-day!
ad paste into visible range


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

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft 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.

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


Demo: paste skipping hidden/filtered cells and rows in Excel

 

In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!


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 60-day!
ad sum count average visible cells only


Related articles:


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ivo · 3 months ago
    Hello,

    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.
    suresh2cctns · 1 years ago
    Copy and paste in filtered mode (Alt + ; and Ctrl + Enter). Very Useful. Thanks a lot.
  • To post as a guest, your comment is unpublished.
    Abdul · 1 years ago
    Steps 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 500
  • To post as a guest, your comment is unpublished.
    ZUZU · 3 years ago
    Hi! 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.
    NIRMAL SHRESTHA · 3 years ago
    I 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.