Forum Discussion
Using the FILTER function to filter out data
I have a large spreadsheet with over 3000 entries and multiple columns of data. I need to efficiently edit out a specific packet of data that repeats in this case its the phrase "inactive" for one specific column. What formula can I use that will parse this column and remove all entries that have "inactive" in this column? Thank you, if this was not clear enough I can provide more information.
BobbyBGud So by the 'filter button' I'm talking about the button on the right of this image:
so click on the table you want to filter and then make sure that button is highlighted as in this image
This will add drop down menus to each column as shown on the first row in this image:
Also in this image I clicked on the drop down in column C (Device) and you can see the options.
at the bottom (cut off on this image) is a list of all values in that column where you can unselect items you don't want to show.
You can also see the option for "Text Filters" and then a submenu of various options will appear for that method (e.g. Text value contains...)
- The FILTER function (like all functions) are designed to act on inputs to give you an output. So the FILTER function could be used to point at that table and output a new table (on that sheet or another sheet; where ever the cell with the function is located).
If you want to see that table without rows that have the "inactive" value in a particular column, activate the filters under the data tab and then in the corresponding column click the drop down and de-select that value or use the text based filtering options. This will keep the data complete and just show the rows without that value. If you want those removed then filter FOR that value and then highlight and delete and then unfilter (and prob sort to remove the blank rows) to see what remains.- Thank you for your response, when you mention the "corresponding column drop down" you lose me. When I click on the filter button under "data" nothing happens, how do I get to where it asks for the text I want to filter out? Sorry if this is elementary I'm lost on excel.
BobbyBGud So by the 'filter button' I'm talking about the button on the right of this image:
so click on the table you want to filter and then make sure that button is highlighted as in this image
This will add drop down menus to each column as shown on the first row in this image:
Also in this image I clicked on the drop down in column C (Device) and you can see the options.
at the bottom (cut off on this image) is a list of all values in that column where you can unselect items you don't want to show.
You can also see the option for "Text Filters" and then a submenu of various options will appear for that method (e.g. Text value contains...)
