We can use the “Find & Select” function in Excel to copy only displayed cells after filtering. The same process can also avoid copying cells in the hidden columns / rows. For example, the below data set is filtered to only showing the “Jacket” and “Shoes” under the “Type” field. I want to copy only the displayed data to another worksheet.
Step 1: Highlight the area of data that you want to fill the data.
Step 2: Click the “Find & Select” icon and choose “Go To…” command. If you know the shortcut key (CTRL+G), you can just use the shortcut key.
Step 3; In the “Go To” pop up screen, click the “Special” button.
Please note that in the newer versions of Excel, you can instead pick “Go to Special” in Step 2 to bypass Step 3.
Step 4: Mark the radio button of “Visible cells only” in the “Go To Special” screen and click “OK” button.
Now only visibie cells are selected and you can proceed with the normal copy and paste action.