Pivot tables from multiple tabs

I typically use Google Sheets, but I have LibreOffice installed and use that as well. I have access to Excel if need be.

I have a spreadsheet with tabs for several years. Each tab is identical in composition. I want a Pivot Table that aggregates the data from all the tabs. Researching this for any spreadsheet software I use gives me no answer. I found one post that had an exceptionally complex solution that was not exactly what I am looking for. It was too complex for me to follow accurately. One answer seemed to be selecting multiple tabs but I can’t create a Pivot Table without de-selecting the tabs.

Is there a simple solution?

I’m not aware of a way to do this myself - might be a good question for a LIbreOffice community.

Maybe this method?
<https://ask.libreoffice.org/t/how-can-i-make-a-pivot-table-with-data-from-several-sheets/660>

I confess I couldn’t get this method to work. It’s my limitation, not the software 's. In the end, I did a copy and paste of each tab onto a single tab and ran the pivot table from there. Since most tabs were historical and don’t change, I just have to update the aggregated data with latest additions. Not ideal but it gets the results.

Thanks for trying to help!

I don’t know that it’s you, Prexy; I’ve never been able to do this successfully either.
To be fair though, I tend to only use pivot tables on very small data sets, so copying everything into one sheet is doable in those circumstances. I’ll let you know if I ever figure this out.

Well, it is final now. I tried two different versions of Xcel in a Windows boot. It didn’t work there either. It strikes me as odd that there is no easy way to this as it seems useful. Anyway, thanks for the suggestions!

It seems that, Pivot Tables in general, only apply to data on a single sheet – regardless of which Spreadsheet product is being used … :expressionless:

Also my experience in MSExcel and LibreOffice; it does limit their usefulness.
Though, they also have some downsides in corruptibility, so maybe it’s a bonus in disguise that you can’t use them on too big a set.

So, wouldn’t it be possible to create a new sheet, combine the data on (all) other sheets in that new sheet, and create a pivot table from just the new sheet?

I understood that is what Prexy has described doing here…

> A pivot table is a table of grouped values that aggregates the individual items of a more extensive table within one or more discrete categories. This summary might include sums, averages, or other statistics, which the pivot table groups together using a chosen aggregation function applied to the grouped values

This will not work at all if the data structures of two worksheets are different in any way so the designers forbid multiple worksheets.

Hmm. Indeed; my bad (it was late).

I found a complicated working formula, but simplest is, like Prexy described, copy and paste it with <Ctrl>+<Shift>-V -> “As Link”.

On whim, I asked Chat GPT to create a formula that would create the pivot table I wanted. I tried two times and neither answer worked. So much for AI. It should have told me “you can’t do that!”

Is ChatGPT “intelligent” or, is it simply a large “expert system” (which is part of AI … )? :imp:

  • Given that, it’s based on GPT-3 –

an autoregressive language model

  • Further information can be gleaned from the non-profit company OpenAI Inc.

Please inspect the large chess-playing expert system “Deep Blue” …

Just saw an article that they are going to a pay model with monthly subscription. Rumored to be $24 per month

Correct – there ain’t no such thing as a free lunch … :sunglasses:

I went to a different AI site that is still free. As previously determined, there is no easy way to create a Pivot table from multiple sheets. Old versions of Excel had a Pivot Table Wizard which did this relatively easily. I do not see it on my version of Excel and it is not available on Google Sheets or LibreOffice.

This new (to me) AI site gave me many links to follow. I ended up following one recommendation. It said to use the Query function to create the sheet I was creating by cut and paste. This is not a solution I was after but it made it easier to add new data to the end of the sheet and keep the pivot table updated.

=QUERY({‘2018’!A1:H253;‘2019’!A2:H244;‘2020’!A2:H81;‘2021’!A2:H101;‘2022’!A2:H150;‘2023’!A2:H19})

As I add data to 2023, I just have to change H19 to H30 etc. Then edit the pivot table range to match.

A separate note: I tried to make the formula easy to read but I see no code tags and I don’t know how the Quote tag will work.

Use the </> button (or press CTRL+E) to add preformatted text…

=QUERY({‘2018’!A1:H253;‘2019’!A2:H244;‘2020’!A2:H81;‘2021’!A2:H101;‘2022’!A2:H150;‘2023’!A2:H19})

While I almost never use Excel, I saw a report that they will add a data command labeled “consolidate.” It will do exactly what I wanted. You can identify columns from different tabs and manipulate them as one. I don’t know if it will ever come to Google Sheets or LibreOffice. I think it will , eventually.

What do you mean “will”? Consolidate is available since Excel 2010 at least. But it is not the same as pivot table. It takes multiple ranges and combines values in the same cell in each range using one of the predefined formula. Excel can reference ranges from different documents here.

Your question was specifically about pivot table (again, the means instead of the goal :slight_smile: ). You never explained what you wanted (except that you wanted pivot table).

LibreOffice has consolidate function. It cannot reference ranges from different documents directly, but you can reference cell from different document so you can create multiple sheets in new document each referencing different documents and consolidate ranges in these sheets. I guess it should be relatively straightforward to auto-generate using simple macros.