When rows are hidden, a normal copy also copies the hidden rows, and pasting can overwrite them. Press Alt + ; to select only the visible cells first, then copy or paste.
The problem
Copying a range with manually hidden rows copies the hidden rows too.
Method 1: Alt + ;
Select the range, press Alt + ;, then Ctrl + C. The selection shows small gaps where the hidden rows are skipped.
Method 2: Go To Special
Press F5 or Ctrl + G → Special → Visible cells only → OK.
Method 3: Quick Access Toolbar button
Customize Quick Access Toolbar → More Commands → All Commands → Select Visible Cells → Add.
Paste into visible cells only
Filtered and manually hidden rows behave differently when you paste:
| Rows hidden by | Pasting a single value into the range |
|---|---|
| A filter | Goes only into visible rows |
| Hide Rows | Goes into hidden rows too |
To fill only the visible cells: copy the value, select the target range, press Alt + ;, then Ctrl + V.
Pasting a block of different values into a filtered range doesn’t line up row by row. Sort the data instead of filtering, or use a helper column with a formula.
Formulas that ignore hidden rows
To total only what you can see, use =SUBTOTAL(109,C2:C100) or =AGGREGATE(9,5,C2:C100). Both skip hidden and filtered rows.