I to exclude rows in a 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)

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

CodePudding user response：

One way is to exploit Excel Table feature together with the FILTER() spreadsheet function. NB. You will need a relatively recent Excel version for this. Using a Table provides some extra useful functionality (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).

NB. The Output range is NOT a Table. Excel doesn't let you convert dynamic ranges into Tables.

EDIT: If you don't want to use Tables, you can simply supply the ranges as the parameters to the FILTER function.

In this example =FILTER(B4:D13,(LEN(B4:B13)>0) *ISERROR(MATCH(B4:B13,F4:F5,0)))

CodePudding user response：

Excluding rows in an Excel table based on certain values can be done using a combination of filter and conditional formatting functions. Here are the steps:

1. Select the entire table including headers
2. Go to Data > Filter
3. Click on the drop-down arrow of the column that you want to filter (in this case column A)
4. Select Number Filters and then Does not equal
5. In the box that appears, enter the values you want to exclude (23, 13, 43, 56) and click OK
6. The table will now show only the rows where the value in column A does not match any of the values entered in the filter
7. Now you can use Conditional Formatting to hide the filtered rows.
8. Select the entire table, go to Home > Conditional Formatting > New Rule
9. In the dialog box that appears, select Use a formula to determine which cells to format
10. In the formula field, enter the following: =IF(COUNTIF(A1:A1000,A1)=0,"hide","")
11. Select the format you want to apply when the rule is true, for example hidden
12. Click OK

This will apply the rule to the entire table and will hide all rows that match the values entered in the filter. Now you can have a clean table with only the data you want to see.

It's important to note that the formula used in step 10 should be adjusted to match the range of your data. The example formula uses A1:A1000 as the range but you should replace it with the range of your