Paste TO visible cells only in a filtered cells only

Copper Contributor

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

48 Replies

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.

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

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.

@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.

@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.

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

@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?

@NSK-Mar Here's what I follow:

1. Cut+paste the rows (for which you want to paste the values) into a new sheet.

2. You will now be able to simply copy+paste the required values into this new sheet.

3. Copy the data from the new sheet back into the original sheet

4. Adjust the sorting as required.

PS: If the original data is not sorted on any column and you want to retain that original order, you can add a column to remember the sequence of rows (1, 2, 3...), which can they be sorted by to get the original order back.

@NSK-Mar 

As others have stated, Excel does not allow you to paste into a filtered data. The best way to do this is to sort instead of filter. The easiest way to do this, especially for information that is not easily sortable, is to:

- filter your info

- fill the cells of your filtered list with a color

- Clear the filter

- do a custom sort of the list by color and then by a-z (to make it match up with the information you are pasting in)

- Paste your information in your sheet

- Reorder the colors from your cells and reorder the information if necessary.

 

Note: If the original order of your information is important, do what one user suggested and add a column to your original sheet and number each row, so you can resort back to its original order.

 

Also, if you are working with extremely large lists where you first have to identify the matching cells before copying over the information, you can always use conditional formatting to identify and highlight any matching cells in a column from one sheet to another within the same workbook. After doing this, you can then follow the steps above to copy the information over. 

@Ingeborg Hawighorst 

is it possible to add icon for it in the toolbar?

@DanNow 

 

I just tried to do the same steps and got the same error message. It would seem that this is a bug in the software.

 

Excel shows a count of 25 items when I select the values to copy. When I filter the 25 cells I want to paste into, the count is 25. Even though I have used the "Go to, special, visible cells", it still won't let me paste into those 25 cells.

 

Other users have reported this inconsistency in software behavior here:

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/34651030...

@Russell Reynolds 

 

Hi Russell. I'm facing the same issue. It copies over the hidden rows, but gives the same error when I try to paste it

How can it be possible that the world`s leading company in terms of office software is not able to offer a simple way to solve such a problem. It's one of their basic business models and they just fail to do a good job. And there are a lot of similar problems beside the mentioned one. I can go through thousands of values of my thesis now to check whether something according to that issue went wrong before. Thank you Microsoft for NOTHING!

1: Filter on the source items you want moved
2: Fill the destination column with a colour
3: Turn off the filter
4: Sort your destination column by colour
4: Copy>paste the source to destination
5: Clear contents of source cells if you wanted a cut>paste

@Ingeborg Hawighorst

Thanks that's very helpful. May I add that you need to copy (Ctrl + C) your selection after you select 'visible cells only'? 

@NSK-Mar Another option would be to replicate your source column, then clear out all the values not shown in your filtered list ('reverse' the filter, then clear the values).  Then remove the filter from the table altogether, copy the entire column, finally do Paste Special and select Skip Blanks.  

@Mike_Selman 

 

think it is better ,if u upload video

@NSK-Mar 
Here's a video can solve this problem
https://youtu.be/F0pUKDQZ9RI

 

This video shows the problems that you can have when you try to copy and paste data into a filtered list. You can accidentally paste data into hidden rows, and ruin your data. Watch this video to see why the problem occurs, and a couple of workarounds, to help you avoid the problem. Visit this ...