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.
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.
b) A "Down Arrow" button appears in all column headers. Click on the Q4-2016 Column down arrow. Click on Number Filters -> Less Than.
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.
d) Table is filtered to show only those records where Q4-2016 Sales figures are less than the $3'900 target.
e) To clear the filter, under "Data" click on "Clear" in "Sort & Filter" section as shown below..
f) The entire Table is shown as below.