How to Copy Hidden Rows in Excel Without Losing Hidden Data

User question

How do I copy hidden rows in Excel?

Artificial Intelligence Response

Direct answer to the question

It depends on what you mean by “copy hidden rows” in Excel:

• If you want to copy a range and include manually hidden rows:
  Select the full range and press Ctrl+C, then paste. Excel normally includes manually hidden rows in a normal copy/paste.

• If the rows are hidden by a filter:
  Excel usually copies only the visible filtered rows. To copy the hidden filtered-out rows too, you need to clear the filter first, then copy the full range.

• If you want to copy only the hidden rows and exclude visible rows:
  Excel does not provide a simple built-in command for that. You can either unhide them and copy manually, invert the filter if filtering was used, or use VBA.

---

Detailed problem analysis

1. Rows hidden manually

If rows were hidden using:

• Right-click row number → Hide
• Home → Format → Hide & Unhide
• Grouping/outlining

then a normal copy operation usually includes those hidden rows.

Steps:

1. Select the full range, including the hidden rows.
   Example: select A1:D100.
2. Press Ctrl+C.
3. Go to the destination.
4. Press Ctrl+V.

The hidden rows will be pasted as part of the copied data. In the destination, they may appear visible unless the destination rows are also hidden.

---

2. Rows hidden by a filter

If rows are hidden because you applied a filter, Excel treats the filtered-out rows differently.

For example, if you filter a table to show only “Completed” tasks, rows containing “Pending” are hidden by the filter. If you copy the visible filtered range, Excel normally copies only the visible rows.

To include the filtered-out hidden rows:

1. Go to the Data tab.
2. Click Clear in the Sort & Filter group, or remove the filter.
3. Select the full range.
4. Press Ctrl+C.
5. Paste where needed.

So, for filtered data, the safest method is: clear the filter first, then copy.

---

3. Copying only visible rows — the opposite case

Many Excel users ask this question because they actually want to avoid copying hidden rows. If that is your goal, use Visible cells only.

Windows shortcut:

1. Select the range.
2. Press Alt + ;
3. Press Ctrl+C
4. Paste with Ctrl+V

Alternative method:

1. Select the range.
2. Go to Home → Find & Select → Go To Special.
3. Choose Visible cells only.
4. Click OK.
5. Copy and paste.

This copies only what you can see and excludes manually hidden rows and columns.

---

Practical guidelines

If you want to include hidden rows

Use this:

Select full range → Ctrl+C → Ctrl+V

But if the rows are hidden by a filter:

Clear filter → Select full range → Ctrl+C → Ctrl+V

If you want to exclude hidden rows

Use this:

Select range → Alt+; → Ctrl+C → Ctrl+V

If you want only the hidden rows

Excel has no simple native command for this. Options include:

• Temporarily unhide the rows and copy them manually.
• If a filter caused the hiding, reverse or adjust the filter to show the rows you want.
• Use VBA if you need to extract manually hidden rows automatically.

Example VBA macro:

Sub CopyHiddenRows()
Dim rng As Range
Dim r As Range
Dim dest As Worksheet
Dim destRow As Long
Set rng = Selection
Set dest = Worksheets.Add
destRow = 1
For Each r In rng.Rows
If r.EntireRow.Hidden = True Then
r.Copy dest.Rows(destRow)
destRow = destRow + 1
End If
Next r
End Sub

To use it:

1. Select the range containing hidden rows.
2. Press Alt+F11.
3. Insert a new module.
4. Paste the macro.
5. Run it with Alt+F8.

---

Brief summary

• Manually hidden rows: normal copy/paste includes them.
• Filtered-out rows: clear the filter first if you want to copy them.
• Visible rows only: use Alt+; before copying.
• Only hidden rows: use filtering logic, manual selection after unhiding, or VBA.

Disclaimer: The responses provided by artificial intelligence (language model) may be inaccurate and misleading. Elektroda is not responsible for the accuracy, reliability, or completeness of the presented information. All responses should be verified by the user.