filter with condition in Excel

Q91. In Excel, how can I filter a table to get all records that have less than a particular value in a specific column?

For example, in the table below, I want to see only those records where Q4-2016 Sales figures are less than the $3'900 target that I set.

excel filter with condition

To do it in Excel, here is the answer:

a) Click on any cell within the data Table. Under "Data" click on "Filter" to turn ON filter.

excel excel filter with condition

b) A "Down Arrow" button appears in all column headers. Click on the Q4-2016 Column down arrow. Click on Number Filters -> Less Than.

excel excel excel filter with condition

c) In the "Custom AutoFilter" dialog box that appears, set the rule as required - in this case, it is less than $3'900. Click OK.

excel excel excel excel filter with condition

d) Table is filtered to show only those records where Q4-2016 Sales figures are less than the $3'900 target.

excel excel excel excel excel filter with condition

e) To clear the filter, under "Data" click on "Clear" in "Sort & Filter" section as shown below..

excel excel excel excel excel excel filter with condition

f) The entire Table is shown as below.

excel excel excel excel excel excel excel filter with condition

 

You can find similar Excel Questions and Answer hereunder

1) How can I get row count of filtered data?

2) Is there a way I can easily identify the duplicate values in a dataset?

3) How can I use SUMPRODUCT to summarize my raw data?

4) How can I find the count of records that meet multiple conditions in my raw data table?

5) How can I set the Source Data of charts using VBA?

6) How can I add and link a Chart Title to a cell value?

7) Can I add a Text histogram to Table data to make it visually appealing and easy to interpret?

8) How can I fill a series of data automatically?

9) I have data arranged in rows of information. Is there any way I can enter new data through a form instead of entering it directly in SpreadSheet?

10) Applying a countif formula only to visible cells in a filtered list in Excel

11) Vlookup to return max value from multiple hits in Excel

12) How do I find the median salary of employees with the same skillset in my Organization?

13) How to read a value from a cell in vba in Excel

14) How can I filter and copy only filtered data using VBA?

15) Given a raw data Table, how can I find the value of a field for a specific value of another field?