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 30-day!
ad paste into visible range

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • 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.

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.

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


Related articles:


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

  • Reuse: Quickly insert complex formulas, charts and 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.
kte tab 201905

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
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.
    Kamil · 2 months ago
    Kutools 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.
    mahmoud saber · 3 months ago
    Thank you so much
    its help me in work
  • To post as a guest, your comment is unpublished.
    Ivo · 10 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 · 2 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 · 4 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.