How to import data from external programs into Microsoft Excel? step by step guide

Microsoft Excel is another program designed by microsoft and that it is compatible with Windows, macOS and even Linux. He is recognized for being a spreadsheet 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 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 depending on the operating system you are working with.

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

First of all, you should be aware that it is possible that the Connections to external data in Excel are disabled on your team. In order to connect to data when opening a workbook, you have 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 repeatedly copying the data.

Keep in mind that after connecting to external data, you will be able to automatically update excel workbooks from data source, each time the data source is updated with new information. Surely this will help you save a lot of time and troublebecause performing these operations manually is prone to errors.

Apart from that, you should know that there are several data objects (such as a pivot table report or external data range) that can be used to connect to different data sources. But the type of source they will be able to connect to varies between each data object.

In this sense, you will be able to use and update the connected data 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 Excel, such 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 packets 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:

• Go to the tab Data and click Existing connections. The dialog box will open automatically. 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 of the cell range 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, press on expand dialog.

• To drop pivot table report on a new worksheet from cell a1, you should click 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 on 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 box 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 be able 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 go to enter the data in Excel, select in return data.
• Inside the dialog import datachoose the location where you are going to save the data: either in the existing sheet, in a new sheet or in a pivot table.
• Finally, click on To accept.

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 go into From database.
• proceed to add the origin of data for the database. Then tap on To accept.
• On the left, click on the arrow next to the server to be able to see the databases.
• Then click the arrow next to the database you want.
• Click on the table name what you wish.
• If you want to 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.