Select and Copy Only Visible Cells in Excel

To select visible cells only in Excel, select your range and press Alt+; on Windows or ⌘+Shift+Z on a Mac. Excel drops the hidden rows and columns from the selection, so when you copy and paste, only what you can see comes along. This guide shows the shortcut, the menu route, when Excel already does this for you, and the pasting trap that catches many people.

Why copying skips some hidden rows but not others#

Excel treats rows differently depending on how they were hidden:

So if you filter a list and copy it, you get just the filtered rows. But if you hid rows yourself, or collapsed a group, a plain copy brings the hidden rows with it, and they reappear wherever you paste. That's when you need to select the visible cells first.

A classic case is a report built with Data > Subtotal. You collapse the outline to level 2 so only the subtotal rows show, then copy them to send to someone. A plain copy pastes every detail row as well. Select the visible cells first and you get just the subtotals.

The examples use this list, where row 4 (Chloe) and row 6 (Ema) have been hidden by hand:

The gap in the row numbers (4 and 6 are missing) is the clue that rows are hidden. The guide to hiding and unhiding rows and columns shows how to hide and show them again.

How to select only visible cells with a shortcut#

The shortcut works on any selection:

1. Select the range, for example A1:C7.
2. Press Alt+; (Windows) or ⌘+Shift+Z (Mac).
3. Press Ctrl+C (Mac: ⌘C) to copy.
4. Click the destination and press Ctrl+V (Mac: ⌘V).

When you copy in step 3, the moving border appears around each visible block separately. That shows Excel has split the selection and skipped the hidden rows. The pasted result has four data rows (Ana, Ben, Dev and Farid), with no gaps.

How to select visible cells with Go To Special#

If you prefer the menus, or the shortcut doesn't work on your keyboard layout, use Go To Special:

1. Select the range.
2. On the Home tab, click Find & Select > Go To Special. Or press Ctrl+G (Mac: Control+G) and click Special.
3. Choose Visible cells only and click OK.

Go To Special has other useful options in the same dialog. Blanks selects every empty cell, which is the first step in filling blank cells. Formulas and Constants select cells by what they contain, which is handy before you lock cells and protect a sheet.

If you use this often on Windows, add Select Visible Cells to the Quick Access Toolbar: open File > Options > Quick Access Toolbar, choose All Commands, find Select Visible Cells and click Add.

Formatting and deleting only visible cells#

Selecting visible cells isn't just for copying. Once only visible cells are selected, anything you do affects only them:

• Format them, for example fill colour or bold, and hidden rows stay unchanged.
• Clear them with Delete, and hidden rows keep their contents.
• Type a value and press Ctrl+Enter (Windows) to enter it in every visible selected cell at once.

In a filtered list, Excel already limits most of these actions to the visible rows. For rows hidden by hand, always press Alt+; (Mac: ⌘+Shift+Z) first.

Watch out when pasting into a filtered list#

Copying from a filtered list is safe. Pasting into one is not. When you paste a block of values over a filtered range, Excel pastes into a continuous block of cells, including the rows the filter hides. Your data lands in the wrong rows and overwrites hidden ones.

Safer ways to update filtered rows:

• Sort first so the rows you want to change sit together, then paste.
• Use a formula in a helper column, fill it down, then copy and paste the results as values.
• Fill down with Ctrl+D (Mac: ⌘D) instead of pasting, when every visible row needs the same value or formula.

If a paste goes wrong, press Ctrl+Z (Mac: ⌘Z) straight away. The guide to editing cells, undo and redo explains how far back undo goes.

How to total only the visible cells#

SUM adds every cell in a range, hidden or not. In the example, =SUM(C2:C7) returns 211, including Chloe's 35 and Ema's 20.

To total only the rows you can see, use SUBTOTAL with 109 or AGGREGATE with option 5:

=SUBTOTAL(109,C2:C7)
=AGGREGATE(9,5,C2:C7)

With rows 4 and 6 hidden, both return 156 (38 + 40 + 42 + 36). Without any hidden rows, they return 211, the same as SUM. In AGGREGATE, 9 means SUM and 5 means "ignore hidden rows". SUBTOTAL with 9 instead of 109 ignores rows hidden by a filter but still counts rows hidden by hand. The guide to summing visible cells covers both functions in more detail.

Practise it#

Practise filtering a table in a live spreadsheet: you'll filter rows, total the visible ones and see each result checked instantly. Then practise copy, cut and paste, including Paste Special, so moving data never drags hidden rows along. Try the AGGREGATE exercise on this page first.