Connect OLE DB data to your workbook

Databases are great for storing information, but sometimes you want to use or analyze that data in Excel. Some data sources need an OLE DB connection for Excel to get the data you want. You can create a connection to an OLE DB data source, then refresh that connection whenever the database is updated.

Important: Connections to external data might be disabled on your computer. To connect to data when you open a workbook, enable data connections by using the Trust Center bar, or save the workbook in a trusted location.

Use Excel's Get & Transform (Power Query) experience to connect to an Excel workbook.

  1. Click the Data tab, then Get Data > From Other Sources > From OLEDB. If you don't see the Get Data button, follow the Data Connection Wizard instructions below.

  2. In the From OLE DB dialog, enter your connection string, then press OK.

  3. In the OLEDB Provider dialog, select from the Default or Custom, Windows, or Database connection options, enter the appropriate credentials, then click Connect.

  4. In the Navigator dialog, select the Database and tables or queries you want to connect to, then press Load or Edit.

  5. In the Power Query Editor, press Close & Load.

Data Connection Wizard

  1. On the Data tab, in the Get External Data group, click From Other Sources.

    Get External Data group on Data tab

  2. Click From Data Connection Wizard.

  3. In the wizard, choose Other/Advanced and click Next.

  4. Choose the type of OLE DB provider you want to connect to, and click Next.

  5. Select the tables or queries you want by following the directions in the rest of the wizard, entering server names, user names, passwords, and any other information as needed.

  6. You can click Finish, or click Next to change details for the connection.

  7. In the Import Data dialog box that appears, choose where to put the data in your workbook and whether to view the data as a table, PivotTable report, or PivotChart.

  8. Click the Properties button to set advanced properties for the connection, such as OLAP settings or options for refreshing the connected data.

  9. Optionally, you can add the data to the Data Model so that you can combine your data with other tables or data from other sources, create relationships between tables, and do much more than you can with a basic PivotTable.

  10. Click OK to finish.

In Excel 2010 and 2013, there are two methods of creating a connection to another workbook. The recommended method is to use Power Query, which is available if you download the Power Query add-in. If you can't download the Power Query add-in, you can use the Data Connection Wizard.

  1. Click the Data tab, then Get Data > From Other Sources > From OLEDB. If you don't see the Get Data button, follow the Data Connection Wizard instructions below.

  2. In the From OLE DB dialog, enter your connection string, then press OK.

  3. In the OLEDB Provider dialog, select from the Default or Custom, Windows, or Database connection options, enter the appropriate credentials, then click Connect.

  4. In the Navigator dialog, select the Database and tables or queries you want to connect to, then press Load or Edit.

  5. In the Power Query Editor, press Close & Load.

Data Connection Wizard

  1. On the Data tab, in the Get External Data group, click From Other Sources.

    Get External Data group on Data tab

  2. Click From Data Connection Wizard.

  3. In the wizard, choose Other/Advanced and click Next.

  4. Choose the type of OLE DB provider you want to connect to, and click Next.

  5. Select the tables or queries you want by following the directions in the rest of the wizard, entering server names, user names, passwords, and any other information as needed.

  6. You can click Finish, or click Next to change details for the connection.

  7. In the Import Data dialog box that appears, choose where to put the data in your workbook and whether to view the data as a table, PivotTable report, or PivotChart.

  8. Click the Properties button to set advanced properties for the connection, such as OLAP settings or options for refreshing the connected data.

  9. Beginning with Excel 2013, you can add the data to the Data Model so that you can combine your data with other tables or data from other sources, create relationships between tables, and do much more than you can with a basic PivotTable.

  10. Click OK to finish.

  1. On the Data tab, in the Get External Data group, click From Other Sources.

  2. Click From Data Connection Wizard.

  3. In the wizard, choose Other/Advanced and click Next.

  4. Choose the type of OLE DB provider you want to connect to, and click Next.

  5. Select the tables or queries you want by following the directions in the rest of the wizard, entering server names, user names, passwords, and any other information as needed.

  6. You can click Finish, or click Next to change details for the connection.

  7. In the Import Data dialog box that appears, choose where to put the data in your workbook and whether to view the data as a table, PivotTable report, or PivotChart.

  8. Click the Properties button to set advanced properties for the connection, such as OLAP settings or options for refreshing the connected data.

  9. Click OK to finish.

Keep the data in your workbook up to date

Now that you're connected to your OLE DB data, you'll always want the latest data in your workbook. Go to Data > Refresh All to get the latest data. For more information on refreshing, go to Refresh an OLE DB data connection.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Power Query is known as Get & Transform in Excel 2016

Import data from external data sources

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×