Import External Program Data into Excel  Detailed Guide ▷ 2020

Microsoft Excel is another program designed by Microsoft and that is compatible with Windows, macOS and even Linux. It is recognized for being a spreadsheet in which you can enjoy different tools and features to customize the work carried out within it.

It has a very powerful interface, in which you can work with personal data and all kinds of information, even external to Excel and then import them into the program.

In this opportunity, we want to explain everything related to how to import data from external programs into Excel, with the help of a step-by-step guide on the subject. Keep in mind that the procedure may vary according to the operating system you are working with.

What should we consider before importing data from external programs into Excel?

What to keep in mind before importing data from external programs into Excel?

First of all, you should be aware that it is possible that Connections to external data in Excel are disabled on your team. To be able to connect to the data when opening a workbook, you have to enable the data connections located in the toolbar. Trust Center. You can also do this by placing the book in a trusted location.

The way to configure it is as follows:

  • Login to Excel and inside the tab Archive, click Choices.
  • Click on Trust Center.
  • Then select Trust Center Settings.
  • In the left bar, enter the section corresponding to Trusted locations.
  • Choose the location what you wish.

The main benefit you will get by connecting to external data, is that you can periodically analyze said information in Excel without copying the data repeatedly.

Keep in mind that after connecting to external data, you can automatically update Excel workbooks from the data source, each time the data source is updated with new information. This will definitely help you save a lot of time and trouble, because performing these operations manually is prone to errors.

Other than that, you should know that there are multiple data objects (such as a PivotTable report or an external data range) that can be used to connect to different data sources. But the type of source that they can connect to varies between each data object.

In this sense, you can use and update the data connected in services of Excel, but there are limitations in some of them. For more information, we recommend visiting the Office website.

Steps to import data packages from an external program into an Excel table

Many times, it is necessary import data that is stored outside of Excel, as for example in a database. Specifically in these cases, you can connect to the external data source in order to work with it.

Then, We explain how to do it according to your computer:

Windows

The procedure for import data packets from an external program into an Excel table in Windows It is easier than it seems to be.

You just need to pay attention to the following steps:

  • Enter the tab Data and click Existing connections. The dialog box will automatically open Existing connections.
  • In the drop-down list To show choose to show: All connections, Connections in this book (for recently used connections), Connection files on this computer (for connections available on the device) or on Network connection files (for available connections from a connection file accessible from the network).
  • Select the type of connection you want and then click Open.
  • Inside the dialog Import data, in Select how you want to view the data in the workbook, choose the option that suits your needs.
  • Then in the section of Where do you want to put the data?, choose between:
    • Existing spreadsheet (to place the PivotTable or PivotChart report on an existing spreadsheet), and then type the address of the first cell in the range of cells where you will place the report.
    • You can also click on Contract to temporarily hide the dialog box. Then, you must choose the initial cell of the spreadsheet and finally, click on Expand dialog.
    • To drop pivot table report on a new worksheet starting from cell a1, you must click on New spreadsheet.
    • Optionally, you can modify the connection properties by clicking on Properties. After that, in the dialog boxes Connection Properties, External Data Range, or XML Mapping Properties and, followed by that, click To accept.

MacOS

Depending on the type of origin, the procedure varies. In case the source is a SQL database, the procedure to follow is:

  • Enter the tab Data within Excel.
  • Click on New database query.
  • Select in SQL Server / ODBC Database.
  • When the dialog box is displayed Connect to SQL Server ODBC data source, write the server information that the database administrator will provide you.
  • Click on Connect when done.
  • On the left, click on the arrow next to the server to see the database.
  • Click on the arrow next to the database you want.
  • Click on the table name what you wish.
  • If you want to preview the data, click Run.
  • When you go to enter the data in Excel, select in Return data.
  • Inside the dialog Import data, choose the location where you want to save the data: either on the existing sheet, on a new sheet or on a pivot table.
  • Finally, click on To accept.

Import SQL database to Excel table

For its part, if the source is NOT in a SQL databaseYou should know that you require an open database connectivity (ODBC) driver installed on your Mac.

Once with that installed, you must do the following:

  • Enter the tab Data and then select New database query.
  • Then enter From database.
  • Proceed to add the data source for the database. Then click on To accept.
  • On the left, click on the arrow next to the server to see the databases.
  • Then, click on the arrow next to the database you want.
  • Click on the table name what you wish.
  • If you want to preview, click Run.
  • Once you are ready to enter the data in Excel, select Return data.
  • Inside the dialog Import data, choose the location where you are going to save the data and finally click on To accept.

Import without SQL database to Excel table

If you have any questions, leave them in the comments, we will answer you as soon as possible, and it will surely be of great help to more members of the community. Thank you! 😉

You may be interested:

Rate this post

Leave a Comment