Copying only the visible cells in Excel 2013

 Oct 03, 2014

Hiding rows and columns in Microsoft Excel is quite commonly done when filtering data, collapsing a sub-totalled list, or where you just don’t need or want to see certain parts of the worksheet.

When copying a range that includes hidden rows or columns (with the exception of filtered lists), the hidden data gets copied along with the visible. To get around this, there is a feature in Excel to select only the visible cells in a range.

Using the 'Go To Special' method

One way to access this command is via the 'Go To Special' dialogue box:

  1. Select the range as normal.
  2. From the 'Home' tab, choose 'Find and Select,' and then 'Go To Special.'
  3. Click the 'Visible Cells Only' option and OK.

Shortcut methods

There are two shortcuts that can be used to choose this command. One is a keyboard shortcut – Alt+;

  1. Select the range as normal.
  2. Press Alt+;

The other is a button that can be added to the Quick Access Toolbar, called 'Select Visible Cells.' This command is in the category 'Commands Not in the Ribbon.' To add it, complete the following:

  1. Choose 'File' and then 'Options.'
  2. Click the 'Quick Access Toolbar' category.
  3. Click the drop-down arrow for 'Choose commands from:' and scroll down to find 'Select Visible Cells.'
  4. Click Add and OK.

For more time-saving tips, be sure to take a look at New Horizons' various Excel 2013 training courses courses.

How do your Excel skills stack up?   

Test Now  

How do your Excel skills stack up?   

Test Now  

About the Author:

Ben Kirk  

With over 16 years of experience working as a Desktop Applications specialist for a number of large education services providers, Ben is one of New Horizons most skilled and dynamic instructors. With his Advanced Diploma of Business Skills alongside his practical experience and expertise, Ben is able to provide insight and guidance to students at all skill levels across the entire Microsoft Office suite.

Read full bio