4

Let's say I have some rows or columns that are hidden, and drag a selection across where those hidden values would normally be. Is there a way to prevent Microsoft Excel from copying those hidden cells?

I've found a workaround that sometimes works by pasting it all into Notepad, and then copying that, then pasting it into the spreadsheet I want to paste those values into, but it seems there should be a better way to do that. Unfortunately this workaround doesn't always work so well.

Improve this question

6 Answers 6

Reset to default
4

From Copy visible cells only:

If some cells, rows, or columns on your worksheet are not displayed, you have the option of copying all cells or only the visible cells. By default, Excel copies hidden or filtered cells in addition to visible cells. If this is not what you want, follow the steps in this article to copy visible cells only. For example, you can choose to copy only the displayed summary data on an outlined worksheet.

  • Select the cells that you want to copy.
  • On the Home tab, in the Editing group, click Find & Select, and then click Go To.
  • In the Go To dialog box, click Special.
  • Under Select, click Visible cells only, and then click OK.
  • On the Home tab, in the Clipboard group, click Copy.

  • Select the upper-left cell of the paste area.

    Tip: To move or copy a selection to a different worksheet or workbook, click another worksheet tab or switch to another workbook, and then select the upper-left cell of the paste area.

  • On the Home tab, in the Clipboard group, click Paste.

Notes

Excel pastes the copied data into consecutive rows or columns. If the paste area contains hidden rows or columns, you might need to unhide the paste area to see all of the copied cells.
If you click the arrow below Paste, you can choose from several paste options to apply to your selection.

Improve this answer
7

I recently learned this nice shortcut from another SU user.

Select all cells as you usually do and then press

  1. Alt+; » Select only visible cells within your (or Alt+Shift+, depending on keyboard layout)
  2. Ctrl+C » Copy
  3. Ctrl+V » Paste
Improve this answer
4
  • I believe that Select Visible Cells is (Alt)+;.
    – G-Man Says 'Reinstate Monica'
    Commented Aug 24, 2015 at 17:02
  • @G-Man So your keyboard has a ; key? I always have to press Shift+, :)
    – nixda
    Commented Aug 24, 2015 at 17:19
  • Yes.
    – G-Man Says 'Reinstate Monica'
    Commented Aug 24, 2015 at 17:31
  • @G-Man Ok, then let's add the US version
    – nixda
    Commented Aug 24, 2015 at 17:36
0

Okay, here is what I found. When using autofilters, only the filtered results get COPIED, but if you CUT the selection, both visible and unvisible cells are moved.

Improve this answer
0

I tried a workaround. All rows are copied only when 1 option is selected in filter. Try using 2 values in filter (ie two values in the same column). To overcome the issue of unwanted data, I inserted a dummy row (and select my data and the dummy data). And after copying that i just remove the dummy data. Please reply for any clarification.

Improve this answer
1
  • 2
    You’re answering the wrong question. This question isn’t about filtered data, it’s about hidden rows.
    – Scott - Слава Україні
    Commented Oct 18, 2017 at 2:54
0

Select all cells as you usually do and then

  1. Press F5 --> Special ---> Tick on VISIBLE CELLS ONLY--> OK

  2. Ctrl+C » Copy

  3. Ctrl+V » Paste

Improve this answer
-2

Same problem, can't do with excel on Macbook. Solved simple by copy to another program (ect. Autocad) then copy from Cad to Excel.

Improve this answer
1
  • 1
    The OP says he has tried that and does not want this workaround.
    – user477799
    Commented Apr 3, 2017 at 7:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .