In a large, complex Excel sheet, you can find the data meets your criteria quickly through the powerful function called Filter, while other data that doesn’t meet your need is automatically hidden by Excel.
How to screen out the data that equals/larger than/smaller a specified value
Using the Filter function, you can screen out all the data in your sheet that larger or smaller than a specified value. For example, I want to screen out all the student in this table that got a grade higher than 500.
1. Click anywhere of the table, then go to Data tab and select Filter in Sort & Filter section.
2. Then the drop-down list will be added to your sheet.
3. Click the small arrow beside “Grade”, choose Number Filters – Greater than… (according to your need).
4. Set your specific criteria in the popping out window and hit OK to confirm it.
5. Now the data you need has been singled out.
How to screen out the data that above or below average value
Most of the steps are the same except that you should choose Above Average or Below Average in the Number Filters menu. In my case I choose Above Average, and then I can get the list of students who have a grade better than average.