General

Problem with copying filtered data in Excel 2007 + workaround

by Bastien 49 Comment

Excel 2007 ribbon: Data /> FilterI just got an email that says you cannot copy only the filtered data in Excel 2007. If you use the autofilter to make a selection and then you only want to copy the filtered rows, Excel 2007 copied everything, even the hidden rows. This is different from the way Excel 2000-2003 works.

You can recognize when it is going to work correctly by double, moving striped-lines:
Excel 2007, copy filtered cells only

If the double-lines don't show, the hidden rows are copied too:
Excel 2007, copy filtered cells only not working as expected

If it doesn't work, here are some workarounds:

Workaround 1
If you convert your table to a 'table' it works as you would expect. Select the cells and then in the Excel menu choose:
Insert > Table. After you've done this is works just as in the previous Excel versions.

Workaround 2
Make your selection, then press F5 (go to..) then click on the "Special..." button and choose "Visible cells only". When you now copy, only the visible result of your filter will be copied and pasted.

The ultimate workaround (and fix?)
I can reproduce the bug in a certain situation. If I have my data, select the header row and then apply the filter, the filtering works but it copies all the data. If I select the entire data-range or a single cell in it and then apply the filter it works as expected. This behavior is different from Excel 2000, 2002/XP and 2003.

Conclusion
The way it works entirely relies on which cells you have selected before you apply the filter. My advice is to select the entire range and then apply the autofilter.

49 comments

Kanwaljit Singh Dhunna

Hi Bastien,

May the couple live 1000 years :)

The above bug affects when the filters are applied using the entire row / or a specified range within the data (not only the Header row). And it affect only for the First time the Filters are used after applying them in the way specified before. If we reapply the filters or reuse them for 2nd time, it will behave properly. So the simple workaround is : click on a cell anywhere within the range, apply the filter, use them and have a good night's sleep.

Good Night

Regards
CA Kanwaljit Singh Dhunna

Mal

You are a legend. My head was melting trying to remember how to do this. Take a bow!!

Nico

genius, i applaud you. I've been dealing with this issue for the last couple of weeks, and it's very irritating when something works only when it wants to. And nobody in my office believe me about this problem. I hope they update to office 2007, then they'll come to me for rescue (and i'll do my evil laugh of course).
thanks a lot!

Courtney

There is a way to permanently turn this off in Excel 2007 so that selecting when using an autofilter behaves in the same way as Excel 2000-2003. Go to Excel Options > Advanced and then uncheck "Extend data range formats and formulas" under the Editing Options. Then you won't need to use a workaround every time.

I miss Office 2003

Thank you soooooo much for this information!

As with everything else in 2007 it has taken me forever to figure out how to work it and Microsoft offers virtually no assistance. I keep begging my IT person to reload 2003 but he said because of an Outlook bug it isn't possible. Yeah.

Now if I could just figure out why 2007 isn't recongnizing my VBA code that works fine in 2003.

Tanya

I think I'm perhaps misunderstanding the solutions. Even when excel copies 'only the filted data' the other data is copied, just hidden. So if I try to calculate based on it, I end up including the hidden data. Is there a way to copy only the data for which you have filtered, without the hidden rows? I have tried all of the solutions above.

Bastien

Hello Tanya,

Try it again but then copy the data below your table or to a new worksheet.

Kind regards,
Bastien Mensink

lino cardoso

The only way that resulted with me is the

Workaround 2
Make your selection, then press F5 (go to..) then click on the "Special…" button and choose "Visible cells only".

thks for the contributes

lino cardoso

Working

You can tell Excel 2007 to select visible cells only by going to Home > Find & Select (under the Editing menu) > Go To Special. From there you click on the "Visible cells only" radio button and click ok. This is a pain, so there is a keyboard shortcut for this. When you have your autofilter on just hit ALT + ; (semi-colon) and all visible cells in the entire workbook will be selected. Still not exactly the same as 2003 used to work, but that's Microsoft for you.

Vi Thang

Workaround 3

Apply the filter selection twice.

eg for the example shown above
for column C.
apply filter for 3 then apply filter for 3 again

Your selection should act like it used to in 2003

The same concept works in macros as well
eg

Selection.AutoFilter Field:=11, Criteria1:="s*", Operator:=xlAnd, Criteria2:="t*"
Selection.AutoFilter Field:=11, Criteria1:="s*", Operator:=xlAnd, Criteria2:="t*"

Jon P

Thanks everybody, this has been driving us crazy!!

Umar Khan

Courtney HAIL!

All the workarounds work for me, but the best one is as courntey said:

Go to Excel Options > Advanced and then uncheck 'Extend data range formats and formulas” under the Editing Options. Then you won't need to use a workaround every time.

Now you dont have to worry about anything ever again, everything is like 2003!!

YAY! Finally!

Seanc

I've been looking into this problem, and playing with the various work arounds listed here. The solution for me is "The ultimate workaround (and fix?)".

If I select the header cells where I want the filter, and then apply the auto filter, copying any filtered data also copies the hidden items.

If I select the complete range, or a single cell in that range, and now apply the auto filter, when copying filtered data I now only copy the visible items.

This appears to be the case whether or not the "Extend data range formats and formulas" is selected, and seeing as this can be a useful function then I will leave it ticked for now.

The reason I often select the row where I want the filters to appear is that I sometimes have data in the row above it, and Excel does not always want to use the same row as me! It looks like in these cases I will have to format as a table.

Some things just don't need changing!

Sean.

dennis

even I unchecked 'Extend data range formats and formulas”, it does not work on my excel 2007. Reapply works and visible cells workaround work as well. I'm missing my excel 2003 …

Felipe

Hi Focus,

I try to figure out how resolve the problem with filters in excel 2007 (ver. 12.0.6300.5000) SP1 MSO (12.0.6213.1000).

I filtered some list by some value, the list obsvously reduce from orignal line numbers, now when I try to copy a value to the interval, means only these filtered cell should be update with the new value, but does not work like should.

Excell 2007 applies the new value to all cell (not listed on filter) between the range of firt occurer of criteria filter and the last

Lets supose the list has type of car and collors of the cars, so I filterd "ferraris" and see how many ferraris has in my filtered list and the collors of theses cars. Now I can change de color (value of cell) of all ferraris, and when I do that, excel applies not only on ferraris, but in porches, bmw, jaguars, etc.

If you know some trick ou underground solution, pls don't hesitate to contat me.

Best Regards,

Felipe Penteado

frank

Yes I have used all the solutions above as well,
I thought the Go to Excel Options > Advanced and then uncheck 'Extend data range formats and formulas” would be perfect BUT IT DOES NOT WORK
Selecting visible cells DOES NOT WORK
The ONLY solution that worked was how you turn on filters in the first place
Example:
turn on the filters by clicking on a single cell in the row that the filters are to appear (top row usually)
(do not highlight whole row) and then turn on the auto filters (I always use keyboard shortcut Alt-d-f-f)
Filter as required
Cut/paste
Then it worked as it did on 2003 which i think is a variation on how others achieved it above though maybe by accident.
So it definitely is a bug if how you apply the filter determines what data is copied

Marcel Meth

I am trying to write a formula based on the visible cells. Thus if I have 10 years worth of data, but someone has filtered out the month of Nov 2008, I would like to show a sum of all the values in November. Is there a way to create a dynamic range that refers to only the visible cells?

Bastien

Hello Marcel,

If the list is filtered you can use Excel's worksheet function =SUBTOTAL(), please have a look in Excel's help on this function.

Kind regards,
Bastien

kamal

thanks special of f5 is working perfectly

Aniket

When I try the workaround 2, it tells me:
"MS office excel cannot create or use the data range reference because its too complex…"
I dont know what to do…

Bastien

Hello Aniket,

I haven't seen that message before, but it might be because the is too many data. What might help in that case is that you
first sort the data on the column(s) that you used for the filtering and then try it again.

Kind regards,
Bastien Mensink

Thilanka

Thanks for the help. This was a really annoying thing for me.

Ruma

Oh…Bastien,

Thnaks a ton…I really need this…Thanks..thnks..thkns…a thousnd times..!!! it was irritating …oh thnkq thnkq…thnkq….thnkq..thnkq thnkq

Alex

Hello!
You can highlight just 5 rows instead 1 row, and then apply filtering.
It will work.Least, i have worked this issue .

Nick

I tried most of the above suggestions to no avail…but a colleague finally helped me to get this working – so I submit it in case it helps someone else!

– Open spreadsheet in Excel 2007
– Apply the required filter
– Click on the first row number that contains the data. in my case, that meant clicking on the '3' for row 3
– Click Ctrl-Shift-down arrow to select all rows
– if you want to keep the visible data, click copy and paste to another worksheet. If not, just ignore this step
– hit 'delete' key
– Remove the filter by choosing 'select all'. The hidden data should now appear…but will obviously be located in chunks
– To make contiguous, just 'sort' in a way that suits you.

This may be the same as others have suggested…but I just couldn't get it working.

Hope it helps for someone

Khan

Thanks for your valuable help

gmf

Awesome – thanks for this!

Luke

thank you so much i was sure i was going mad, i'm not alone regarding this problem.

Jesper

I can copy only selected cells, that is no problem. But I wonder if it is possible to paste only to the same selected rows as well. An example:

I copy the cells A3 and A5 (in a filtered list)
Then I want to copy this to B3 and B5 (in the same filtered list) –

but either I get the values to B3 and B4, or I get the message "That command cannot be used on multiple selections" (when I try to select only cells with the "Mark Visible cells only” option)

Hope anyone can help me on this, has been bugging me for a looong time…

Kavitha

Hi,

Another solution is to copy the filtered data into a different workbook. I tried all the sols mentioned, but nothing worked for me. I was trying to paste filtered data from sheet1 to sheet2. Finally i tried pasting it into a diff workbook and it worked.

-Kavitha

Irfan

Selection of entire range before applying filter is a best solution. It really behaves like 2003.

Thanks to all,

Gabor

Use the filter, mark rhe cells you want to copy, right click with your mouse ( not Ctr+C !!! ), copy and paste it .
This way worked.

Gabor

Rudy Thong

Wow, it works :)
Thank you so much :)
This little thing has made my team dizzy all night long…

M Bison

I hate hate HATE Microsoft. Why do they have to change this? It worked perfectly in 2003.

None of the solutions above worked for me. It keeps pasting every f**kin' thing!

M Bison

I tried all the workarounds and unchecking "Extend data range formats and formulas” does nothing.

Oh and if you follow Nick's solution step by step, it deletes everything and leaves a blank spreadsheet.

The ONLY thing that has worked is:

– Paste the filtered cells into word
– Copy it all from Word back into a new spreadsheet

CannotBelieveNotFixedin07

i can't believe Microsoft has not fixed this bug yet. I sometimes wonder if Bill Gates has ever used a spreadsheet!!

Most annoying is copying and pasting in autofiltered list. Excel will paste the values to rows that are filtered out unless you control Z the first time then select Paste again.This is confirmed in excel 2003 and can't believe not fixed in 2007. (you can see this when you unfilter the list and every row in between has copied data into it instead of just the ones visible at the time of the copy) (i have destroyed lots of work accidentally like this)

Many other annoyances that have not been fixed in all this time in development!

r75

Hello,

I am glad to hear that I am not the only one with this problem. And yes, I also hate MS. With this bug, it made excel useless for me.

So I managed to get the copy thing selecting the visible range only. Pasting into a new sheet is fine.

But I did in previous versions was to copy a block onto a filtered range. What excel does, is just pasting my selection continously, therby also populating the hidden row. Now way to get this right except my sorting things around. But this comes close to doing everything manually as well, so garbage. I hate workarounds, especially if it worked with older products.

By the way, the ribbon menu change in 2007 is also totally ridiculous. Why do I need to re-learn where to find the same things, without any add-on. It's like someone comes to my flat and rearanges everything without really placing new furniture. That's of no use.
One example: To set the transparent color in of a picture in ppt, Inow need some 5 clicks, which took me much less in the older version. Everything is just 25 klicks away, awsome!

Oleg

Thank You VERY Much for the answer!

Austin

Of all the sources I've gone to for Excel issues, this was the best – clearly laid out and easy to understand problem and solution. Perfect.

Thanks!

Bikerbill7

I, like many here, am still having trouble copying filtered cells, even after all these suggestions. Here's what I figured out, and it seems to be quite easy. I have column A filtered, and I want to copy the cells in column G (contains a formula) to column H (Paste Special, Value). I highlighted all of the cells in G and hit Ctrl C (copy), and I see the dashed lines around each cell. I right click H2, Paste Special, Value, OK. I can see the paste didn't work correctly. I hit Ctrl Z (undo), and the dashed lines around the cells becomes dashed around the GROUP of cells. Right click on H2, Paste Special, Values, OK. All of the values are copied correctly.

Pat

OK, I am trying to copy, via a macro, the first row of a filtered sequence (I have several filtering functions to do). Let's assume that I have 15,647 rows of data (by 9 columns) that I want to use a macro to run. When I auto-filter today's data, the first returned row is Row 477. I want to copy five columns in that Row 477 (Columns B, C, D, E, and F). My cursor always starts in A1 before every auto-filtering sequence. I am trying to write a macro to always get the data in the first row of the visible, filtered data. Since each day's data is different, the first filtered sequence will not always be on Row 477, therefore I can not be specific on the Row designation. Does anyone have suggestions as to how to build in the macro to always move to the first visible, row of a filtered sequence when that row # will be dynamic from day to day?

Thanks!

Pat

Sean

I recently upgraded from Excel 2003 to 2007. I was very surprised with the way that Excel 2007 handled a copy and paste of filtered rows. Seems much different from the way that 2003 worked, but I don't have ready access to 2003 anymore so I was unable to confirm.

In the example below, assume that row, columnA and columnB are header values. Let's say you want to filter columnB for non-blank values or values > 50. I copy the filtered rows to get B5 and B8 to the clipboard and want to copy them to columnA since I want to overwrite A5 and A8. However, by default 2007 seems to paste to A5 and A6 which seems odd. I really can't believe this is the way that Excel was built to work. It seems so illogical.

To workaround this to get a copy to A5 and A8, do the following.

1. Apply your filter
2. Copy the filtered rows. In this case, B5 and B8
3. Paste to a free column at row 5. On the first paste, you will only see the first number (80)
4. Ctrl-Z to undo
5. Paste again, now you will get B5 and B8 in a free column such as F5 and F6
6. Select the cell A5
7. Right click and select Paste Special / Skip blanks
8. Now you will get B5 to A5 and B8 to A8

I still am looking for a solution that will work when A6 and A7 have values that do not meet the filter criteria. I cannot seem to get a paste of just the filtered values without also getting the values between the first and last filtered row. If I find a solution to this, I'll forward it along.

demando

that's it men. thank u so much. my alternative way, but a little bit cranky, was to copy related items to excel 2003 workseet and from then copy it to excel 2007 sheet. it was successful also:)))

Kothainayaki K

Thanks for a lot for the solution! :)
It saves lot of time for me while copying the filtered data from excel 2007.

NovaisPT

I still have a problem.
I want to make a sum of only the visible cells of a column, like the value that appears on the bottom right side of excel: Average; Count and Sum
The Workarounds work if I only want to select copy and then paste the data, but when I type:
=sum(
and then try to select only the visible cells, It still selects the hidden cells.
How can I solve this problem?

bersever

for copying filtered data problem in excel 2007, service pack 2 solves the problem

Zulfi Mardanov

THANKS INDEED!
IT WORKS AS WELL!!!

Muthu

Hi,

I have a problem in Excel 2007. Some of my friends e mailed me a excel file
which I was not able to save again, cut/copy and all. I checked it was not in'read
only mode. Can you suggest some solutions ?

Muthu