Paste ignoring hidden or filtered cells
How to paste values ignoring hidden or filtered cells using an Excel method
Select filtered range in which to paste > Select the Home tab > Editing group > Click Find & Select > Click Go to Special > Select Visible cells only > Click OK > Enter the first cell reference of data to copy > Press Ctrl + Enter keys simultaneously
| In this example we will be replacing the old stock with the revised stock only for Bread, Milk, Apples and Bananas. This image shows the starting point without any adjustments. |  |
| 1. Filter for the values that you want to replace. Note: in this example we are filtering for Bread, Milk, Apples and Bananas given we are replacing the old stock with the revised stock for only these four products. |
 |
| 2. Select the filtered range that you want to paste over. Note: in this example we are replacing the old stock values with the revised stock values, therefore, we have selected range (C3:C8). |
 |
| 3. Select the Home tab. |  |
| 4. Click Find & Select in the Editing group. 5. Click Go To Special. |
 |
| 6. Select Visible cells only in the Go To Special window. 7. Click OK |
 |
| 8. Enter the first cell reference of the data you want to copy by typing the = sign in the formula bar and selecting the relevant cell. Note: in this example we reference cell D3 given it is the cell that relates to the cell C3 that we want to copy over. |
 |
| 9. Press Ctrl + Enter keys simultaneously to copy over the remained of the selection. |  |
| When we clear the filter you can see that only the old stock for the filtered products was replaced, the rest remained unchanged. |  |
EXPLANATION
This tutorial explains and provides step by step instructions on how to paste values ignoring hidden or filtered cells using an Excel method.
This tutorial explains and provides step by step instructions on how to paste values ignoring hidden or filtered cells using an Excel method.
Excel Method: Using an Excel method you can paste values to visible cells only, ignoring the filtered or hidden cells. In this tutorial we are ignoring all filtered cells, however, the same principal would apply if we have hidden the cells instead of filtered cells.