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.