A question from Mike in Oklahoma City. Excel is supposed to copy only the visible rows in a dataset where AutoFilter is turned on. However, sometimes this does not work. Episode 445 shows the simple shortcut to make sure that you have only the visible rows.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen. It's February 15, 2007. if you happen to be in Canada or Australia Make sure to catch me on call for help tonight in Canada on the east Coast 6 o'clock on G4 Tech TV.
ln Australia we're on the how to channel. I think that's like 11:30.Although I'm not sure the time zones there in Australia.
Today we have a question that came in from Mike in Oklahoma City.
Mike was talking about the auto filters.
We talked about auto filters last thursday.
He says sometimes when he has auto filters and copies the filtered rows only the filtered rows come along, other times he gets all of the data.
So let's take a look. I've turned on auto filters here by going to data.
Filter auto filters and once that's turned on I can choose to see just certain records so let's say that I asked for Just the records for one particular customer.
Now once the auto filters are turned on I should be able to select just those rows Use Ctrl C Go to a new worksheet, or just a spot on this worksheet and Ctrl V to paste And we should get just the filtered rows.
Now for some reason Mike is finding that occasionally excel will bring all of the rows along and I'm not sure exactly what would cause that.
I've tried a few different things I've tried including Data that wasn't in the filtered selection, but I can't really seem to replicate this behavior.
but however, If this is happening to you, one sure way to make sure that you're going to get just the filtered rows is use something called visible cells only.
The way to get to visible cells only is to use the "Edit" menu.
Choose "Go to" at the bottom of the Go to dialog box choose the "Special" button and then choose visible cells only Click OK and now even though I've selected 548 rows of data excel has chosen only the visible cells.
I can now use Ctrl C to copy.
Go to a new row Ctrl V to paste.
There's a great shortcut for visible cells only.
If you select the entire data set and type Alt ; it does the exact same thing as edit go to special visible cells only.
Ok great Shortcut, remember.
Mike thanks for sending your question in. if you'd like to have your question answered on a future netcast several different ways to go you can leave me a voicemail at either the 800 number in the US, our number in London or if you have Skype you can use Bill Jelen or Simply send me an email to Bill@MrExel.com hey, thanks for stopping by. We'll see you tomorrow from another netcast from MrExcel.
I'm Bill Jelen. It's February 15, 2007. if you happen to be in Canada or Australia Make sure to catch me on call for help tonight in Canada on the east Coast 6 o'clock on G4 Tech TV.
ln Australia we're on the how to channel. I think that's like 11:30.Although I'm not sure the time zones there in Australia.
Today we have a question that came in from Mike in Oklahoma City.
Mike was talking about the auto filters.
We talked about auto filters last thursday.
He says sometimes when he has auto filters and copies the filtered rows only the filtered rows come along, other times he gets all of the data.
So let's take a look. I've turned on auto filters here by going to data.
Filter auto filters and once that's turned on I can choose to see just certain records so let's say that I asked for Just the records for one particular customer.
Now once the auto filters are turned on I should be able to select just those rows Use Ctrl C Go to a new worksheet, or just a spot on this worksheet and Ctrl V to paste And we should get just the filtered rows.
Now for some reason Mike is finding that occasionally excel will bring all of the rows along and I'm not sure exactly what would cause that.
I've tried a few different things I've tried including Data that wasn't in the filtered selection, but I can't really seem to replicate this behavior.
but however, If this is happening to you, one sure way to make sure that you're going to get just the filtered rows is use something called visible cells only.
The way to get to visible cells only is to use the "Edit" menu.
Choose "Go to" at the bottom of the Go to dialog box choose the "Special" button and then choose visible cells only Click OK and now even though I've selected 548 rows of data excel has chosen only the visible cells.
I can now use Ctrl C to copy.
Go to a new row Ctrl V to paste.
There's a great shortcut for visible cells only.
If you select the entire data set and type Alt ; it does the exact same thing as edit go to special visible cells only.
Ok great Shortcut, remember.
Mike thanks for sending your question in. if you'd like to have your question answered on a future netcast several different ways to go you can leave me a voicemail at either the 800 number in the US, our number in London or if you have Skype you can use Bill Jelen or Simply send me an email to Bill@MrExel.com hey, thanks for stopping by. We'll see you tomorrow from another netcast from MrExcel.
