Forum Discussion

Copper Contributor

Nov 01, 2024

Solved

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 sp...

excel

Formulas and Functions

m_tarler
Nov 01, 2024
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...)

m_tarler

Bronze Contributor

Nov 01, 2024

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.

BobbyBGud
Copper Contributor
Nov 01, 2024
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.
- m_tarler
  Bronze Contributor
  Nov 01, 2024
  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...)