Copy visible cells only in Excel

 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 1: Subtotal data

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.

How do your Excel skills stack up?   

Test Now  

About the Author:

Alice Antonsen  

Alice is one of New Horizons’ most experienced Desktop Applications trainers. She is qualified with a Diploma of Business (Computing) and a Diploma of Information Technology. Since joining New Horizons in 2006, Alice has achieved the prestigious status of a certified Microsoft Office Master and for the past 4 years, has been placed in the Top 25 Desktop Applications trainers for New Horizons Worldwide. Throughout her career, she has gained a wealth of knowledge and experience in providing training to individuals and groups of varying skill levels. Alice delivers each training session with great care and consideration ensuring each one is tailored to the learning needs of her students.

Read full bio