Using filters

Here’s a very useful option! Filters can allow you to easily view, sort and narrow large tables based on one or more of the given criteria. All this makes filtered tables the first choice for entering data about partners, contacts, or even creating a simple CRM system. Working with filters is easy and intuitive, they are swiftly created and removed from the table. Read on, you will going to like this!

In order to apply automatic filtering, first we need to select the table. It’s easiest to click somewhere in it, so use the shortcut CTRL + A to quickly select all the data in the table. Then, in the Home ribbon, open the Sort & Filter menu and click the Filter option. Once this is done, the table will change so that the filter symbols appear at the end of the tag of each column, in the header of the table. The symbol of the filter is a reverse triangle arrow. Clicking on an arrow will open the filter menu for the given column. In our example, by clicking on the arrow next to the “Ime (Name)” column, the name filter menu will open in the table.

In the upper part of the menu there are options for sorting the table by selected column. The first two options are for sorting in ascending or descending order, and the third column is used for arranging items according to the colour of the text or the colour of the background of the cell. If we have rows with multiple colours, the menu will show all of these colours together with the unmarked cells label. From this menu it is also possible to enter the Custom Sort menu, where we define user-defined sorting criteria. Then, in the menu, filtering criteria are displayed in the form of submenus. Depending on the type of data, the name of that submenu denotes Number Filters or Text Filters. When filtered column contains numbers, submenu will contain following criteria: equal, not equal, larger, greater or equal, less, less, or equal, between, the first 10, above the value of the value for the given column, below the average of the values ​​for the given column and custom filter. When column contains text data, filtering sub-menu will contain criteria: equals, is not equal, begins with text, ends with text, contains, does not contain, an arbitrary filter. In both cases, an arbitrary filter allows us to select the filter that will be applied by choosing the options in the dialog box itself. Similarly, as we were able to sort data based on a certain colour, we can apply filtering in relation to a particular colour (text or background).

At the bottom of the filter menu, there is a search box (Search) and all possible filter values, or all those in the given column. By clicking on the check boxes next to the values, we determine which of them will be displayed, and by clicking on the Select All option, we will display all the values. If it has more values ​​than it can fit in menu, a scroll bar will appear on the right side of the menu, so we can scroll by changing which values ​​are displayed in the menu. However, it’s better to use the search box at that time. Entering criteria in this field condenses the list of displayed values ​​in accordance with the given criteria. For example, by entering the text “Mar” the set of values ​​will be narrowed to those names beginning with the given letters, for example: „Marko“, „Marija“, „Marijana“, „Marina“, etc.

By entering the filter criteria for one column, the data in the whole table is filtered. If the filter is sorting, we will only be able to sort it in relation to the specified column (unless we select the Custom Sort option as a criterion). Otherwise, it is possible to enter multiple filters for different columns, and each new filter will additionally narrow down the set of data displayed in the table. In the filtering menu there is a Clear Filter option, which removes the filter set. The same option exists in the Sort & Filter menu in the Home ribbon, but with its selection, we remove all filtering criteria in the table. If you select Reapply from the same menu, we re-apply the specified filters.

If we want to remove filtering, or to reuse a traditional Excel table, we need to repeat the initial automated filtering process. In other words, you need to select a filtered table and in the Sort & Filter menu, select Filter again and the filters will be removed.