【Import Data from External Program into Excel】Detailed Guide ▷ 2022

MS Excel is another program designed by Microsoft and which is compatible with windows, macOS and even linux. It is recognized for being a in which you can enjoy different tools and features to customize the work done within it.

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

On this occasion, 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 with which you work.

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

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

The way to configure it is as follows:

• Enter in Excel and inside the tab File, Archiveclick Options.
• 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 datais that you can periodically analyze such information in Excel without copying the data repeatedly.

Keep in mind that after connecting to external data, you will be able to Automatically refresh Excel workbooks from the data source, each time the data source is updated with new information. This will surely help you save a lot of time and troublebecause 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 to which they will be able to connect varies between each data object.

In this sense, you will be able to use and update the data connected in services of Excel, but there are limitations in some of them. For more information on this, we recommend you visit 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 Excelsuch as in a database. Specifically in these cases, you can connect to the external data source in order to work with it.

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

windows

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

You just have 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 between showing: all connections, Connections in this book (for recently used connections), Connection files on this computer (for connections available on the equipment) or in Network connection files (for connections available from a network-accessible connection file).
• Select the type of connection you want and then click Open.
• Inside the dialog import datain Select how you want to view the data in the workbookchoose the option that suits your needs.
• Then in the section where do you want to put the data?, choose between:

• existing spreadsheet (to place the PivotTable or PivotChart report on an existing worksheet) 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. Then, you must choose the initial cell of the spreadsheet and finally, click on expand dialog.

• To place the 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 dialogs 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 an 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 is displayed Connect to SQL Server ODBC data sourceenter the server information that the database administrator will provide you.
• Click on Connect when you’re 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 it table name what you wish.
• If you want to preview the data, click Run.
• When you are going to enter the data in Excel, select in return data.
• Inside the dialog import datachoose where to save the data: either in the existing sheet, in a new sheet, or in a PivotTable.
• Finally, click To accept.

For his part, if the source is NOT in a SQL databaseyou should know that you need 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 go into from database.
• Proceed to add data source for the database. Then tap on To accept.
• On the left, click on the arrow next to the server to see the databases.
• Then click the arrow next to the database you want.
• Click on it table name what you wish.
• If you want to get a preview, click on Run.
• Once you’re ready to enter the data into Excel, select return data.
• Inside the dialog import datachoose the location where you are going to save the data and finally click on To accept.