Ability to paste to filtered (visible) cells
PROBLEM: Excel's PASTE (ctrl-V) operation does not distinguish between filtered and unfiltered cells, even though its COPY (ctrl-C) operation copies filtered (visible) cells.
SOLUTIONS:
* Enhance PASTE SPECIAL (ctrl-alt-V) to support the ability to paste into only filtered (visible) cells.
* Add a user option to support this default PASTE action.
BENEFITS:
* Eliminates the danger of pasting into a filtered area without realizing the problem. This is a very common problem with users new to Excel. Even experienced users must be constantly aware of this problem.
* Addresses a problem for which there is no good workaround. All VBA workarounds are very slow and/or poorly supported. For example, one could un-filter, paste and re-filter, but saving and restoring the state of a user’s filters is subject to change with each Excel upgrade. One could loop through all visible rows to paste data, but pasting row-by-row is MUCH slower than pasting a range, especially for many rows.
Dave McCulloch
shared this idea
47 comments
-
Anonymous
commented
Hello Team, has this problem been solved? Is there a Paste into Visible Cells Only solution yet? Macros wouldn't work as creating it for the new data updates would take almost as much time as inserting those 2,000 copied cells into (not the same) range of visible cells one by one. Terribly sad story... I've seen questions about this going back to 2015 and earlier... I hope this has been addressed - this would help and be appreciated a lot. Thanx...
-
Tom Wright
commented
This issue, last year nearly broke me (professional burnout) when cleaning big data. Each iteration took a solid day of concentration and the first two days had to start from the drawing board whilst working under high pressure, to a deadline to prove the benefits of new software.
In the end I learned the keyboard short cut to select visible cells, but this only works if pasting a single value. With ranges, it's a whole new ball game. -
Anonymous
commented
Dears Excel developers, copying filtered cells (which is working well using visible only selection) need its equivalent to paste, that's a must definitely. Lot of time saving. Please bring this up !
-
Andy
commented
Good(ish) news! The easiest workaround I've found is to use Google Sheets, as it actually pastes filtered lists as you'd expect. MS needs to fix this bug - thanks to validation steps I've found corrupted data from this bug before it reaches our production system. Pretty surprising to see this behavior in excel. I wonder how many spreadsheets have been completely messed up from this!?!
-
Anonymous
commented
I feel I am going aqound in circles with thi, it is incredibly limiting and unacceptable how it is taking to solve. I think I teied everything in every forum, getting stuck in theis operation cannot be done in multiple sections. Its really really despairing, please fix this!
-
Anonymous
commented
The ability to paste into visible cells is not available under Paste Special when pasting in a table. Brilliant, Microsoft, just brilliant!
BTW - Microsoft Support pointed to this article that was published in 2014, so they knew this was a problem even way back then. Does Microsoft even give a $#!+ what users think?!??
https://contexturesblog.com/archives/2014/04/24/problems-pasting-in-an-excel-filtered-list/
-
Anonymous
commented
Please take this up!
-
Fiona Ottley
commented
This used to work in older version of excel, and caught me out a couple of times when I unsuspectingly used it in the latest version.
-
CTL
commented
This feature must be removed from Microsoft 365. How was this chosen as a new default?? And how is there no way to change the default to paste only in visible cells?? Why would anyone filter data and then want a copied value pasted in a hidden cell??? What use to be a quick-key operation just turned into a time-wasting several step process. For users that spend hours in spreadsheets, this is a huge non-value-added waste.
-
rayvic
commented
It's 2020 now.
DEAR, DEAREST MICROSOFT, can you make 'paste to visible cells' a default behavior of paste? -
Thilak Nagarajan
commented
The below trick works precisely and it's not in any of the answers shared. Please have a check.
1. you will be having the Filtered values with formulas in a column which are visible.
2. Do not go to visible columns using Ctrl + G & all. Just select the filtered column with columns using CTRL + C.
3. You would may need to Paste in the same columns that removes just the formulas but the values should remain same. Therefore, Now you need to go to - Paste Special Window - Select Values - Press " CTRL + ENTER" . Not just ENTER.
4. This will just make the formulas vanish in the columns filtered and just make it as value and the other values in the hided cells of that filtered columns remains unaffected.
5. This also works for copying and pasting the values for removing the formulas in same column itself as well as other destination column ranges.Please share your feedback.
Thank you.
Thilak N
-
dj
commented
Even selecting "visible only" when pasting doesn't work. This does not help productivity. What's going on with the MS product management? This is really unfortunate.
-
Brian Basarich
commented
Microsoft, it's ridiculous this is still even an issue. I can't even think of a case where I'd want to paste data into cells that aren't visible, which means poor design. Come on, man!
-
sharath
commented
Excel should allow to copy & paste the values even when there is filter that is used. This will make our work faster, as we need not un-filter the cells to copy and paste the values. Currently we can't make range of cells static by copy pasting when there is formula in those cells.
-
Anonymous
commented
This would be very, very useful.
-
Vitalii Hrtysenko
commented
I support that the problem of losing some data because of pasting into filtered area must be solved.
-
Kapil Lovanshi
commented
UNABLE TO DONE THIS WORK EASILY PLEASE PROVIDE ANY HOW SOLUTION OF THIS ISSUE AS SOON SA POSSIBALE
SAME PROBLEM FACING WHICH IS MENTION IN BELOW.
I find it a problem that if I copy a filtered area for example (E2, E8, E9) and I try to paste in the same filtered area one or more column later, it will paste into E2, E3, E4 -
Ken
commented
Set default to Copy - paste visible only, opt into paste hidden
The Default setting should be able to be set for visible only with paste special being for hidden.Really annoying when working with filtered lists and pasting on everything hidden and not knowing / forgetting.
-
Matt
commented
I have excel business and I do NOT have a button that i can check for paste to visible cells only. I estimate and this is a big problem for me costing me hours of time. They need to fix this ASAP or I'm going back to apple numbers.
-
Roy
commented
Speaking of users, doing something on a set of presented data and NOT affecting anything "in between" is so utterly basic that no user should expect anything else.
Does anyone know of an application that uses Ctrl-C to delete all the files in the directory the working file resides in? No. That'd be silly. Ctrl-C copies to the clipboard (Windows world anyway, can't speak about Macs) or does nothing at all, in anything except pretty restricted use software.
Picture applying this concept to a database. It presents you customer purchase records for three months, for a single customer. You use a function it provides to change three of those that happen to be that customer's sequential purchases on, say, Jan 1, Jan 26, and Mar 4. After the user changes whatever was being changed all at once, you find that ALL the records for ALL the customers purchasing between Jan 1 and Mar 4 have the same change written to them as well. To be clear, they were NOT presented in the view, just the single customer the change/s needed made for was presented.
See? It's just ridiculous that it would have been written to allow this.
It actually suggests that Excel is NOT filtering the records, just their presentation. So instead of operating on the range desired, finding the appropriate records, DISCARDING the others, and presenting a true subset of records, Excel would seem to be keeping the entire set active, never ITSELF regarding only the filtered records as a set, but rather always considering the entire set.
Remind you of anything recently proudly touted?
Those Ober-Functions, the new "spill" functions... they do not give you access to a subset of records, no matter what they suggestively sound like they do. They give you a filtered selection (call it spilling if you like, because it doesn't write them for real anywhere or give you access to them to operate on them, but it is simply filtering) TO VIEW but not to access.
So... IF the new spill functions are built on their filtering engine, and that's why they are limited to viewing only, not operating on, then 1) If they ever let us operate on their material, the same trouble might exist, and 2) Now that something else depends on the filtering engine, difficulties in adjusting any part of it become exponentially greater because a lot of fixes would now break the other thing.
And given the publicity about the new functions, that ain't gonna be allowed. Or risked.