Filtering and sorting data

Even though it is easy to use the formulas in a Google Spreadsheet, to compute almost any kind of operation with the data, there are still some features to make our life even easier. For example, let us imagine this shopping list:

Figure 12: Example of data in a spreadsheet

Activity 4: Let the learners inspect the interface and find their way. Ask them to sort the data by price (Hint: put the cursor on column C, and go to Data -> Sort sheet by Column C).

But what if we need to sort the data multiple times? How can we order it by price? How can we reverse the date order? How can we show only some data? Easily: by creating a filter.

Figure 13: Creating a filter

To create a filter we should select our data and click on the Create a filter button. It automatically detects if it has headers.

Figure 14: Resulting filter. Note the icons to the right of the headers.

Now it’s easy for us to sort or filter the data of our table. In the Figure 15 we can see how we can sort in an ascending or descending way (1), we can filter by a condition (i.e. the value is greater than other, or the date is previous to another) (2) or we can filter by the values (3).

Figure 15: Filtering

Converting text into data

Sometimes we have data in a spreadsheet that we cannot work with, because it’s not numeric or it has more than one piece of information in one cell. For example:

Figure 16: Example of overcrowded cells

In Figure 16 we have a column (Name) where the names are separated by a comma, and another column (Age range) indicating the estimated minimum and maximum of their ages. We cannot work with this data: we cannot sort by first name and we cannot make calculations by minimum or maximum values.

Activity 5: Ask your learners how they would solve this problem. See how many alternative solutions are.

We have the possibility to split text into columns. It’s important to leave as many blank columns to the right as new columns we will need. Then we go to Data -> Split text to columns and select the separator. In the column A, the separator will be a comma, where as in column C we’ll separate by a dash.

Figure 17: Splitting into columns

Now we can change the headers of the new columns and work with the new data.

Activity 6: Make a similar file with different columns to separate and allow your learners some minutes to practice.