April 26, 201511 yr 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?
April 26, 201511 yr If your data has a header row, you can add filters using the data/autofilter menu. Then filter the column for a specific word.
April 26, 201511 yr 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/
April 26, 201511 yr 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.
April 26, 201511 yr 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
Join the conversation
You are posting as a guest. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.