Paste TO visible cells only in a filtered cells only

%3CLINGO-SUB%20id%3D%22lingo-sub-1013991%22%20slang%3D%22en-US%22%3EPaste%20TO%20visible%20cells%20only%20in%20a%20filtered%20cells%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013991%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20paste%20a%20formula%20or%20value%20in%20the%20visible%20cells%20of%20a%26nbsp%3Bfiltered%20column.%20How%20to%20go%20about%20it%3F%20Thanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1013991%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014003%22%20slang%3D%22en-US%22%3ERe%3A%20Paste%20TO%20visible%20cells%20only%20in%20a%20filtered%20cells%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014003%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460327%22%20target%3D%22_blank%22%3E%40NSK-Mar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3Ecopy%20the%20formula%20or%20value%20to%20the%20clipboard%3C%2FLI%3E%0A%3CLI%3Eselect%20the%20filtered%20column%3C%2FLI%3E%0A%3CLI%3Ehit%20F5%20or%20Ctrl%2BG%20to%20open%20the%20Go%20To%20dialog%3C%2FLI%3E%0A%3CLI%3EClick%20Special%3C%2FLI%3E%0A%3CLI%3Eclick%20%22Visible%20cells%20only%22%20and%20OK%3C%2FLI%3E%0A%3CLI%3Ehit%20Ctrl%2BV%20to%20paste.%3C%2FLI%3E%0A%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014021%22%20slang%3D%22en-US%22%3ERe%3A%20Paste%20TO%20visible%20cells%20only%20in%20a%20filtered%20cells%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014021%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3BThanks%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1020100%22%20slang%3D%22en-US%22%3ERe%3A%20In%20filtered%20column%2C%20need%20to%20paste%20in%20visible%20cells%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1020100%22%20slang%3D%22en-US%22%3E%3CP%3EHai%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20paste%20multiple%20values%20in%20the%20visible%20cells%20instead%20of%20same%20value%20or%20formula%2C%20can%20you%20please%20help%20me%20with%20this%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20also%20attached%20a%20file%20with%20an%20example%2C%20kindly%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20initial%20file%20with%20pivot.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158062iF82982B88E0875A3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Egetting%20this%20summarized%20as%20below.%2C%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158063i46ACD8AFAD0DB91C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efiltering%20on%20Messi%2C%20as%20Messi%20is%20the%20first%20person%20and%20hence%20filtering%20and%20have%20to%20accommodate%20the%20headers%20(Free%20kick%2C%20penalty%20%26amp%3B%20Dribble)%20to%20Messi%20and%20later%20I%20can%20paste%20it%20to%20rest%20of%20players.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158064iE4A7E73ED6B58326%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_2.png%22%20title%3D%22clipboard_image_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20I%20face%20the%20problem%20of%20pasting%20in%20visible%20cells%20in%20the%20filtered%20column%2C%20please%20assist%20me%20with%20the%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Karthik%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1024794%22%20slang%3D%22en-US%22%3ERe%3A%20Paste%20TO%20visible%20cells%20only%20in%20a%20filtered%20cells%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1024794%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20tried%20the%20steps%20below%20and%20go%20the%20below%20error%20message%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158344iC329B933F8B6DA0E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWhat%20might%20I%20be%20doing%20wrong%3F%26nbsp%3B%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1024879%22%20slang%3D%22en-US%22%3ERe%3A%20Paste%20TO%20visible%20cells%20only%20in%20a%20filtered%20cells%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1024879%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F464813%22%20target%3D%22_blank%22%3E%40DanNow%3C%2FA%3E%26nbsp%3BThe%20steps%20I%20described%20only%20work%20when%20you%20paste%20a%20single%20item%20into%20multiple%20cells.%20As%20the%20error%20message%20says%2C%20if%20you%20have%20several%20items%20selected%2C%20the%20target%20of%20the%20paste%20must%20be%20the%20same%20shape%20as%20the%20source.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20case%20you%20may%20need%20to%20sort%20your%20source%20table%20so%20you%20can%20select%20the%20data%20as%20one%20contiguous%20range%2C%20then%20sort%20the%20target%20table%20and%20paste%20as%20one%20contiguous%20rang%20there%2C%20too.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1030217%22%20slang%3D%22en-US%22%3ERe%3A%20Paste%20TO%20visible%20cells%20only%20in%20a%20filtered%20cells%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1030217%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3BThanks%20for%20responding.%26nbsp%3B%20Yes%2C%20that%20is%20the%20difficulty%3B%20the%20target%20table%20was%20not%20easily%20sorted%20into%20one%26nbsp%3B%3CSPAN%3Econtiguous%20rang.%20That%20said%2C%20I%20just%20used%20some%20vlookups%20to%20help%20me%20transfer%20the%20data%20from%20the%20source%20file%20to%20the%20destination%20file.%26nbsp%3B%20It%20is%20a%20shame%20that%20there%20isn't%20a%20way%20to%20inform%20Excel%20that%20the%20pasted%20data%20should%20only%20be%20deposited%20into%20visible%20cells.%26nbsp%3B%20Have%20a%20good%20one.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1459544%22%20slang%3D%22en-US%22%3ERe%3A%20Paste%20TO%20visible%20cells%20only%20in%20a%20filtered%20cells%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1459544%22%20slang%3D%22en-US%22%3EIt%20is%20not%20working.%20Can%20you%20send%20me%20a%20video%20of%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548938%22%20slang%3D%22en-US%22%3ERe%3A%20Paste%20TO%20visible%20cells%20only%20in%20a%20filtered%20cells%20only%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548938%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F697957%22%20target%3D%22_blank%22%3E%40Muhammad_Asim%3C%2FA%3E%26nbsp%3BI%20experience%20similar%20problem%20also%20when%20pasting%20into%20filtered%20cells.%20This%20seems%20like%20it%20should%20be%20pretty%20simple%2C%20but%20it%20doesn't%20work.%20See%20attached%20video%20evidence.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20Microsoft's%20moderator%20response%20to%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I want to paste a formula or value in the visible cells of a filtered column. How to go about it? Thanks 

8 Replies
Highlighted

Hello @NSK-Mar 

 

  • copy the formula or value to the clipboard
  • select the filtered column
  • hit F5 or Ctrl+G to open the Go To dialog
  • Click Special
  • click "Visible cells only" and OK
  • hit Ctrl+V to paste.
Highlighted
Highlighted

Hai @Ingeborg Hawighorst 

 

Is it possible to paste multiple values in the visible cells instead of same value or formula, can you please help me with this query.

 

I have also attached a file with an example, kindly assist.

 

This is the initial file with pivot.

 

clipboard_image_0.png

 

getting this summarized as below.,

clipboard_image_1.png

 

filtering on Messi, as Messi is the first person and hence filtering and have to accommodate the headers (Free kick, penalty & Dribble) to Messi and later I can paste it to rest of players. 

 

clipboard_image_2.png

 

Here I face the problem of pasting in visible cells in the filtered column, please assist me with the solution.

 

Thanks!

 

- Karthik

Highlighted

Hi@Ingeborg Hawighorst 

 

I just tried the steps below and go the below error message:

 

clipboard_image_0.png

What might I be doing wrong?  Thanks.

Highlighted

@DanNow The steps I described only work when you paste a single item into multiple cells. As the error message says, if you have several items selected, the target of the paste must be the same shape as the source.

 

In this case you may need to sort your source table so you can select the data as one contiguous range, then sort the target table and paste as one contiguous rang there, too.

Highlighted

@Ingeborg Hawighorst Thanks for responding.  Yes, that is the difficulty; the target table was not easily sorted into one contiguous rang. That said, I just used some vlookups to help me transfer the data from the source file to the destination file.  It is a shame that there isn't a way to inform Excel that the pasted data should only be deposited into visible cells.  Have a good one.

Highlighted
It is not working. Can you send me a video of it.
Highlighted

@Muhammad_Asim I experience similar problem also when pasting into filtered cells. This seems like it should be pretty simple, but it doesn't work. See attached video evidence.

 

What is Microsoft's moderator response to this?