Filters and advanced filters allow you to work on certain filtered rows (records) of a data range. In the spreadsheets in LibreOffice there are various possibilities for applying filters.
One use for the AutoFilter function is to quickly restrict the display to records with identical entries in a data field.
In the Standard Filter dialog, you can also define ranges which contain the values in particular data fields. You can use the standard filter to connect the conditions with either a logical AND or a logical OR operator.
The Advanced filter allows up to a total of eight filter conditions. With advanced filters you enter the conditions directly into the sheet.
To remove a filter, so that you see all cells again, click inside the area where the filter was applied, then choose .
When you select multiple rows from an area where a filter was applied, then this selection can include rows that are visible and rows that are hidden by the filter. If you then apply formatting, or delete the selected rows, this action then applies only to the visible rows. The hidden rows are not affected.
This is the opposite to rows that you have hidden manually by the Format - Rows - Hide Rows command. Manually hidden rows are deleted when you delete a selection that contains them.
Copy the column headers of the sheet ranges to be filtered into an empty area of the sheet, and then enter the criteria for the filter in a row beneath the headers. Horizontally arranged data in a row will always be logically connected with AND, and vertically arranged data in a column will always be logically connected with OR.
Once you have created a filter matrix, select the sheet ranges to be filtered. Open the Advanced Filter dialog by choosing , and define the filter conditions.
Then click OK, and you will see that only the rows from the original sheet whose contents have met the search criteria are still visible. All other rows are temporarily hidden and can be made to reappear with the command.
Load a spreadsheet with a large number of records. We are using a fictional Turnover document, but you can just as easily use any other document. The document has the following layout:
|
A |
B |
C |
D |
E |
|
|
1 |
Month |
Standard |
Business |
Luxury |
Suite |
|
2 |
January |
125600 |
200500 |
240000 |
170000 |
|
3 |
February |
160000 |
180300 |
362000 |
220000 |
|
4 |
March |
170000 |
and so on... |
Copy row 1 with the row headers (field names), to row 20, for example. Enter the filter conditions linked with OR in rows 21, 22, and so on.
|
A |
B |
C |
D |
E |
|
|
20 |
Month |
Standard |
Business |
Luxury |
Suite |
|
21 |
January |
||||
|
22 |
<160000 |
Specify that only rows which either have the value January in the Month cells OR a value of under 160000 in the Standard cells will be displayed.
Choose , and then select the range A20:E22. After you click OK, only the filtered rows will be displayed. The other rows will be hidden from view.
The AutoFilter function inserts a combo box on one or more data columns that lets you select the records (rows) to be displayed.
Select the columns you want to use AutoFilter on.
Choose Data - Filter - AutoFilter. The combo box arrows are visible in the first row of the range selected.
Run the filter by clicking the drop-down arrow in the column heading and choosing an item.
Only those rows whose contents meet the filter criteria are displayed. The other rows are filtered. You can see if rows have been filtered from the discontinuous row numbers. The column that has been used for the filter is identified by a different color for the arrow button.
When you apply an additional AutoFilter on another column of a filtered data range, then the other combo boxes list only the filtered data.
To display all records again, select the all entry in the AutoFilter combo box. If you choose Standard, the dialog appears, allowing you to set up a standard filter. Choose "Top 10" to display the highest 10 values only.
To stop using AutoFilter, reselect all cells selected in step 1 and once again choose Data - Filter - AutoFilter.
Sort lists allow you to type one piece of information in a cell, then drag it to fill in a consecutive list of items.
For example, enter the text "Jan" or "January" in an empty cell. Select the cell and click the mouse on the lower right corner of the cell border. Then drag the selected cell a few cells to the right or downwards. When you release the mouse button, the highlighted cells will be filled with the names of the months.
Hold down Ctrl if you do not want to fill the cells with different values.
The predefined series can be found under Tools - Options - LibreOffice Calc - Sort Lists. You can also create your own lists of text strings tailored to your needs, such as a list of your company's branch offices. When you use the information in these lists later (for example, as headings), just enter the first name in the list and expand the entry by dragging it with your mouse.
