Combine Multiple Pivot Tables into 1 table

Question

Is there a way to combine multiple pivot tables into 1 table of data. Currently the only way I can do this is by copy/paste and it is very tedious, time consuming and potential for entry errors. This is a small sample of what I am needing to do. I have Raw Data that has columns for Sex, Ethnicity, Race and Locations (Bedrock, Zoo, Lake). I can do a pivot table for each location to get counts and % for sex, ethnicity and race, but then I need to combine them into 1 table for presentation.

See screenshot of raw data, pivot tables, and desired 1 table.

Any help is greatly appreciated.

Thanks

Stacy

Answer 1

Dear Gehm,

I'm Ibhadighi and i'd happily help you with your question. In this fdorum, we are Microsoft consumers just like yourself.

There are several ways to combine data from multiple pivot tables into one table. One way is to use the Consolidate function in Excel 1. Another way is to use Power Query to import and combine data from multiple tables into one table 2. You can then create a pivot table based on the combined data.

If you have Excel 2013 or later, you can also create relationships between tables and build a pivot table from multiple tables 3. This method automatically creates the lookups for you and allows you to analyze data from multiple tables in one pivot table.

I hope this helps.

Best Regards, IBHADIGHI

Answer 2

Can you provide any instructions or resources on how to do any of these options. I am feeling at a loss, I have tried to follow youtube and other info I found but nothing seems to work correctly.

Thanks

Stacy

Answer 3

Hi,

In the first place, why are you creating one Pivot Table for each location? Why not show all in one or create a slicer of the Location.

Answer 4

I can’t do 1 because the columns nest into each other and I don’t want that. I am not familiar with “slicer” is this something that is available or is it an add on?

Thanks

Stacy

Answer 5

Hi,

If the columns are nesting into one another, then most likely your data is not well organised. Share data in a format that can be pasted in an MS Excel file and show the expected result.