0

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?

Grasshopper
  • 152
  • 1
  • 10

1 Answers1

0

Grasshopper, Select the effective columns by click-drag-selecting the built in headers on a worksheet. On the data tab, click Subtotal. This launches a wizard that will subtotal sections of the columns based on increments you select. It's pretty intuitive if my explanation doesn't make sense.

Dave Sullivan
  • 31
  • 3
  • Thanks Dave. Should I be doing this on the sheet with the data or the sheet with the pivot table? I tried on the data sheet, it took several minutes, but the result wasn't what I needed because there were far too many subgroups. I also tried on the pivot table sheet by selecting the subtotals from the Design tab in the ribbon but that did nothing at all. – Grasshopper Jul 11 '16 at 20:53
  • You're welcome, Grasshopper. I have only used it on data to calculate quarterly subtotals, not a pivot table. I would try it on the data and create/update the pivot table. It really should make a lot more sense once you see how it works. You might wanna back up the file first for safety. Good luck. – Dave Sullivan Jul 11 '16 at 21:05