Managing External Data Connection in Advanced Excel

External Data Connections are SQL Server database, another workbook of Excel, or any other database that can easily get connected in Excel. External data will help to add extra features or information to the data model in excel. There is a refresh button in Excel that will represent the connection with its recent data whether the new data has been inserted or deleted.

Follow the further steps to add external data connection using advanced excel.

Step 1: Select the Data tab from the ribbon.

Step 2: Select Connections from the Connections section.

Step 3: Add new connections by selecting Add in Workbook Connections.

Step 4: Select the external connection which we want to insert from Existing Connections.

Step 5: If we want to add more external connections then select Browse for More in the Existing Connections tab.

Step 6: Go back and select the required connection from Workbook Connections.

Step 7: The connection Properties tab will appear where we can choose four types of refresh under Refresh Control.

• Enable background refresh: It enables the user to use excel without waiting for several minutes but in the meantime, we can’t use any query to retrieve the data from Data Model.
• Refresh every: This will refresh the data automatically after the selected time duration.
• Refresh data when opening the file: This will refresh the file when the file will be opened so that the file will be updated according to the insertion or deletion of the data.
• Refresh this connection on Refresh All: The connection will update itself every time when Refresh All is selected from the Data tab as shown below.

Step 8: You can also refresh under the data tab then in the connections group select Refresh All.