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

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.

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.

1: Filter on the source items you want moved2: Fill the destination column with a colour3: Turn off the filter4: Sort your destination column by colour4: Copy>paste the source to destination5: Clear contents of source cells if you wanted a cut>paste

HiIngeborgHawighorst I just tried the steps below and go the below error message: 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.

Paste TO visible cells only in a filtered cells only

SindhuraSingeetham
Copper Contributor
Aug 28, 2020
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.
IngeborgHawighorst
MVP
Nov 17, 2019
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.
- jaleescool
  Copper Contributor
  Sep 13, 2024
  it does not work for me IngeborgHawighorst
- maxbaumann
  Copper Contributor
  Jul 24, 2024
  IngeborgHawighorst
  I am not sure that works if you dont want to paste an ENTIRE column. There may be different content above or below which you dont want to overwrite, so your second bullet point "select the filtered column" does not work in that instance. Selecting just a cell results in "this action won't work on multiple selections".
  Any other idea?
- Sajad_Alam
  Copper Contributor
  Mar 12, 2024
  IngeborgHawighorst Its not working
  I am still unable to put data in visible cells only'

Forum Discussion

Paste TO visible cells only in a filtered cells only

Share

Resources