Share via

Create Pivot Table in Excel (Office 365 Version on Mac) using data on Multiple Sheets without using Add-ins

Anonymous
2020-05-27T11:31:52+00:00

I am using Excel 16.37 (Office 365 Version) on a Mac and I would like to create a pivot table using data from multiple sheets (without using VBA Script).

Each sheet contains data for a specific year. The format of the data (row labels, column headings etc.) is essentially the same for each sheet. One solution is to copy and paste the data one below the other, but this is extremely inelegant and time-consuming.

I am using the version on a Mac so I can not install Power Query, Power Pivot etc. It is the version of Excel given to students from my university so I can not install add-ins.

Is there a way that I can easily create a pivot table by using data on multiple worksheets in my situation by simply using the tools provided with Excel 16.3 (Office 365 Version) on a Mac without installing add-ins etc. and without using VBA script?

Microsoft 365 and Office | Excel | For home | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jim G 134K Reputation points MVP Volunteer Moderator
    2020-05-29T18:15:00+00:00

    Hi again Nishan

    I forgot to mention some things:

    The code base of Excel for Windows and Excel for Mac is the same. Almost all of Get and Transform for Windows is in Excel for Mac except for the graphical user interface.

    If you want your students to really become experts, you might show them how to use Get and Transform with Visual Basic for Applications (VBA). You can also use AppleScpript, JavaScript or Objective-C to programmatically generate Get and Transform queries in Excel for Mac.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2020-05-27T17:59:43+00:00

    Thanks for the response.

    I am a teaching assistant and I posted this problem in order to conduct a tutorial for a college class. Unfortunately I'm not in a situation to request students to pay $40 USD in order to get software to complete just one assignment.

    Are any free alternatives available?

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Jim G 134K Reputation points MVP Volunteer Moderator
    2020-05-27T16:29:07+00:00

    PowerQuery uses queries as its name implies. PowerQuery uses the Data Model, a feature that exists but is not exposed in Excel for Mac.

    In Excel for Mac you can use Microsoft Query to make a PivotTable using multiple worksheets from an Excel workbook as your data source. 

    "Easy" is a relative concept. Yes, it is easy once you know how to do it. The first time through will be the most difficult, but once you see how all the pieces fit together, you will be able to run your queries with little to no difficulty.

    Knowing Visual Basic for Applications is not required. Just so you know, you can automate your query process with VBA, AppleScript, Objective-C, or Javascript, but it is certainly not required or necessary to use any of these languages. 

    Knowing Structured Query Language (SQL) at least at a beginner level is essential.

    Installation of add-ins is not required. The ability to perform SQL queries is already built into Excel. You will not need any add-ins.

    On the Mac side, the infrastructure required to preform ODBC connections is no longer included with Mac OS. ODBC drivers and the ODBC manager application were casualties of making Mac OS free. Now, only people who want these connections and are willing to pay for them have them. The price is quite small, IMHO. You will need to install the **ActualTech Access driver**. Installing the driver also installs the new 64-bit ODBC manager application to your Applications > Utilities folder. The driver costs $39.95 US. There is a free trial that you can use. It works the same as the full version, but returns only the first 5 or so records of your query.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Jim G 134K Reputation points MVP Volunteer Moderator
    2020-05-28T14:53:48+00:00

    I know of only one driver that works, the ActualTech driver.

    Installing the free trial installs the full working version of the ODBC Manager application. The free trial mode of the driver works the same as the full version except that only a handful of records is returned. I think that for instruction purposes the free trial is all your students need. You can perform all SQL queries. The only catch is the number of records returned. The ODBC manager app is the full working 64-bit version and is not limited.

    I am very glad you are even considering offering this topic to students. It is very rare, yet I think almost everyone can use this concept.

    Years ago I wrote a tutorial that covers this topic in depth. It has not been updated for Office 2019 (that's in my bucket list for 2021), but most of what is there still applies. Office 2019 is very close in the way it works to Office 2016, so use the portions that apply to Office 2016 in the tutorial. You may use this tutorial freely and I encourage you to share it with your students or modify it to make it current for your purposes.

    Using Microsoft Office 2011 for Mac as a Relational Database and querying other database products

    Was this answer helpful?

    0 comments
  5. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more