The AutoFilter tool is very useful for manipulating the data to find data subsets at the click of a button.
To turn the AutoFilter on:
- Highlight the data including the header column (if applicable).
- From the toolbar select Data > Filter > AutoFilter…(the downward arrows will appear for each heading)
Note: the filter can be turned on without highlighting the data and it predicts the data range (usually all the data on the worksheet).
Working example: You want to find out what Service Centres have a boiling point of higher than 80.
- Select the Boiling Point downward arrow
- Select Custom
- Select is greater than and enter 80 into the value box
- The data now only displays those rows that match the criteria. How this works is that the rows that do not match the criteria are hidden not removed so be careful when editing the data and dragging formulas as this is prone to overwrite data that is not in view without you knowing! It is advisable to copy the data from here to another worksheet and then edit it from there especially when you’re dealing with large number of rows.
- You can now sort in order of Boiling Point as follows:
To show all the data again:
- From the toolbar select Data > Filter > Show All