Using fill handle on filtered data

[Valerie]

I am new to Office 2007 and the new features in Excel. I am used to using
Excel in Office 2003. I'm trying to use a feature that I've always used in
Excel 2003 and for some reason the feature does not work the same way in
Excel for Office 2007.

I have several columns of text data, here's an example:

Test Name Test Level
Training 4
First 1
Second 2
Trainig 3

I created a pivot table and discovered that I mispelled the word "Training"
in the fourth row. So, I filtered the data for rows where "Test Name"
contains the word "Train". The results are:

Test Name Test Level
Training 4
Trainig 3

Then, I select the first instance of "Training" and dragged down to replace
the misspelled word "Trainig" with the correct word "Training". This used to
work just fine in Excel for Office 2003. But, much to my dismay, the result
of dragging the value down from one cell to the other resulted in also
modifying all of the rows that were hidden by the filter. The end result of
this operation is:

Test Name Test Level
Training 4
Training 1
Training 2
Training 3

Can anyone help me with this? I need to be able to use the fill handle
feature on filtered data without modifying the hidden data. Is there an
option I can use to make sure the fill handle feature is only applied to
cells that are not hidden?

Thanks!

 

[Roger Govier]

Hi Valerie

If you place your cursor in a cell within column A, before you make the
selection from the dropdown on that column, then the fill handle will behave
correctly.
If the cursor is place anywhere outside of the autofiltered range before
making the selection with the dropdown, then you do get the behaviour as
described.
It is a bug, MS do know about it.

Alternatively, you can choose Insert tab>Table to apply to your data, rather
than using Autofilter.
The Data table has the same dropdowns, and the autofill feature always works
correctly when you make a filtered selection and then fill down.