Paste Special on Filtered Data

[MMGroup]

Excel 2003, SP2, Windows XP
Setup: Two columns, as follows:
Pet Cost
Dog
Cat
Dog

Filter on Pet = Dog
Somewhere convenient, enter this formula: =5/2
With filter on, copy the formula and paste special, values into the Cost column
Turn the filter off
The cost column for Cat has been overwritten.
Whyzat?

 

[pgc01]

Hi MMGroup

It doesn't matter if the filter is on or off as far as the paste is concerned. The command paste works on the selected area, filtered or not. You are selecting both visible and non-visible cells. What you have to do is to select correctly the range.

After you filter on pet, select the range where you want to paste the values as you did before, but before the paste do: Edit>Goto, click on Special and check Visible cells only. Now the paste should work as you want.

Hope this helps
PGC

 

[Richard Schollar]

There's a funky "Select Visible Cells" toolbar button in the Edit menu from View>Toolbars>Customize which you might find useful if you have to do this a lot.

Richard

 

[MMGroup]

Thanks! (Added detailed instructions)

Thanks for the tip. Here is the detailed setup.
View | Toolbars | Customize
Command Tab
Category = Edit
Click Select Visible Cells
Drag this button to any of the visible toolbars
Close this window

Setup the spreadsheet as above, filter on dogs
Select the cell with the formula, then copy
Select the two visible cells
Click on the Select Visible Cells button on the toolbar (wherever you put it)
Right-click, select Paste Special, Values
The cat row will not update.

I think this is a bug in Excel for this reason:
Do the same setup (so the first column is filtered on dogs), go to the formula cell, copy, then paste (not paste special). The formula is not copied to the hidden cell. I guess it makes sense either way, but it does not make sense both ways.

 

[babycody]

Keyboard shortcut for selecting visible cells: highlight range and press Alt and ; at the same time.