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.
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.
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!
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?
> 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.
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!”
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.
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 ). 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.
). You never explained what you wanted (except that you wanted pivot table).