You need to copy data from a filtered list or a table with hidden rows. When you paste, all the hidden data appears, cluttering your results. This happens because the standard copy command includes all cells in the selected range. This article explains how to use the Alt+Semicolon shortcut to select and copy only the visible cells you see.
Key Takeaways: Copy Visible Cells in Excel
- Alt+Semicolon (Alt+;): Selects only the visible cells within a highlighted range before copying.
- Go To Special > Visible cells only: An alternative menu-driven method to achieve the same result.
- Paste Values or Formulas: You can paste the copied visible data as values, formulas, or with formatting to a new location.
Why Excel Copies Hidden Cells by Default
Excel is designed to work with the entire grid of cells in a worksheet. When you apply a filter or manually hide rows, you are only changing the view, not deleting the data. The hidden rows and columns remain part of the worksheet’s structure. The standard copy command operates on the underlying cell range you select, ignoring the visual filter state. This ensures formulas that reference the range continue to work correctly. The feature to copy only visible cells is a special operation that must be explicitly activated.
Steps to Select and Copy Visible Cells
Follow these steps to copy data while skipping any hidden or filtered-out rows and columns. The first method uses a keyboard shortcut for speed.
Method 1: Use the Alt+Semicolon Shortcut
- Select your data range
Click and drag to highlight the cells you want to copy. This can include both visible and hidden cells. - Press Alt+Semicolon (Alt+;)
On your keyboard, hold the Alt key and press the semicolon key. You will see the selection marquee change to a dotted line around only the visible cells. - Copy the selection
Press Ctrl+C to copy the now-selected visible cells to the clipboard. - Paste to your target location
Click the cell where you want the data to begin and press Ctrl+V. Only the data from the visible cells will be pasted.
Method 2: Use the Go To Special Dialog
- Select your data range
Highlight the cells containing both visible and hidden data you wish to copy. - Open the Go To Special dialog
On the Home tab, click Find & Select in the Editing group. Then choose Go To Special from the dropdown menu. Alternatively, press F5 and click the Special button. - Select Visible cells only
In the Go To Special dialog box, select the option labeled Visible cells only. Click OK. - Copy and paste the data
Press Ctrl+C to copy. Navigate to your destination cell and press Ctrl+V to paste.
Common Mistakes and Limitations
Knowing what to avoid ensures your copy operation works as expected every time.
Forgetting to Select Visible Cells First
The most common error is pressing Ctrl+C immediately after selecting a range. This copies all cells. Always use Alt+Semicolon or the Go To Special command first. Verify the selection marquee changes to a dotted outline before copying.
Pasting Overlapping the Source Range
If you paste the copied visible cells into a location that overlaps the original source range, you may get unexpected results or errors. Excel cannot paste data onto itself in this context. Always paste to a completely separate area of the worksheet or a different sheet.
Copying from Grouped and Outlined Data
The Alt+Semicolon method works on rows and columns hidden via grouping and the outline controls. However, if you have collapsed multiple group levels, only the currently visible summary rows will be selected and copied. The hidden detail rows within the collapsed groups will be skipped.
Keyboard Shortcut vs Menu Method: Key Differences
| Item | Alt+Semicolon (Keyboard Shortcut) | Go To Special > Visible cells only (Menu) |
|---|---|---|
| Speed | Faster, one key combination | Slower, requires multiple clicks |
| Accessibility | Requires memorizing the shortcut | Easier to discover via the ribbon interface |
| Selection Feedback | Immediate visual change to dotted outline | Same visual feedback after clicking OK |
| Use with Macros | The macro recorder captures this as the Go To Special method | Recorded directly as the Application.Goto method |
Use the Alt+Semicolon shortcut for daily efficiency after you learn it. The Go To Special menu is a reliable fallback and is easier to record in a macro. Both methods perform the same core function of isolating visible cells for copying. For advanced automation, record the menu action to generate the correct VBA code.