CODEWITHSUNDEEP
google-sheetspivot-table
fedorqui
fedorqui

Reputation: 290455

How can I have multiple pivot tables in same sheet?

I have a big sheet with quite a lot of info that keeps growing over time. Based on that, I have created several pivot tables that do some calculations and rankings.

Every ranking keeps growing, so pivot tables may contain 10 rows now, but can grow up to 20 or 30 rows.

I managed to insert several pivot tables in the same sheet and now it looks well, with every ranking after the next one. However, if I add multiple rows, the pivot tables grow and start overlapping, so after a while the ones below start disappearing in favour of the first ones.

Is there a way to have multiple pivot tables in the same sheet with a fixed number of rows among them, preventing them from overlapping?

If you want to 'play' with the data, I created a sample in https://docs.google.com/spreadsheets/d/1MVX3tp6GIqVX6hTyk6TsCxV7YngiMpi7E8oSxa7a9ck/edit?usp=sharing. It is just a ranking on races, where I want to track the best times on different legs. Then it has a second sheet 'rankings' in which I have different pivot tables, one for each leg.

Upvotes: 3

Views: 13097

Answers (2)

MattKing
MattKing

Reputation: 7783

HOw's this for a single formula solution that will scale infinitely in users or legs:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(results!A2:A&"|Best of "&results!D1:1&"|"&OFFSET(results!D2,,,ROWS(results!D2:D),COLUMNS(results!D1:1))),"|",0,0),"Select Col2,Col1,MIN(Col3) where Col1<>'' and Col2<>'Best of ' group by Col1,Col2 order by Col2, MIN(Col3) label MIN(Col3)'Best', Col1'User',Col2''"))

You'll find it in cell B1 on the new tab in your sample called MK.Idea

I should mention that FLATTEN() is an undocumented function that I only recently discovered. I've believe it is intended to remain "hidden" in the back end of the sheets programming, but if what I did is what you're after, there really isn't a more efficient way to do it. I've spoken with an engineer at Google who was surprised it existed as well and told me there were no plans to deprecate it, so here's hoping! For a demo of what it does generally, you can see my sample here: https://docs.google.com/spreadsheets/d/196NDPUZ-p2sPiiiYlYsJeHD6F_eJq7CWO_hP7rFqGpc/edit?usp=sharing

Upvotes: 2

Wicket
Wicket

Reputation: 38435

Spreadsheets and Pivot Tables are marvelous tools for data analysis but they aren't too friendly for creating reports and dashboards, if you are open, to recommendations try Google Data Studio (it includes pivot tables too --> https://support.google.com/datastudio/answer/7516660?hl=en)

Let say that you don't have time to learn another app or you just prefer to keep using spreadsheets, in this case it will be required to implement a workaround.

First, bear in mind that Pivot Tables don't actually overlap, instead an error message is shown:

Solution: insert some rows/columns to give enough room to the Pivot Table to be expanded.

NOTE: You could do this in advance by including a "safe zone" (meaning blank rows /columns) around the pivot tables. You could hide/unhide this "safe zone" as needed.

If you don't want to do the above manually, I think that it should be possible to use an on edit trigger of Google Apps Script to detect that pivot tables are shown and insert new rows/columns to avoid this. If the Pivot Table top left cell returns #REF! your script could use a do.. while or a while to insert the required rows or columns. An smart algorithm will read the Pivot Table settings to calculate the required rows and columns and then insert the required rows / columns in one pass.

Related

Upvotes: 1

Related Questions