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
11 comments
-
Vitalii Hrtysenko
commented
I support that the problem of losing some data because of pasting into filtered area must be solved.
-
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.
-
Olivier Hault
commented
Reported since at least July 2010, Offbug 48323 and still not fixed. Excel is mostly the latest spreadsheet still not being WYSIWYG. This is also a severe security issue, because users cannot even see they are corrupting their date while pasting values.
-
Roy
commented
Yeah. No brainer here.
If a feature presents only some of a data set, it should work on only those items if changes are allowed.
It's more than just pasting (which my boss does any time I present something with filters available, no matter how many times I've told him). Formatting the filtered material is an issue too. I suspect there are other problems as well.
Find and Replace's replacing comes to mind as something that may work differently (which is what we want filtering to do too... work differently!) but basically takes a set of cells and does something to them. I'd be shocked if I hear that it actually tries to make the change to all 300,000 cells I tell it to look in and deals with an error from 299,968 of them in order to make the 32 desired replacements. I'm sure something better is done, like selecting the subset which needs changed by looking at every cell, then attempting to change only the 32 cells in the... wait for it... FILTERED
set of 32 cells.Features are great, but every grown-up in the world knows you get a nice thing mostly right if you try, then gradually make it close to perfect by modifying it as overlooked or left behind things come to light in real usage. That's what we want here. Not asking something to go a whole different direction from its conceived usage. Just to evolve to perfection after testing in the real world.
Isn't that what all the "Windows-phone-home-with-stuff-you-thought-was-private" stuff is all about? Seeing how features of Windows (and a lot more I imagine) are used so they can be improved and evolved to fit how people use them? Why not here too?
-
Joann Lawler
commented
Anonymous: Where do you see "Visible Cells Only"? It's not in Paste Special.
-
Anonymous
commented
If you choose Visible Cells Only and then paste, it will only paste to the visible filtered cells.
-
Daryl
commented
It's bizarre that you can *copy from* filtered cells only but there's no easy way to *paste to* filtered cells only.
You'd think the need to do one would automatically suggest the need to do the accompanying reverse action.
-
DC
commented
I second this. The fact that this issue still exists blows my mind. Worst of all it didn't always work like this.
-
dov
commented
click on the filtered range, then click alt+; it selects the filtered rows and copies them
-
Dave McCulloch
commented
To clarify, the problem is with pasting multiple rows into an implied range of rows that contains some hidden rows. Although copying a range will copy only its visible rows, pasting a range will paste only into contiguous rows, regardless of whether they are visible or hidden.
This problem has been reported MANY times. It is surely one of the most common problems encountered by users as they learn Excel. It is also one of the most common and frustrating problems encountered by VBA programmers when attempting to write fast and efficient code. Reading or writing cells is VERY slow, but accessing a range of cells is incredibly fast. VBA can read a range of visible rows (very quickly!), but it cannot write those rows back as a range. Each row must be written separately (or contiguous rows must be identified, but using associated source rows from an array is very tedious).