Sorting and Filtering Data

16 min

Let's begin

The class teacher needs two things from the marks sheet: the students listed from the highest Mathematics mark to the lowest, for prize-giving, and a list of only the students who scored 60 or more. Sorting and filtering do this in seconds, however long the list is.

The data

	A	B	C	D
1	Name	Maths	English	ICT
2	Uwase	78	64	90
3	Mugisha	55	72	48
4	Keza	91	85	88
5	Ishimwe	43	50	61
6	Gatete	67	49	75

Each row is one record (all the data about one student), and each column is one field (one kind of data, such as the Maths mark).

Sorting

Sorting rearranges whole rows into an order based on one or more columns, called sort keys.

Ascending: smallest to largest (0–9) or A to Z.
Descending: largest to smallest (9–0) or Z to A.

Click any cell in the data, or select the whole table including the headings.
Choose Data > Sort. (Excel: Data > Sort.)
For Sort Key 1, choose the column (Maths) and the order (Descending).
Check that the first row is treated as headings (Options tab: Range contains column labels; Excel: My data has headers), then click OK.

Result: Keza 91, Uwase 78, Gatete 67, Mugisha 55, Ishimwe 43. Each student's English and ICT marks moved with them, because whole rows were sorted.

Add a Sort Key 2 to decide the order when two values in Key 1 are equal, for example sort by Class, then by Name within each class. The toolbar also has quick Sort Ascending and Sort Descending buttons.

Warning: never select just one column and sort it on its own. The marks would move but the names would not, and every mark would end up beside the wrong student.

Filtering

A filter shows only the rows that meet a condition and hides the others. Nothing is deleted.

AutoFilter: click in the data and choose Data > AutoFilter (Excel: Data > Filter). A drop-down arrow appears in every heading. Click an arrow and tick the values to show, or choose a condition such as Top 10.
Standard Filter: Data > More Filters > Standard Filter lets you write conditions, such as Maths >= 60, and combine up to eight of them. (Excel: in the filter arrow, Number Filters > Greater Than Or Equal To.)

With the condition Maths >= 60, only Uwase (row 2), Keza (row 4) and Gatete (row 6) stay visible. The row numbers jump from 2 to 4 to 6: that is how you can tell rows are hidden. To show everything again, choose Data > AutoFilter again (Excel: Data > Clear).

Tip

Sort moves, filter hides. Sorting changes the order of whole rows; filtering changes only which rows you can see.

✦

Practice

Use the marks table above.

Sort by ICT, ascending. Who is first and who is last?

Show solutionHide solution

Solution: ICT marks from lowest: Mugisha 48, Ishimwe 61, Gatete 75, Keza 88, Uwase 90. First: Mugisha. Last: Uwase.

Sort by Name, A to Z. Who comes third?

Show solutionHide solution

Solution: Alphabetical order: Gatete, Ishimwe, Keza, Mugisha, Uwase. Third: Keza.

Filter to show English < 60. Which students are shown?

Show solutionHide solution

Solution: Ishimwe (50) and Gatete (49).

Why is Mugisha's row not deleted when a filter hides it?

Show solutionHide solution

Solution: A filter only hides rows that do not meet the condition. Removing the filter shows them again, unchanged.

Remember

Data > Sort orders whole rows by one or more sort keys, ascending or descending. Always include every column.
Data > AutoFilter (Excel: Data > Filter) and Standard Filter show only the rows that meet a condition.
Filtering hides rows; it never deletes them.

Sources and credits

Getting Started Guide 26.2, Chapter 3: Getting Started with Calc, LibreOffice Documentation Team, The Document Foundation, licensed under CC BY 4.0. Adapted by Ganzaa.
The explanations, worked examples and practice items were written by Ganzaa.

1The data

2Sorting

3Filtering

Practice

The data

Sorting

Filtering