Who's good with Microsoft Excel 2003?

Fordulike

Esteemed Pedelecer
Feb 26, 2010
3,802
1,537
I've got some large Excel spreadsheets, where I want to isolate just the rows which contain certain words.
Is there a way of inputting my criteria, so that it shows just the rows with those words?
 

baldylox

Pedelecer
May 25, 2012
240
77
Hants/Wilts border
If your data has a header row, you can add filters using the data/autofilter menu. Then filter the column for a specific word.
 

trex

Esteemed Pedelecer
May 15, 2011
7,703
2,671
Excel is very complete and yet lacking that simple facility to select all rows containing certain word. Hit control-F to open up FIND, enter the word eg 'FRED', then control-A to SELECT ALL. If you just want to delete all rows containing FRED, then it's easy but if you want to do something else, then you will need to create a small macro.
Here are the steps:
http://pakaccountants.com/select-entire-rows-of-multiple-selected-cells-vba-macros/
 
D

Deleted member 4366

Guest
If you select your table first, then tell Excel to make it data. I can't remember how you do it in that version. The help will tell you. It will then give you the option of making the top row labels. You mustn't have a gap between the column headings and the data below them. Once you tell Excel that it's data, you'll see little arrows in the headings. When you click those, you'll get a pull down selection. Choose the one you want from any column and it will show only those data records. It's a lot easier to do than explain. It's about three clicks in practice. You have to set the pull-down back to "all" to see all your records again, or you can filter again within the filtered data by using another column. The filter also allows you to enter conditions, like greater or less than a value.

I think some versions of excel automatically see tables with a top row as headings as a data array, so you only have to put your curser in any box and click the data tab/menu at the top, where it offers you the autofilter, which brings the arrows into the top row, like Baldylox said.

Another useful trick is conditional formatting. You can select your table of data, click format and select conditional format. You can then turn the boxes green where the value meets a condition or turn it red if it doesn't. You'll see how it works. It's dead simple to do.
 
Last edited by a moderator:

jwm

Pedelecer
Feb 11, 2014
139
41
Hampshire
iif function using a cell reference for parameter (criteria word), if you want to pick up more than one word you may have to do it another way....


John
 

Advertisers