3.1 Sorting and Filtering

Sorting and filtering can easily be applied to organize and locate information. There are sort options in the Data menu, but if your list has a header row use the following method for both sorting and filtering:

1.- Select any one cell in the data range you wish to sort or filter.

2.- Enable the filter controls (even if you only want to apply a sort) by choosing Data > Filter (also available as a button on the toolbar).

a) Sort: In the header of the column on which you wish to sort, select the filter control drop-down, and choose Sort A Z or Sort Z A. At the time of writing, you can sort by:

  • Numbers – ascending or descending

  • Text – alphabetical, ascending/descending

  • Dates – ascending/descending

b) Filter: To apply a single field filter, choose one or more values from the drop-down list, or choose an option from Filter by condition...

3.2 Pivote tables

Pivot tables allow you to rearrange a data set so as to be able to view it from different perspectives. In order to do this, the data must be organised in a pivotable way; you cannot create a pivot table from poorly-organised data. In order to construct a pivot table, you need to choose:

  • A field that contains the values to be used in calculation

  • A field to be used as labels on the left (row labels)

  • A field to be used as labels across the top (column labels)

  • Optional – a field to be used for filtering

Ask yourself what two attributes you want to compare – one of these will become the row labels, the other the column labels. The filter allows you to limit the rows, columns and values by another field.

Having first ensured that your data is pivotable, and that you have an idea as to what numerical values you're going to be working with, it's time to do some pivoting!

1.- Select any cell in your dataset and choose Data > Pivot table — the data range is indicated and a dialogue opens with the range already entered.

2.- You will probably want your pivot table on a new sheet, but you can opt to put it on an existing sheet (in which case you'll need to define the position of the top-left cell of the area to be used).

3.- Choose Create and the empty framework for your pivot will be created.

4.- Using the Pivot table editor pane on the right, add fields in the locations you require using the Add buttons. The panel will also suggest options it thinks you might want to try.

3.3 QUERY function

The QUERY function is the most powerful data function in Google Sheets. It provides a method to interrogate a data table and generate a sub-set that is actively linked to the source data but will not change it. This makes it particularly useful in a collaborative context, or where you want to produce several sub-sets from the same data source.

=QUERY(data-array,"query-expression")

The "query-expression" argument is a string of text which defines the columns to be used, the sort orders to be applied, filtering criteria, and even grouping options and calculated values. The expression is based on Structured Query Language (SQL), used by database systems, but it's not difficult to work with for things like straightforward sorting and filtering.

Here's some simple examples where dataList is a named range: