CODEWITHSUNDEEP
google-sheetspivot-table
Samuel Hapak
Samuel Hapak

Reputation: 7192

Remove zero rows from Google Sheets pivot table

I have a pivot table to aggregate some financial balance by counterparties. Link to the example.

Over time, it's expected that the aggregate amount for most of the parties will be 0. I want to show only those rows in the pivot table where the aggregate is nonzero. Otherwise, my pivot table will be cluttered by tons of empty zero rows.

In the above example, this corresponds to hiding John, Mary, and Thomas records.

Is there a way to accomplish this?

Upvotes: 2

Views: 21780

Answers (2)

Samuel Hapak
Samuel Hapak

Reputation: 7192

You can achieve this by adding a filter on top of your pivot table.

  1. Select the row with currencies (2nd row of pivot table)
  2. Data->Create a filter...
  3. Click on the last column
  4. Filter by condition...->Custom formula is
  5. =ABS(B3) + ABS(C3) + ABS(D3) > 0

See the example.

Upvotes: 6

player0
player0

Reputation: 1

you could use a double query like:

=QUERY(QUERY(Transactions!A1:C, 
 "select A,sum(B) 
  where A is not null 
  group by A 
  pivot C", 1),
 "where Col2>0 
     or Col3>0 
     or Col4>0", 1)

0

Upvotes: 3

Related Questions