Preparing Data
To make the most out of Excel’s filtering capabilities, your data should adhere to a few basic formatting guidelines:
- There should be no blank rows or columns. The occasional blank cell is acceptable.
- There should be a header above every column.
- Headers should be in only one row; otherwise Excel will get confused and be unable to find the header row on its own.
Applying a Filter to a Dataset
To activate the filtering option, select a single cell in the dataset and use one of the following:
- On the Home tab, select Editing, Sort & Filter, Filter.
- On the Data tab, select Sort & Filter, Filter (works only in Excel, not Excel Starter or the Web App).
- When
a dataset is turned into a table (Insert, Tables, Table), the headers
automatically become filter headers. This is the only method for
turning on filters in the Web App.
|
It
is very important to select only a single cell because it is possible
to turn on filtering in the middle of a dataset if you have more than
one cell selected.
|
In Excel and Excel Starter, the Filter button is a
toggle button. Click it once to turn filtering on and click it again to
turn filtering off.
When a filter is applied to a dataset, drop-down
arrows appear in the column headers. One or more selections can be made
from each drop-down, filtering the data below the headers. Filters are
additive, which means that each time a filter selection is made, it
works with the previous selection to further filter the data.
When filters are in use in Excel and Excel Starter,
the numbered row headings turn blue. In all Excel applications, an icon
will replace the arrow on the column headers that have a filter
applied. Although it may look as if the filtered rows are deleted, they
are only hidden. After filtering is cleared, all rows reappear.
Clearing a Filter
A filter can be cleared from a specific column or
for the entire dataset. To clear all the filters applied to a dataset,
use one of the following methods:
- On the Home tab, select Editing, Sort & Filter, Clear.
- On the Data tab, select Sort & Filter, Clear.
- Turn off the Filter entirely using one of the following methods:
On the Home tab, select Editing, Sort & Filter, Filter.
On the Data tab, select Sort & Filter, Filter (works only in Excel, not Excel Starter or the Web App).
To clear all the filters applied to a specific column, use one of the following methods:
- Click the filter drop-down arrow and select Clear Filter From column header.
- Click the filter drop-down arrow and select Select All from the filtering list.
- Right-click a cell in the column to clear and select Filter, Clear Filter From column header.
Reapplying a Filter
If
data is added to a filtered range, Excel does not automatically update
the view to hide any new rows that don’t fit the filter settings. You
can refresh the filters settings so they include the new rows through
one of the following methods:
- On the Home tab, select Editing, Sort & Filter, Reapply.
- On the Data tab, select Sort & Filter, Reapply.
- Right-click a cell in the filtered dataset and select Filter, Reapply.
Filtering in the Web App
A workbook with normal or table filtering can be
uploaded, and users in View or Edit mode can interact with the filters.
The only way to turn on filtering in an uploaded workbook is to be in
Edit mode and to turn the dataset into a table by selecting a cell in
the dataset and going to Home, Tables, Sort & Filter as Table or
Insert, Tables, Table. After a dataset has been turned into a table in
the Web App, it cannot be turned back unless it is opened in Excel or
Excel Starter.
The filtering options in the Web App work a bit
differently from Excel and Excel Starter, but offer many of the same
options. To get to the filtering list, click the columns drop-down and
select Filter. Most of the Text, Number, and Date Filters options
are also available—see the section “Using Text, Number, and Date
Special Filters” for more information.
|
Sometimes you may upload a workbook where the
filters are already set and rows are hidden, but when you look at the
drop-down, you don’t see the option to clear the filter. To force the
filters to reset, select Filters from the drop-down to bring up the
filter listing. Select one item from the list and click OK. The Clear
Filter from column header option should now be available.
|
Turn Filtering on for One Column
Filtering can be turned on for a single column or
for two or more adjacent columns. This can be useful if you want to
limit the filtering users can apply. If the sheet is then protected,
users cannot turn on filtering for the other columns (see the Allow
Filtering on a Protected Sheet section for more information). A
workbook uploaded to the Web App will retain this setting.
To
control what column has filtering, select the header and first cell
directly beneath the header. Then do one of the following:
- On the Home tab, select Editing, Sort & Filter, Filter.
- On the Data tab, select Sort & Filter, Filter.
