Excel for Mac 2016 - Pivot Table data from multiple tabs in workbook

Question

I have workbook for our employee time allocations.  Each month is on a different tab and the tables are set up so that each row is an employee and each column is a different cost center that their time gets charged to.  

I would like to create a Pivot Table that pulls from each month into a summary that shows each employee's average time spent in each cost center.

Is it possible to pull data from multiple tabs into a Pivot Table in Excel for Mac 2016?

Answer 1

Hi emmabos,

Currently, I'm afraid there is no out of the box option to aggregate data in a pivot table from multiple worksheets. I encourage and suggest you vote for Consolidate Multiple Data Sources in a Pivot Table or add the comments inExcel for Mac UserVoicewhich is the best platform for us to listen from you and others to improve our products. Thanks for your understanding and feedback.

Besides, here is Consolidate multiple data sources in a PivotTable (applies to: Excel for Mac 2011) for your reference.

Regards,

Eli

Answer 2

Yes, this can be done. You can do it using a SQL query

http://www.agentjim.com/MVP/Excel/2011Relational18Queries.html

If your query pulls data from other worksheets in the same workbook, the query can not be refreshed. If you set it up so the query result comes from a different workbook, you will be able to refresh the query.

Answer 3

As of now, No. This is not possible to pull data from multiple sheets to create a pivot table in Mac. While this is possible in Windows version.

My apologies.