Multiple pivot tables to one pivot table?

[tta.akmal]

Hello Excel Professionals,

The year is ending, i have multiple pivot tables by month and at the end of the year, i want to combine them, and see Overall picture of the year. Could You please help me the to find the fastest and effective way? Thank You

[kersplash]

You don't need to combine the pivot tables, you just append all the data and then refresh the pivot table.

[tta.akmal]

Thank You for your answer. Imagine i have pivoted sales data of avocado,banana, orange monthly Jan sheet,Feb sheet, i want to Combine them into one, how to?

[kersplash]

All a pivot table shows you is what is contained in your source data.

If the source data for Jan is put into the pivot table you get your results for Jan.

If the source data is Feb you get Feb results and so on.

If you append the Jan, Feb, Mar, etc data in the one table then connect that combined data to your pivot you should get the combined results.

[tta.akmal]

better to give sample)

[sandy666]

is that what you want?

[tta.akmal]

Yes. You made a sweet chocolate!, but the question is, how You made it so sweet?) ( *step by step )

[sandy666]

step by step? LOL

• you've Ex2016 so you've PowerQuery (Get&Transform) also
• click on January range
• go to the ribbon - Data - From table (it will create table in PowerQuery)
• Close&Load as ( in new window choose: as connection)
• do the same for all months
• then ribbon Data - Show Queries
• right click on the first Table (probably Table 1)
• from menu select Append
• in new window select: Three or more tables
• add all tables from left to the right
• Ok
• it will create query table named: Append1
• go to the ribbon - Insert - Pivot table
• on Pivot table window choose Use an external data source
• Choose connection
• click on Query - Append1
• Ok

how to use Pivot Table tyou know

btw. all headers in all tables should have the same headers. It means Sales = Sales but SALES and Sales is not the same

[tta.akmal]

I wish to give you so much more than just a thank you for the beautiful help you gave me. I absolutely love it. But as for now thank you.

[sandy666]

You're welcome
Have a nice day