Use QUERY Formula in Google Sheet as Replacement of PivotTable Function


Second case will show when grouping rows from multiple columns.

QUERY grouping of multiple columns

The formula shown above:
=QUERY(A2:C51,"select A, C, sum(B) where A is not null or B <> 0 group by A, C label A 'Cable Size', C 'Cable Type', sum(B) 'Length (m)'")
As we can see, if we want to group by 2 or more column (ex: group by A, C), we shall also select those column (ex: select A, C).

Last case will show how to look like a pivot table.

QUERY with pivot function

The formula shown above:
=QUERY(A2:C51,"select A, sum(B) where A is not null or B <> 0 group by A pivot C label A 'Cable Size'")
As we can see, if we want one of the column data to be the row header, use pivot function (ex: pivot C) and remove that column from select.

That’s it. Did you find this formula easier & more practical than PivotTable function?
Attention:
There shall be no any content in cells below cell that contain QUERY formula and cells affect by it, or the QUERY function will shown error.

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...