Use QUERY Formula in Google Sheet as Replacement of PivotTable Function


QUERY formula can be used as replacement of PivotTable function. However this formula can only be used in Google Sheet not other spreadsheet application.

In this tutorial I will show 3 (three) case of example used in electrical engineering, cable schedule. First case, there are different size of cables with different length. Then it will be grouped by the same size of cables with total length of that size.

In the next page I will show other cases, if there is another column to be grouped (multiple column grouping), in this case cable type. And also format it to look like pivot table.

First case:

Sample of QUERY formula implementation
QUERY formula implementation in cable schedule

The formula shown above:
=QUERY(A2:B51,"select A, sum(B) where A is not null or B <> 0 group by A label A 'Cable Size'")

Explanation:
table_range : Range of table consist of multiple columns & rows (ex : A2:B51). Could use Named ranges (ex: Power_Cable).
select A : Select column to be displayed based on Google Sheet column label. Other example, select * : select all columns in the range. Example select multiple column : select A, sum(B), D, count(G). Attention : If user delete or insert a column(s), the column label inside QUERY formula will not be updated, user shall change manually!
sum (B) : Calculation operation of number in this column. Other calculation : avg(), count(), max(), and min().
where A : Conditional statement.
is not null : A condition (The cell is not blank). Other condition : is null (The cell is blank), <> (Is not, for example 0 or 'Lorem ipsum')
or : Logic operation (One of the conditions must be met). Other logic : and (All conditions must be met).
group by A : Grouped with the same content of this column. Can use multiple group of column, for example: group A, C; but the select must also those columns: select A, C.
label A : Give a label (example: 'Cable Size') to the header of this column. Add comma then the column for another column header title, for example : label A 'Cable Size', sum(B) 'Length (m)'.
Next page for second & third case.

R. Bambang Widiatmoko

I'm an engineer and working in engineering consultant office. I have some experience in computer application in graphic design (CorelDRAW, Inkscape, GIMP), AutoCAD, and Office. I also like to read technology news.

You may also like...