Skip to main content

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

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 - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with 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 - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

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


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download 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.

Get a 30-day full-featured free trial now!

ad sum count average visible cells only

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description

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!
Comments (7)
No ratings yet. Be the first to rate!
Kamil
Kamil
This comment was minimized by the moderator on the site
Kutools you are amazing, around 2 weeks I am using Kutools and it was fixed a lot of issues
You deserve your fee
Reply
0
0
mahmoudsaber.transportst@gmail.com
This comment was minimized by the moderator on the site
Thank you so much
its help me in work
Reply
Report
0
0
Brasil
Brasil
This comment was minimized by the moderator on the site
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?
Reply
Report
0
0
suresh2cctns
suresh2cctns
This comment was minimized by the moderator on the site
Copy and paste in filtered mode (Alt + ; and Ctrl + Enter). Very Useful. Thanks a lot.
Reply
0
0
Abdul
Abdul
This comment was minimized by the moderator on the site
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
Reply
0
0
ZUZU
ZUZU
This comment was minimized by the moderator on the site
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
Reply
0
0
NIRMAL SHRESTHA
NIRMAL SHRESTHA
This comment was minimized by the moderator on the site
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.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations