how to make a single pivot table from multiple identically formated sources

Question

how to make a single pivot table from multiple identically formated sources

David Berg 20

I have a workbook with several worksheets in which the data is arranged the same, ie, rows with identical columns. I can easily make a pivot table from one of these. If I combine all the data on a single worksheet, it is quite large and excel repeatedly hangs or crashes. So I want to make a single pivot table from all of them.

Emi Zhang-MSFT 30,046 Reputation points Microsoft External Staff

2023-04-25T09:08:17.13+00:00

Hi,

I have not heard back from you in a few days and wanted to check on the status of the problem. If you have any questions with the previous information I've provided, please don't hesitate to let me know. I am glad to be of assistance.

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
David Berg 20 Reputation points

2023-04-27T00:15:37.2666667+00:00

This was quite helpful. However, I have a couple problems. To begin with, I have an older version of Excel, and it supports only Power Query, but not Power Pivot. I can generate queries from my tables, and append them into a single query. When I make a pivot table from that query, it has 2 problems. By default, the values are counted. When I change the value field setting from count to sum, I get an error message that says I can't sum these values because they are text. This is despite changing the type of that column in the query from text to currency. The other problem is that the pivot table covers one query, but only part of the second. Any suggestions? Thanks.
David Berg 20 Reputation points

2023-04-27T01:17:49.87+00:00

I fixed the problem with the values counted instead of summed by changing the type of that column in the query to decimal number, and then making the number format in the pivot tablle currency. I will start over and try again to see if the second problem persists. Thanks.

Accepted answer

0 additional answers

Your answer

Emi Zhang-MSFT 30,046 Reputation points Microsoft External Staff

2023-04-25T09:08:17.13+00:00

Hi,

I have not heard back from you in a few days and wanted to check on the status of the problem. If you have any questions with the previous information I've provided, please don't hesitate to let me know. I am glad to be of assistance.

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
David Berg 20 Reputation points

2023-04-27T00:15:37.2666667+00:00

This was quite helpful. However, I have a couple problems. To begin with, I have an older version of Excel, and it supports only Power Query, but not Power Pivot. I can generate queries from my tables, and append them into a single query. When I make a pivot table from that query, it has 2 problems. By default, the values are counted. When I change the value field setting from count to sum, I get an error message that says I can't sum these values because they are text. This is despite changing the type of that column in the query from text to currency. The other problem is that the pivot table covers one query, but only part of the second. Any suggestions? Thanks.
David Berg 20 Reputation points

2023-04-27T01:17:49.87+00:00

I fixed the problem with the values counted instead of summed by changing the type of that column in the query to decimal number, and then making the number format in the pivot tablle currency. I will start over and try again to see if the second problem persists. Thanks.

Answer 1

Hi @David Berg,

Did all tables have the same title? I created a sample about your requirement: User's image

Go to Data- From Table/Range import all tables into Power Query, and then Append Queries as New: User's image

User's image

Then you can based on the new Table to created the PivotTable in Excel: User's image

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

how to make a single pivot table from multiple identically formated sources

0 additional answers

Your answer