I have two Data Tables that I would like to combine into one Pivot Table in Excel for Mac.
I have Excel for Mac 2019, version 16.54, so a lot of the solutions that are available online seem not to be available for me, such as combining source data into a new table through a Power Query, using Multiple source ranges/tables for the Pivot table, or establishing Relationships between multiple Data tables.
Here's the setup:
I have two Data Tables (on two different sheets in the same workbook). Each table has the following Columns that are : Date, Amount, Category, and Group. They are not in the same order in the two tables. (The tables also have some other unique columns that are irrelevant for my summary.)
I would like to create a Pivot table with Years and Months as columns, and Group (which is simply a Macrocategory) and Category as rows and Amount as values. I was able to successfully do that with data from one single source. But I couldn't find a way to get data from both tables combined.
It seems that my version of Excel doesn't have any of the following solutions:
Of course, as a workaround, I could try to combine the two tables into one. But I would like to have the opportunity to add more data to each table and then update my Pivot – and since the data comes ordered differently, it would be super helpful if there was a way to keep the two tables on different sheets, so I won't have to rearrange data for one of them every time I receive new data.
Help is very much appreciated!
I have Excel for Mac 2019, version 16.54, so a lot of the solutions that are available online seem not to be available for me, such as combining source data into a new table through a Power Query, using Multiple source ranges/tables for the Pivot table, or establishing Relationships between multiple Data tables.
Here's the setup:
I have two Data Tables (on two different sheets in the same workbook). Each table has the following Columns that are : Date, Amount, Category, and Group. They are not in the same order in the two tables. (The tables also have some other unique columns that are irrelevant for my summary.)
I would like to create a Pivot table with Years and Months as columns, and Group (which is simply a Macrocategory) and Category as rows and Amount as values. I was able to successfully do that with data from one single source. But I couldn't find a way to get data from both tables combined.
It seems that my version of Excel doesn't have any of the following solutions:
- the possibility to do a PowerQuery, through which I could first create a consolidated table on a separate sheet and create the pivot table from that
- the possibility to create the Pivot Table from Multiple ranges/tables (I don't have any Pivot table Wizard option that I can find)
- creating a data model by establishing relationships between the two tables and then using that to construct my Pivot table
Of course, as a workaround, I could try to combine the two tables into one. But I would like to have the opportunity to add more data to each table and then update my Pivot – and since the data comes ordered differently, it would be super helpful if there was a way to keep the two tables on different sheets, so I won't have to rearrange data for one of them every time I receive new data.
Help is very much appreciated!
