Import Data Into Excel

What Is Import Data In Excel?

The options to import data into Excel enable one to import data directly from external sources such as text files and advanced servers. We can perform the importing action using the Get & Transform Data group options in the Data tab.

Users can use the import data into Excel options to import data into a worksheet from a CSV file, web, another workbook, or database.

For example, the following image shows a worksheet.

Import Data Into Excel - Definition Example - 1

Importing the Forbes website sitemap index data into the above workbook is the task.

Then, it is a scenario of import data into Excel from web, and here is how to achieve the required output.

Import Data Into Excel - Definition Example - 2

In the above import data into Excel from web example, we first choose the From Web option in the Data tab.

The From Web window opens, where we update the URL of the site index link of the Forbes website.

Next, clicking OK will close the From Web window, and the Navigator window will open. Select the sitemap table to enable Power Query to preview it in the Table View pane.

After that, click Load to make Excel Power Query to transform the data and load the data from the web into a new worksheet in Excel. Finally, double-click the target Excel worksheet tab to rename the sheet according to our requirements.

What Is Import Data In Excel?

The methods to import data into Excelhelp us import data from other sources into the required workbook.
Users can use the data importing techniques into a target worksheet to get data from XML files, text files, Excel workbooks, databases, and online sources and services.
We can use the options in the Get & Transform Data group in the Data tab to import data into an Excel workbook from external sources without using macros.
Excel offers options to import data from PDFs and pictures into a worksheet in Excel 365 and the latest versions.

How To Import Data In Excel?

While we can write a VBA macro to import data into Excel, we shall see different methods to import the required data in a workbook without VBA coding.

We can import data into Excel using the following three methods:

#1 – Import Data from Another Excel Workbook

#2 – Import Data from MS Access to Excel

#3 – Import Data from Text File to Excel

We shall see the steps involved in each method to automatically import data into Excel with examples.

#1 – Import Data From Another Excel Workbook

The steps to import data into an Excel file from another workbook are as follows:

Choose the Data tab → Existing Connections option in the target workbook.
The Connections tab in the Existing Connections window will open, where the Show field must be All Connections. Next, click Browse for More.
The Select Data Source window opens, where we must browse and choose the Excel file to import the required data. Next, click Open.
The Select Table window opens, listing all the worksheets in the source or chosen Excel workbook. Select the sheet containing the data we aim to import into the target workbook. Also, check the First row of data contains column headers option checkbox and click OK.
The Import Data window opens. Select the required data format and the location to display the imported data in the target workbook.
Click OK to view the data imported from the source Excel file into the target workbook.

Basic Example

The following image shows a worksheet in a workbook.

The task is to import data from another Excel workbook, Fruit_Category_Data.

Then, the steps are as follows:

Step 1: Select the Data tab → Choose the Existing Connections option.

Import Data From Another Excel Workbook - Basic Example1 - Step 1

Step 2: The Connections tab in the Existing Connections window will open.

Let the Show field be All Connections to ensure we can view all the existing connections in the target workbook and choose the Browse for More option.

Step 3: The Select Data Source window opens, which enables us to locate and choose the required workbook stored at a location on the computer.

Next, click Open.

Step 4: The Select Table window opens, where we click the worksheet containing the required table to import from the chosen workbook.

Ensure the First row of data contains column headers option is checked, and click OK.

Step 5: The Import Data window opens.

Choose the required data format option in the first section and update the location where we want to view the data in the target worksheet in the second section.

In this example, we shall choose Table in the first section. After that, we shall select the Existing worksheet option and enter the Excel absolute reference to the target cell in the existing worksheet in the second section.

Next, clicking OK will display the data as a table imported from the source workbook into the target Excel file.

Import Data From Another Excel Workbook - Basic Example1 - Step 5b

Step 6: Double-click the target sheet tab to rename it.

#2 – Import Data From MS Access To Excel

The steps to automatically import data into Excel from MS Access are as follows:

Choose the Data tab → From Access option in the target workbook.
The Select Data Source window opens, where we can browse and choose the required MS Access file from which we need to import data. Next, click Open.
The Select Table window opens, listing all the tables in the source or chosen MS Access file. Check the Enable selection of multiple tables option checkbox. Next, choose the table containing the data we aim to import into the target workbook and click OK.
The Import Data window opens. Select the required data format and the cell address to display the imported data in the target workbook.
Click OK to view the data imported from the source MS Access file into the target workbook.

Basic Example

The following image shows a workbook.

Importing an MS Access file, Employee_Data, data into the active sheet is the aim.

Then, the steps are as follows:

Step 1: Choose the Data tab → From Access option.

Import Data From MS Access To Excel - Basic Example2 - Step 1

Step 2: The Select Data Source window will open. Browse and choose the required MS Access file from the computer, and click Open.

Step 3: The Select Table window opens.

Select the Enable selection of multiple tables checkbox and check the required table option checkbox to choose the table whose content we must import into the target Excel file.

Next, click OK.

Step 4: The Import Data window will open.

We shall choose the Table option as the format to view the imported data. Next, choose the Existing worksheet option and enter the target cell reference where we want to view the import data.

Finally, clicking OK will show the data imported from the chosen MS Access file in the active worksheet.

Step 5: Double-click the active worksheet tab and update the name to fit our requirements.

Import Data From MS Access To Excel - Basic Example2 - Step 5

#3 – Import Data From Text File To Excel

The steps to import data into Excel from Notepad or a text file are as follows:

Choose the Data tab → From Text/CSV option in the target workbook.
The Import Data window opens. Browse and select the required text file and click Import.
The text file query window opens. We can set the required Delimiter and Data Type Detection settings by clicking the respective drop-down buttons and choosing the required options.
Select Load to view the data imported from the source text file into the target workbook.

Basic Example

The following image shows a workbook.

The task is to import data from the Notepad file Invoice Data into the target workbook.

Then, the steps to import data into Excel from Notepad file using the From Text/CSV option in the Data tab are as follows:

Step 1: Choose the Data tab → From Text/CSV option.

Step 2: The Import Data window will open, where we can search and choose the required text file.

Next, click Import.

Step 3: The text file query window will open. We shall set the three fields at the top, as depicted below:

The window will preview the text file data, which will get imported as a table in the target workbook.

Finally, click Load to view the data imported from the source text file into a new worksheet in the target workbook. Also, the Queries & Connections pane opens on the right of the worksheet, with the corresponding query, Invoice Data, highlighted, as shown below.

Step 4: Double-click the target worksheet tab to rename the sheet according to our requirements.

Import Data From Text File To Excel - Basic Example3 - Step 4

Thus, the above examples show that it is not necessary always to use VBA macro to import data into Excel. Instead, we can import the data into Excel from different sources using the Data tab options.

Important Things To Note

When we import data into Excelfrom other sources containing multiple tables, we must choose the specific table to import the required data.
Excel allows us to import data from external sources into the current or a new worksheet in the target workbook.
Excel offers Table tools in the Design tab when importing data from another Excel file or MS Access file. On the other hand, we get the Table and Query tools in the Design and Query tabs when importing XML and text file data into Excel to manage the imported data.

Frequently Asked Questions (FAQs)

1. How do I import data from XML to Excel?

You can import data from XML to Excel using the following steps, explained with an example.

The following image shows a worksheet in a workbook.

The aim is to import an XML file, Employee Appraisal Data, data into the above workbook.

• Step 1: Choose Data → Get Data → From File → From XML.

• Step 2: The Import Data window opens, where you can browse and select the required XML file.

Next, click Import.

• Step 3: The Navigator window opens, where you must click the table under the chosen XML file for a preview.

If the data appears to be correct, click Load.

The above steps will import the chosen XML file data into a new sheet in the target workbook. Also, the Queries & Connections pane will open, highlighting the imported data table.

• Step 4: Double-click the new sheet tab to rename the sheet according to your requirements.

2. How do I import data from a PDF into Excel?

You may import data from a PDF into Excel using the following steps:

1) Open the Excel worksheet.
2) Choose Data → Get Data → From File → From PDF.
3) The Import Data window opens, where you must choose the PDF file and click Import.
4) The Navigator panel opens, showing the tables and pages in the source PDF file. Click the required table you aim to import from the PDF file into Excel for a preview.
5) Finally, if the chosen table data is correct, click Load. The table gets imported to the Excel worksheet.

3. Can you import data from a picture into Excel?

You can import data from a picture into Excel, and the steps are as follows:

1) Ensure the image to import into Excel is saved on the computer.
2) Open the Excel program and choose the Data tab.
3) Choose From Picture → Picture From File.
4) Browse the image on the computer and upload it to Excel.
5) A dialog box appears on the right of the worksheet, and it analyzes the data. When the analysis is complete, it previews the data and gives the options to Insert Data or Review.
6) If the data is correct, choose Insert Data. Otherwise, click Review to analyze the data that Excel has captured.
7) After insertion, all the data from the picture will appear formatted in the target worksheet.

Download Template

This article must be helpful to understand the Import Data Into Excel, with its formula and examples. You can download the template here to use it instantly.

Import Data Into Excel Download

Import Data Into Excel