In addition to traditional search through the Find tool, Excel tables can be searched and browsed in different more powerful manners (please note that these tools may slightly differ on a Mac or in old versions of Excel):
– To sort the entries alphabetically or numerically according to a given column, click the little arrowhead in the header of this column and select the relevant sorting option.
– To filter the entries according to values in a given column, click the little arrowhead in the header of this column and check the desired checkboxes (1). When you need more advanced filtering options, or when the number of different values present in this column is too large for Excel to display, you can instead use either the search field (2) or the powerful “text filter” menu (3) present in the pop-up window. Filtering can be applied simultaneously to several columns, allowing for refined searches (filters in different columns behave as “AND” Boolean operators).
– Filtering the table across several columns while requiring a “OR” behavior (rather than a “AND” behavior as before) is a little less straightforward. A typical case would be you wanting to retrieve all ORFs related to your favorite XYZ protein: you may want in that case to get all entries having the term “XYZ” in either the gene name column, OR aliases column, OR GO terms column, OR gene family column. For that, use the advanced filtering tools available from the data/données tab of the Excel menu bar (4). First add additional rows to the table, which will serve as “criteria rows” (5). Then fill in the fields in the advanced filtering pop-up window (6) as indicated in the figure below. Entering criteria in different rows triggers an “OR” behavior, and entering them in the same row triggers a “AND” behavior. Any combination is possible. The asterisks in *XYZ* will get any XYZ occurrence at any position in the cell. An example file is available here.