Sometimes you need a PivotTable for data that is stored outside of Excel, like in a database. In this case, you connect to the external data source first, and then use a PivotTable to summarize, analyze, explore, and present that data.
Use an external data source to create a PivotTable
You can use an external data source, but it depends on the type of source.
If the source is a SQL Database
-
On the Data tab, click New Database Query
-
Click SQL Server ODBC.
-
The Connect to SQL Server ODBC Data Source dialog box appears. Get the server information from your database administrator, and type it in this dialog box. Click Connect when you're done.
-
On the left, click the arrow next to the server to see the databases.
-
Click the arrow next to the database you want.
-
Then click the name of the table you want.
-
To get a preview of what the data will look like, click Run.
-
When you're ready to bring the data into Excel, click Return Data.
-
In the Import Data dialog box, click PivotTable, and then click OK.
Excel creates an empty PivotTable and displays the PivotTable Builder. Add, remove, or move the fields as you see fit.
If the source is not a SQL Database
If you want to use an external source that is not a SQL Database, you will need an Open Database Connectivity (ODBC) driver installed on your Mac. Information on drivers is available on this web page. Once the driver for your source is installed, you can follow these steps to use the external data in a PivotTable:
-
On the Data tab, click New Database Query
-
Click From Database.
-
Add the data source for your database, and then click OK.
-
On the left, click the arrow next to the server to see the databases.
-
Click the arrow next to the database you want.
-
Then click the name of the table you want.
-
To get a preview of what the data will look like, click Run.
-
When you're ready to bring the data into Excel, click Return Data.
-
In the Import Data dialog box, click PivotTable, and then click OK.
Excel creates an empty PivotTable and displays the PivotTable Builder. Add, remove, or move the fields as you see fit.
