Does anybody know how to consolidate multiple sheets as one large table using a pivot table in Excel 2013.
Data Setup
I have data split into 4 different worksheets because the number of rows is very large and will not fit on a single sheet. I have named the tables as Table1, Table2, Table3, Table4 and the columns headings are the same for each table.
Pivot Table Setup
Insert -> Pivot table and select the data from the sheets.
Checked Add this data to the Data Model
- Pivot Table fields:
- Product Type (Table 1)
- AMT(Table 1)
- AMT(Table 2)
- AMT(Table 3)
- AMT(Table 4)
- Filters: None
- Columns: None
- Rows: "Product Type"
- Values: Sum of AMT(Table1)
- Values: Sum of AMT(Table2)
- Values: Sum of AMT(Table3)
- Values: Sum of AMT(Table4)
What I Get
Row Labels
PRODUCT TYPE 1
Sum of AMT 190160.84
Sum of AMT 13286710.49
Sum of AMT 12971944.86
Sum of AMT 2234244.9
PRODUCT TYPE 2
Sum of AMT 9514522.78
Sum of AMT 13286710.49
Sum of AMT 12971944.86
Sum of AMT 2234244.9
PRODUCT TYPE 3
Sum of AMT 6439.71
Sum of AMT 13286710.49
Sum of AMT 12971944.86
Sum of AMT 2234244.9
PRODUCT TYPE 4
Sum of AMT 49994.9
Sum of AMT 13286710.49
Sum of AMT 12971944.86
Sum of AMT 2234244.9
PRODUCT TYPE 5
Sum of AMT 2968200.83
Sum of AMT 13286710.49
Sum of AMT 12971944.86
Sum of AMT 2234244.9
PRODUCT TYPE 6
Sum of AMT 362348.12
Sum of AMT 13286710.49
Sum of AMT 12971944.86
Sum of AMT 2234244.9
Total Sum of AMT 13091667.18
Total Sum of AMT 13286710.49
Total Sum of AMT 12971944.86
Total Sum of AMT 2234244.9
What I Want to Get
Instead of having each table on its own line I want to have the amounts summed into a single line as if it were one large table. Something like this:
Row Labels
PRODUCT TYPE 1
Sum of AMT 28683061.09
PRODUCT TYPE 2
Sum of AMT 38007423.03
PRODUCT TYPE 3
Sum of AMT 28499339.96
PRODUCT TYPE 4
Sum of AMT 28542895.15
PRODUCT TYPE 5
Sum of AMT 31461101.08
PRODUCT TYPE 6
Sum of AMT 28855248.37
Total Sum of AMT 41584567.43
How can this be done?
