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.
Best Office Productivity Tools
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...
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!