Sometimes you need data that is stored outside of Excel, such as a database. In this case, you connect to the external data source first, and then you can work with the data.
Connect to an SQL Database
-
Select Data > Get External Data > New Database Query > SQL Server ODBC. The Connect to SQL Server ODBC Data Source dialog box appears.
-
Enter the server in the Server Name box, and optionally, enter the database in the Database Name box.
Get this information from the database administrator. -
Under Authentication, select a method from the list: Username/Password, Kerberos, or NTLM.
-
Enter credentials in the User Name and Password boxes.
-
Select Connect. The Navigator dialog box appears.
-
In the left pane, navigate to the table you want, and then select it.
-
Confirm the SQL statement on the right pane. You can change the SQL statement as you see fit.
-
To preview the data, select Run.
-
When you're ready, select Return Data. The Import Data dialog box appears.
-
Choose where you want the data to be added: either on the existing sheet, on a new sheet, or in a PivotTable.
-
To set connection properties on the Usage and Definition tabs of the Properties dialog box, select Properties. After you import the data, you can also select Data > Connections, and then in the Connection Properties dialog box, select Properties.
-
Select OK.
-
To ensure the connection is working, enter some data, and then select Data > Refresh All.
Connect to another database
If you want to use an external source that is not a SQL Database (for example, FileMaker Pro), you can use an Open Database Connectivity (ODBC) driver installed on your Mac. Information on drivers is available on this web page. Once the driver for your data source is installed, follow these steps:
-
Select Data > New Database Query > From Database.
-
Add the data source for your database, and then select OK.
-
At the SQL Server credentials prompt, enter the authentication method, the username and the password.
-
On the left, select the arrow next to the server to see the databases.
-
Select the arrow next to the database you want.
-
Select the table you want.
-
To preview the data, select Run.
-
When you're ready, select Return Data.
-
In the Import Data dialog box, choose where you want the data to be located: either on the existing sheet, on a new sheet, or in a PivotTable.
-
Select OK.
-
To ensure the connection is working, enter some data, and then select Data > Refresh All.
Clear your login credentials
If your permissions are not working, you may not to clear them first, and then log in.
-
Select Data > Connections. The Workbook Connections dialog box appears.
-
Select the connection you want in the list, and then select Clear Permissions.
See Also
TBD
To import data from a database, such as Microsoft SQL Server, you must have an ODBC driver that is compatible with Microsoft Query installed on your computer. Compatible ODBC drivers are available from third-party vendors. For more information, see ODBC drivers that are compatible with Excel for Mac. For more information about installing ODBC drivers, see Microsoft Query Help.
-
On the Data menu, point to Get External Data, and then click New Database Query.
-
Use Microsoft Query to connect to a data source and to create a query. When you have finished, click Return Data to import the data into Excel.
For more information about connecting to a data source and using Microsoft Query, see Microsoft Query Help.
-
In the Returning External Data to Microsoft Excel dialog box, do any of the following:
|
To |
Do this |
|---|---|
|
Choose query definition, refresh control, and data layout options |
Click Properties. |
|
Change the settings for a parameter query |
Click Parameters. |
|
Return the external data to the active sheet |
Click Existing sheet. On your sheet, click the cell where you want to place the upper-left corner of the external data range, and then click OK. |
|
Return the external data to a new sheet |
Click New sheet, and then click OK. Excel adds a new sheet to your workbook, and automatically pastes the external data range at the upper-left corner of the new sheet. |
|
Create a PivotTable by using the external data |
Click PivotTable, and then click OK. |
Notes:
-
Excel 2011 for Mac cannot import data from OLE DB or OLAP data sources.
-
By default, Excel for Mac uses tables to import data. To turn off tables when importing data, in the Returning External Data to Microsoft Excel dialog box, click Properties, and then clear the Use Table check box.
-
If the external data source you want to access is not on your local computer, you might need to contact the administrator of the database for a password, user permission, or other connection information.
