Power Pivot is a popular business-intelligence add-in from Microsoft for Microsoft Excel. When PowerPivot is installed, the PowerPivot tab is added to the Excel ribbon. PowerPivot is freely available from Microsoft. You can follow this guide to import tables into PowerPivot.

macOS Configuration

On macOS, you need to set the AnsiCodePage configuration setting in the cdata.odbc.googlesheets.ini file, which is located in the folder containing the driver library, the lib subfolder under /Applications/CData ODBC Driver for GoogleSheets. Add the AnsiCodePage property in the Driver section.

You need to set the value of this property to the code page of your machine's current system locale. Here are several examples:

Load Google Sheets Tables

1. Launch the PowerPivot window from the PowerPivot Window button on the PowerPivot tab of Excel.
2. Click Get External Data > Other Data Sources to open the Table Import Wizard.
3. Select Other/Advanced (OLEDB/ODBC) Relational Data Source and click Next. You will be presented with a dialog that allows you to define a user-friendly connection name for your connection and build a connection string for it.
4. Click Build to open the Data Link Properties window.
5. On the Provider tab, select Microsoft OLEDB Provider for ODBC Drivers.
6. On the Connection tab of the Data Link Properties window, select Use data source name and select the CData Google Sheets Data Source. See "DSN Configuration" to configure an ODBC Data Source if you do not see the CData Google Sheets data source. Once you select the correct data source and exit the Data Link Properties dialog, a connection string will be built for you. It should resemble the following example:
   

   Provider=MSDASQL.1;Persist Security Info=False;DSN=MyGoogleSheetsDSN

7. Select the option to select from a list of tables for the data to import.
8. Select the tables you want to import and click Finish.

If you want to import specific columns or only the results that match your criteria, you can also select the option to write a query.