To filter the data in the columns of an Excel spreadsheet
large tables are clearly – just too much information will appear. However, Excel for a recipe: with a few clicks, you can filter data. Thus appear only the cells you are really interested in.
so you filter data in Excel
- click in the field in which you want to filter.
- you then use the option to filter data – .
- in addition to the column headings in the current scope. After clicking on it, you will see the filter options.
- in the filter options, you can then set the requested data for the current column. Either through the selection of suggested values, or conditions.
- click on OK Excel restricts the view according to your input and displays only the rows that pass through the filter.
- one again click on the button filters returns all rows
to set, where you can filter
if – as suggested – just click on a cell, and then the filters enable, choose Excel automatically the area, for the filtering effect. It uses the area surrounding the cell and adds an icon for the filter options for each column at the top.
you can control the behavior but also itself. To do this, select for example only certain columns and then click filters. Then the icons to control the filter options are displayed only for this. So, you can use data from other columns to filter.
to work the filter options
how to restrict the rows displayed works in several ways. For one, Excel parses the contents of the affected cells and tried you with a meaningful choice of ways to build it. This works well for date fields: here get the common values in outline by year, month and day and can bring out the check boxes quite effectively, what you are interested in.
Text or numbers, the pure collection of existing cell contents offered by Excel mostly less sense. But there is still an alternative: using the search box you will find according to the type of the current column a menu entry like number filters or text filter .
in a number field, click it and use for example the option greater than .
Then, enter a value. Now only lines, where the content of the columns is greater than this value will appear.
this option to offers for the other field types appropriate options, such as for text values the search for a common string of part of or date so meaningful conditions ‘in this quarter”or”Last week”.
caution with the sort function
you might get the idea, that the sorting options offered at the top of the filter options just seem like the filter, that you could reorder the list of rows so that temporary and again would be in the old representation according to the filter mode.
This is not so: the reordering affects the original data and still remains even after the exit of filtering.
thanks to the function to the rows Undo on it is no problem, but, if necessary, to bring the previous collation: just press the key combination [Strg + Z] , until the old state is regained (Mac users use [Cmd + Z] ).
you are interested in it, how you can optimally present your tables? Please read our tips to print with Excel !