Excel Function to Exclude rows based on certain values

Question

I excluded rows in an excel table based on certain values

For example:

I need to exclude all rows if column A is equal to any of these numbers ( 5840,4302,4432, and so on)

The table data will be huge to filter only the data I want.

Answer 1

One method is to combine the FILTER() spreadsheet function with the Excel Table feature. NB. To do this, you'll need a recent version of Excel. Utilizing a Table adds some additional practical usefulness (such as automatically adding rows and allowing reference by column name).

The OP's input data may already be a Table, if so, this first step can be skipped.

1. Put the input and filter list into tables. Excel help page. After the table has been created I have used the Table Design menu (which appears in the menu bar when a cell in the table is selected) to turn off the row banding format and header filters. This is also where you can rename the Tables. I have named them "Input" and "Exclude"

2. For the filtered output, choose where you want the output to start (cell H3 in my example), and enter a formula to copy the headers: =Input[#Headers]. Of course you can copy and paste the headers manually if you like. Here I've used the Format Painter to copy across the cell formats for the headers.

3. In the next cell down (H4 in my example), enter this formula: =FILTER(Input,(LEN(Input[ID])>0) * ISERROR(MATCH(Input[ID],Exclude[IDs to exclude],0))).

You should be able to add or delete new rows (right-click in the Table and choose Delete) in both the Input and Exclude tables, and the output should react (if you have Calculation set to Automatic).