Apr 07, 2015

I am often asked why it is that sometimes when you copy and paste a filtered or subtotaled range of cells in Microsoft Excel, they expand and paste even the hidden cells. It is because, by default, Excel copies hidden or filtered cells in addition to visible cells. If this is not what you want then you should try using visible cells only feature.

To use this feature, follow the steps below.

1. Select the cells that you want to copy in your Excel spreadsheet.
2. On the Home tab and in the Editing group, click Find & Select, and then click Go To Special.
3. In the dialogue box, click Visible cells only and then click OK. Alternatively, you can use the keyboard shortcut ALT+; or you can add the visible cells icon to your Quick Access Toolbar.
4. Copy your selection. You can do this by going to the Home tab and clicking Copy in the Clipboard group. Alternatively, you can use the keyboard shortcut CTRL+C.
5. Once copied, paste into your preferred area. You can do this by going to the Home tab and clicking Paste in the Clipboard group. Alternatively, you can use the keyboard shortcut CTRL+V.

Figure 2: Comparison of standard copy and paste (left) and visible cells only copy and paste (right).

For more information, take a look at New Horizons' Microsoft Excel training courses.