Select Visible Cells Only: Excel Shortcut & Guide
Selecting visible cells only in Excel lets you copy, format, or delete data from filtered or hidden rows without affecting the hidden cells. You can do it with the keyboard shortcut Alt+; (Windows) or Cmd+Shift+Z (Mac), or by using the Go To Special dialog. This article explains what the feature does, when to use it, and how to avoid common mistakes.
Why “Select Visible Cells Only” Matters
When you filter a list or hide rows, Excel highlights the visible rows, but copying that selection also copies the hidden cells. Pasting it elsewhere brings along rows you didn’t want. The same issue happens when you apply formatting, delete data, or create charts from filtered data. The “visible cells only” command restricts your action to the rows and columns you can actually see.
This is a daily need for anyone who works with large datasets: sales reports, inventory lists, or any table where you temporarily hide rows to focus on a subset. Without this feature, you would have to manually re-type data or use complex formulas to avoid hidden rows.
How the Feature Works
Excel stores information about hidden rows and columns in the worksheet’s structure. When you filter, Excel marks certain rows as hidden (their row numbers turn blue). The “visible cells only” command tells Excel to create a selection that skips those hidden rows and columns. The selected range appears as a set of separate blocks, and when you copy, Excel copies only those blocks.
You can access the command in three ways:
- Keyboard: Alt+; (Windows) or Cmd+Shift+Z (Mac)
- Ribbon: Home tab → Find & Select → Go To Special → Visible cells only
- Quick Access Toolbar: add the command for one-click access
After you select visible cells, you can copy, cut, format, or delete. The selection looks normal, but if you press Ctrl+C and paste, only the visible cells are pasted. If you delete, only visible cells are deleted, leaving hidden rows intact.
How to Select Visible Cells Only (Step-by-Step)
Here’s the exact process for the most common scenario: copying filtered data.
- Apply a filter to your data (or manually hide rows).
- Select the range that includes the rows you want to copy.
- Press Alt+; (Windows) or Cmd+Shift+Z (Mac). You’ll see a subtle outline around the visible cells.
- Press Ctrl+C (or Cmd+C on Mac) to copy.
- Click where you want to paste, then press Ctrl+V (or Cmd+V). Only visible rows appear.
If you forget step 3, you’ll paste the hidden rows too. A quick way to check: after selecting, look at the Name Box (top-left). If it shows a single cell like “A1” but you selected multiple rows, that means you have a multi-area selection—a sign you’re on the right track.
Common Mistakes and How to Avoid Them
The most frequent error is copying without pressing Alt+; first. Another is thinking that filtering automatically excludes hidden cells from copying—it doesn’t. Also, if you hide columns, the same issue occurs, and the same shortcut works.
Another mistake is using “Go To Special” and accidentally selecting “Blanks” instead of “Visible cells only.” That selects empty cells, not visible ones. Always double-check the dialog option.
When pasting, if you paste into a range that has hidden rows, the visible cells will paste into the visible rows only, which can shift data unexpectedly. It’s safer to paste into a fresh area.
Deleting visible cells only is trickier: after selecting visible cells, press Ctrl+- (minus) and choose “Shift cells up” or “Delete entire row.” If you just press Delete, you clear the contents but leave the empty rows, which can mess up your data layout.
Alternatives to the Shortcut
If you don’t like the shortcut, you can use the ribbon path every time. But there are also formula-based alternatives:
- SUBTOTAL function: Use =SUBTOTAL(109, range) to sum only visible cells in a filtered list.
- AGGREGATE function: Similar to SUBTOTAL but with more options, like ignoring errors.
- Copy filtered data with a formula: Use a helper column with =SUBTOTAL(103, A2) to mark visible rows, then filter on that column.
For copying, the shortcut is the fastest. For calculations, SUBTOTAL is the standard tool. If you need to extract visible data into a separate list, the shortcut is still the best because it gives you a direct copy.
When the Feature Doesn’t Work (and What to Do)
Sometimes Alt+; seems to do nothing. This happens when your selection is already a single contiguous block with no hidden rows or columns. The command only matters when there are hidden cells. If you have a filter but the rows are not hidden (e.g., you selected entire columns), the shortcut may not change anything.
Another case: if you have merged cells, the visible selection can behave unpredictably. Unmerge before using the command.
If you’re using a Mac, the shortcut Cmd+Shift+Z might conflict with the “Redo” command in some Excel versions. In that case, use the ribbon or add the command to your Quick Access Toolbar.
If you’re working with a Table (Insert > Table), filtering works the same, but the shortcut still works. If you’re using a PivotTable, the concept is different—PivotTable fields are not hidden rows in the same way, so the shortcut may not apply.
Frequently Asked Questions
Why does my copy paste include hidden rows even after filtering?
Because filtering hides rows visually, but Excel still considers them part of the range. You must explicitly select visible cells only (Alt+;) before copying. Otherwise, the hidden rows are included in the clipboard.
What is the exact shortcut for visible cells only on a Mac?
In most current Excel for Mac versions, it’s Cmd+Shift+Z. If that doesn’t work, go to Home > Find & Select > Go To Special > Visible cells only. You can also add the command to the Quick Access Toolbar for a one-click button.
Can I delete hidden rows after selecting visible cells?
Yes, but be careful. After selecting visible cells with Alt+;, press Ctrl+- and choose “Delete entire row.” That deletes only the visible rows, leaving hidden rows in place. If you press Delete (the key), it only clears the contents, not the rows.
Does the shortcut work with grouped rows (outlined) as well?
Yes, it works with manually hidden rows, grouped rows, and filtered rows. Any row that is hidden by any method is excluded from the selection when you use the command.
How do I copy only visible cells to another sheet?
Select the data, press Alt+; (or Cmd+Shift+Z), then Ctrl+C, go to the other sheet, and Ctrl+V. The pasted data will only contain the visible rows, and it will be contiguous (no gaps).
Is there a way to make the shortcut always available?
You can add the “Select Visible Cells” command to your Quick Access Toolbar (QAT). Right-click the QAT, choose “More Commands,” select “All Commands,” scroll to “Select Visible Cells,” add it, and it’s always one click away.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.