Excel tries to help users in all spheres of work on the spreadsheet, but some of the help it offers may not be wanted at the time.
For instance, if you try to paste data into a visible cell (a filtered cell), excel will also paste the data into the hidden cells (filtered-out cells). Excel thinks the action is very helpful to you! But we know that it is very hurtful. Excel does not allow users to paste values directly into visible cells only.
This action of excel has become a serious issue for many excel users. However, they can get out of this situation by using the "Paste into excel visible fields" or "paste into excel visible fields only with code."
New versions such as Excel 2013 can use the Flash fill function while the older versions can use the Fill function.
Method 1 (Fill only)
Let's take a look at the picture below. There are three columns, and we want to filter the table so that it displays odd numbers only.
1. Open the filtered table.
2. Identify and select the columns and cells you wish to work on.
3. Navigate to the home tab (ribbon) and click "fill" and "fill right." If your target is the columns by your left, then go ahead and click "fill left instead."
Viola! All the desired values will appear in the visible cells.
Meanwhile, you can click the "clear the filter" condition on your cells to be sure that nothing is copied into hidden rows.
Method 2 (Flash fill)
Users of a newer version of Excel can use the Flash fill function to quickly paste into visible cells only.
For example, you can filter odd numbers by using the steps below:
1. Identify the first cell and type '1' in the destination column.
2. Move to the next cell down and type '9'.
3. Proceed by pressing 'Enter' and then 'Ctrl + E'.
This will kick start the Flash fill function. The visible cells will be copied while the hidden rows will not be touched. You can confirm this action if you clear out the filter condition.