When you copy a range in Excel and paste it into a filtered list, Excel pastes into the hidden rows too, which corrupts your data. This article shows you the reliable ways to paste only into visible cells, why the standard paste fails, and how to avoid the problem in both Windows and Mac versions of Excel.
Why Excel Pastes Into Hidden Rows
Excel treats a filtered range as a single contiguous block of cells. When you copy a range that includes hidden rows and paste it next to a filtered range, Excel pastes the entire block, overwriting hidden rows as well as visible ones. This happens because the paste operation uses the original cell addresses, not the visible subset. The same issue occurs when you paste into a filtered list from an unfiltered copy: Excel fills every row in the target range, including those hidden by the filter.
The Standard Workaround: Select Visible Cells Only
The most common fix is to use the Go To Special dialog to select only visible cells before pasting. Here are the steps for Excel 2016, 2019, 2021, and Microsoft 365:
- Select the range where you want to paste (the filtered list).
- Press F5 or Ctrl+G to open the Go To dialog.
- Click Special at the bottom-left.
- Choose Visible cells only and click OK.
- Now press Ctrl+V to paste. Excel pastes only into the visible cells.
Alternatively, you can add the Select Visible Cells button to the Quick Access Toolbar (QAT). It’s the icon that looks like a small square with a dashed border. Once added, you click it before pasting to achieve the same result with one click.
Using the Keyboard Shortcut (Windows and Mac)
There is a dedicated keyboard shortcut for selecting visible cells only:
- Windows:
Alt+;(semicolon) - Mac:
Cmd+Shift+Z(in some versions it’sCmd+;– check your Excel version)
After selecting the target range, press the shortcut, then paste. This is faster than the Go To Special route once you memorize it. Note that the shortcut works only when the selection includes hidden rows; if you press it on a fully visible range, nothing changes.
Pasting Formulas or Values: Same Problem, Same Fix
Whether you paste formulas, values, or formatting, the hidden-row issue persists. The fix is identical: select visible cells first. However, when pasting formulas, be aware that relative references adjust based on the target cell, not the visible row order. For example, if you copy =A1*2 from row 1 and paste it into visible row 5 (with rows 2-4 hidden), the formula becomes =A5*2, not =A2*2. This is usually what you want, but it can surprise you if you expected the formula to reference the original row numbers.
Using the Paste Special Dialog for Extra Control
If you need to paste only values or only formatting, combine the visible-cells selection with Paste Special:
- Select the target range and press
Alt+;to select visible cells. - Press
Ctrl+Alt+V(Windows) orCmd+Ctrl+V(Mac) to open Paste Special. - Choose Values or Formats as needed.
- Click OK.
This is especially useful when you want to paste calculated results without overwriting formulas in the target area.
Common Mistakes and Myths
Many users try to use the Filter dropdown to select only visible rows, but that does not affect the paste operation. Another misconception is that copying only visible cells (by selecting them with the mouse) solves the problem – it does not, because Excel still remembers the full copied range. The only reliable way is to explicitly select visible cells in the target range before pasting.
Another frequent error: after selecting visible cells, users press Ctrl+C again, which resets the copy and breaks the selection. Always copy first, then select the target range, then use the visible-cells selection, then paste.
Alternatives: Using Formulas or Power Query
If you frequently need to paste into filtered lists, consider these alternatives:
- Use a helper column: Add a column with a formula that references the source data, then filter and copy down. This avoids paste entirely.
- Use Power Query: Load both ranges into Power Query, merge them, and load the result back. This is more robust for complex transformations.
- Use VBA: A simple macro can loop through visible cells and paste values individually. This is overkill for most users but useful for repetitive tasks.
Frequently Asked Questions
Why does my paste still go into hidden rows after I select visible cells?
Make sure you selected the target range before pressing Alt+;. If you press the shortcut after copying but before selecting the target, it does nothing. Also, ensure that the target range is actually filtered; if you are pasting into a range with manually hidden rows (not filtered), the same technique works, but you must select the entire range first.
Does this work in Google Sheets?
No, Google Sheets does not have a “visible cells only” selection. In Sheets, you can use the FILTER function to create a temporary visible-only range, or use the “Paste special” > “Paste values only” after filtering, but it still pastes into all rows. The workaround is to use a formula like =FILTER(A:A, A:A<>“”) to extract visible rows.
Can I paste into visible cells without selecting them first?
No, Excel requires you to select the target visible cells before pasting. There is no built-in option to paste only into visible cells automatically. The only exception is if you use a VBA macro that loops through the selection and pastes into each visible cell individually.
Why does my shortcut Alt+; not work on a Mac?
On Mac, the shortcut is Cmd+Shift+Z in most versions, but some older versions use Cmd+;. Check your Excel menu: go to the Edit menu and look for “Select Visible Cells” – the keyboard shortcut is listed there. If it’s not assigned, you can add it via System Preferences > Keyboard > Shortcuts.
Does this work when pasting from another application?
Yes, as long as you copy from Excel or another app, the visible-cells selection works for pasting from any source. However, if you copy from a web page or another program, Excel may paste as a table with formatting that can affect the result. Use Paste Special > Values to avoid formatting issues.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.
More cards in this drawer
- Photoshop vs Lightroom AI Features Compared
- DNF vs DNF5: Which Package Manager?
- Car Trends Making Cars Worse: 7 Downsides
- Nostalgic Classic Games Revamped on G2A?
- Nvidia RTX 4000 Super GPUs: Full Specs & Buying Guide
- Korean SUV Making BMW and Audi Owners Question Their Choices?