Copy Only Visible Cells in Excel (Skip Hidden and Filtered Rows)

Quick answer

Select the filtered or partly hidden range, press Alt+; (or go Home → Find & Select → Go To Special → Visible cells only), then copy and paste as normal. Excel now copies only the rows and columns you can see; the hidden ones stay behind. Paste into a range that has no hidden rows, or the pasted block will look incomplete until you unhide it.

You filter a list down to twenty rows, copy it, paste it into an email or a fresh sheet — and get all two hundred rows back. This is the single most common “Excel ignored me” complaint, and the fix is one selection step that most people have never seen. Here it is, with Microsoft’s own wording where it matters, the two shortcuts, and the three cases where it still goes wrong.

Why the hidden rows come along

A filter or a hidden row does not remove anything from the sheet; it collapses the row to zero height. A normal selection is a rectangle, and a rectangle includes every row inside it whether it is visible or not. So when you drag from A1 to D200 you have selected 200 rows, and the clipboard copies 200 rows. The trick is to turn the rectangle into a selection of only the visible cells before you copy.

The menu route (Microsoft’s steps)

These are Microsoft’s instructions, word for word¹:

1. “Select the cells that you want to copy.”
2. “Select Home > Find & Select, and pick Go To Special.”
3. “Select Visible cells only > OK.”
4. “Select Copy (or press Ctrl+C).”
5. “Select the upper-left cell of the paste area and select Paste (or press Ctrl+V).”

After step 3 the selection changes shape: instead of one solid rectangle you see a thin border around each visible block, with gaps where the hidden rows are. That is the sign it worked. If you still see one solid outline, the dialog did not apply — usually because nothing was hidden inside the range.

The keyboard route

Read carefully

On Windows, Alt+; (Alt and semicolon) selects visible cells only in the current selection — it is the keyboard equivalent of the Go To Special option above. Microsoft’s own Copy-visible-cells page does not list this shortcut¹, and its Excel shortcuts page documents only the Mac version, “Select only visible cells. Shift+Cmd+Asterisk (*)”². We include Alt+; because it is the standard Windows shortcut and it is what you will see in every Excel course; if it does nothing in your build, use F5 → Special → Visible cells only, which is the same dialog².

So the full keyboard sequence is: select the range, Alt+;, Ctrl+C, move to the destination, Ctrl+V. Four keystrokes, no mouse. Do it a few times and it becomes the way you copy any filtered list.

Where it still goes wrong

The paste looks incomplete. Microsoft’s note is the explanation: “Excel pastes the copied data into consecutive rows or columns. If the paste area contains hidden rows or columns, you might have to unhide the paste area to see all of the copied cells.”¹ Visible-cells-only applies to the source. The destination is always filled consecutively, so paste into a clean area — a new sheet is safest — rather than into another filtered list.

You wanted to paste into a filtered list. That is the reverse problem, and there is no visible-cells option for pasting. The reliable approaches are to sort the list so the target rows are contiguous, or to use a formula in a helper column that looks up the value you want to bring in — a VLOOKUP or XLOOKUP against the source table — and then convert it to values.

Formulas broke after pasting. Formulas in the visible rows that referenced the hidden rows now point at cells that were not copied. If the destination should hold numbers, paste values instead: after Ctrl+C, press Ctrl+Alt+V for Paste Special², then V for Values, Enter. The same dialog handles formats only and arithmetic on paste.

Subtotals and hidden rows

If the reason you are copying is to total the visible rows, you may not need to copy at all. SUBTOTAL(109, range) sums only the rows that are visible after a filter or manual hide, and SUBTOTAL(9, range) ignores filtered rows but includes manually hidden ones. Put that formula above the list, filter, and the total follows the filter. It also stays live when you next change the filter, which a pasted copy never does. And if you want a total per group rather than one total at the top, let Excel write those formulas for you with automatic subtotals.

On the exam

Filtering, hiding and moving data sit inside the two largest objective groups of exam MO-210 — “Manage worksheets and workbooks” and “Manage data cells and ranges” — at 25–30% each³. The exam is timed and hands-on, so a task like “copy the filtered rows to Sheet2” is exactly where Alt+; saves the minute you need elsewhere. The MO-210 guide lists all five objective groups.

Related

• Format Painter: copy formatting to one cell, many cells, or a whole sheet
• Hide values in a column with a custom number format
• Hide cell contents without hiding the row
• How to get Excel certified

Sources