Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In short, I have filtered a worksheet by column A, and I want to copy/paste from column B to column C. Obviously I don't wish to copy/paste values from rows that were filtered-out here.

The above sounds ridiculously simple, right?

  1. First I tried simply copy/pasting on the filtered worksheet. This appeared to select and copy only the filtered data, however pasting appeared to insert values into hidden/filtered rows - as you might expect.

  2. So my initial research suggests I may wish to select the filtered data and press Alt+;, which is a shortcut key for Goto Special > Select Visible. Then just copy-paste.

    CTRL+C correctly copies the filtered data, however when I go to paste the values into another column, it pastes into hidden rows as well.

  3. Okay, so perhaps I should also "Select Visible" on the cells I wish to paste into as well? Nope - that gives me the error That command cannot be used on multiple selections.

What am I doing wrong?!

share|improve this question

3 Answers 3

Yes, I agree this is stupid. You can do it using the 'fill handle' - select the cells you want to copy and drag the fill handle across to copy them into the next column. This only works for a contiguous selection, and only if you're copying into the adjacent column. But presumably you could always move your column temporarily. I can't see any other way to do it without recourse to VBA.

share|improve this answer
1  
THIS is freaking amazing, it is the only solution that I've found to actually do this properly. Thank you!! –  Shackrock Dec 3 '13 at 15:22

You have found one of Excel's many "got ya's".

You have to do it the other way around. Copy/paste everything, filter out what you want to keep then delete the contents.

Alternatively, you could create a macro to do it in one step.

share|improve this answer
    
Thanks. Glad to have some confirmation that Excel spreadsheets are indeed a silly way of working with large data sets. –  hazymat Dec 18 '12 at 17:27
    
Yep! If you regularly need to manipulate tabular data, I recommend OpenRefine (used to be Google Refine) rather than Excel. This tool is superb at tidying up tabular data. –  Julian Knight Dec 18 '12 at 21:41
    
Quick update to this. You could also try out the Microsoft Power Query Preview. This allows you to query, filter and manipulate all sorts of data including data queried from the parent workbook. –  Julian Knight Dec 18 '13 at 13:42

A better approach might be to use an IF formula something like =if(A2='filter criteria',B2,"") in column C.

If you want static rather than dynamic data you can copy column c afterwards and paste special(values).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.