How do you filter an Excel sheet...

Joe 90 · 09-06-2009, 18:07

I've just been passed on a rather long and poorly organised Excel sheet.

Its our options for a new car, I want to be able to filter this list of around 600 rows.

First filter that'd be perfect would be to select all diesels so they could be copied onto their own sheet. Theres a much more manageable 266 diesels so i figure thats a good start.

Any ideas? I don't really know how to manipulate the search results.

Dymetrie · 09-06-2009, 18:11

So long as the columns are all in the right place (ie: option of 'Diesel' is all listed in a single column) then you want to select all, then tools->data->autofilter.

You'll get a drop down box at the top of each column and you can use that to filter what you see

Joe 90 · 09-06-2009, 18:14

yeah, thats the easy option.
this sheet doesn't like the easy option.

the cells in the column that I want to filter are all like;
(each line being the contents of one cell)

Code:

Seat Leon 2.0 TDi 140 Hatch 5Dr Sport Diesel 6sp
Renault Megane 2.0 16V 140 Hatch 5Dr Privilege CVT Auto 1sp
VW Golf Plus 1.9 TDi DPF 105 Hatch 5Dr BlueMotion Diesel

LeperousDust · 09-06-2009, 18:28

So its not really an excel sheet really, its a list?

Joe 90 · 09-06-2009, 19:12

no, its an excel sheet. the other columns are all details like costs, mpg etc...

i just had a though, if i filter like > 50 mpg i'll generally find all the diesels \o/

whoo.

Wryel · 09-06-2009, 19:31

I'm sure you could split that first cell into more than one column. Otherwise you could do a more advanced filter I think.

Edit: Data - Filter - Advanced Filter

I bet if you play with that you could find only the rows that have the word diesel in them.

kaiowas · 10-06-2009, 08:31

Turn on the autofilter as Dym said then instead of picking a particular option select "Custom" then in the top left box select "Contains" then type "diesel" in the top right box. That should filter the list to show you all rows where that cell contains the word diesel.

09-06-2009, 18:07	#1
Joe 90 Absinthe Join Date: Jan 2007 Location: Chester Posts: 2,345	How do you filter an Excel sheet... I've just been passed on a rather long and poorly organised Excel sheet. Its our options for a new car, I want to be able to filter this list of around 600 rows. First filter that'd be perfect would be to select all diesels so they could be copied onto their own sheet. Theres a much more manageable 266 diesels so i figure thats a good start. Any ideas? I don't really know how to manipulate the search results. __________________ 360 Blog \| Join GiffGaff \| Twitter

09-06-2009, 18:11	#2
Dymetrie A large glass of Merlot Join Date: Jun 2006 Location: Letchworth with a Lightsaber Posts: 5,819	So long as the columns are all in the right place (ie: option of 'Diesel' is all listed in a single column) then you want to select all, then tools->data->autofilter. You'll get a drop down box at the top of each column and you can use that to filter what you see __________________ Khef, Ka and Ka-Tet....

09-06-2009, 18:14	#3
Joe 90 Absinthe Join Date: Jan 2007 Location: Chester Posts: 2,345	yeah, thats the easy option. this sheet doesn't like the easy option. the cells in the column that I want to filter are all like; (each line being the contents of one cell) Code: Seat Leon 2.0 TDi 140 Hatch 5Dr Sport Diesel 6sp Renault Megane 2.0 16V 140 Hatch 5Dr Privilege CVT Auto 1sp VW Golf Plus 1.9 TDi DPF 105 Hatch 5Dr BlueMotion Diesel __________________ 360 Blog \| Join GiffGaff \| Twitter

09-06-2009, 18:28	#4
LeperousDust Bananaman Join Date: Jul 2006 Location: Liverpool/Edinburgh Posts: 4,817	So its not really an excel sheet really, its a list? __________________

09-06-2009, 19:12	#5
Joe 90 Absinthe Join Date: Jan 2007 Location: Chester Posts: 2,345	no, its an excel sheet. the other columns are all details like costs, mpg etc... i just had a though, if i filter like > 50 mpg i'll generally find all the diesels \o/ whoo. __________________ 360 Blog \| Join GiffGaff \| Twitter

09-06-2009, 19:31	#6
Wryel Absinthe Join Date: Dec 2006 Posts: 1,148	I'm sure you could split that first cell into more than one column. Otherwise you could do a more advanced filter I think. Edit: Data - Filter - Advanced Filter I bet if you play with that you could find only the rows that have the word diesel in them.

10-06-2009, 08:31	#7
kaiowas The Stig Join Date: Jul 2006 Location: Fightertown USA Posts: 1,458	Turn on the autofilter as Dym said then instead of picking a particular option select "Custom" then in the top left box select "Contains" then type "diesel" in the top right box. That should filter the list to show you all rows where that cell contains the word diesel. __________________ Anal Fish Porn