Import data from a database in Excel 2016 for Mac

Sometimes you need data that is stored outside of Excel, like in a database. In this case, you connect to the external data source first, and then you can work with the data.

Connect to an external data source

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, choose where you want the data to be: either on the existing sheet, on a new sheet, or in a PivotTable. Then click OK.

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 data:

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, choose where you want the data to be: either on the existing sheet, on a new sheet, or in a PivotTable. Then click OK.