Best Ways to Import Data to Excel From Any Source

Excel is a preferred option for many people when it comes to processing data. Any calculations can be done with the help of Excel formulas, while Excel macros leave much space for all kinds of automatization.

But not all the data is stored in the form of spreadsheets. There are plenty of systems and tools for data collection and management. Thankfully, Excel is extremely flexible in terms of data importing. This article will explain how you can export data from the most popular sources to Excel.

Importing web data to Excel

Who needs to import data from the web to Excel

It’s not a secret that the internet is made of data. Thanks to the development of broadband networks and wireless technologies, all that data is available at your fingertips at any moment.

But sometimes, you may need to go beyond just reading the information. For example, when you are working on research and want to analyze large amounts of data. In this case, it would be helpful to import web data into specialized software, such as Excel.

How to import data from HTML to Excel

Of course, the simplest way to import data from a website to Excel is to copy-paste it from an HTML page by using a web browser. Sometimes that works pretty well; the simpler, the better. But what if the required data is on hundreds of pages? Or maybe, you don’t need all the information, but only specific fields. In such cases, the simple method will consume an unreasonable amount of time.

Excel has a built-in feature for scraping tables from web pages. To import data from a web source:

1. On the Data tab, select New Query > From Other Sources > From Web:

2. Enter the web page URL and click OK:

Excel will look for tables on the page.

3. In the Navigator window that opens, select the table that you want to import:

Use the Select multiple items checkbox if you want to import several tables. Click Load when you are ready to start the import.

4. Excel will insert the imported table into the current worksheet. You can refresh data by clicking the refresh button in the Workbook Queries task pane:

How to automatically import live data into Excel

Data is relevant as long as it’s fresh. If you need to update the information frequently to keep it up-to-date, manual data import will bring nothing but pain.

Thankfully, many websites provide REST API for accessing their data. Sometimes you have to pay for API access, sometimes it’s free. You need to make a request to get the data. The details on making requests can be found in the API reference of the data provider. API responses usually contain data in JSON format. There are free services that allow you to import JSON data to Excel. The best ones will even make API requests for you. Let’s see how you can use Coupler.io for such tasks.

Coupler.io is a data integration solution for importing data into Excel from different apps. You can load records from Shopify, HubSpot, Pipedrive, and many other sources. In addition to Excel, you can use Google Sheets or BigQuery as a destination for your data.

1. Sign in to Coupler.io with your Microsoft account.
2. Go to Importers and click Add new importer.
3. Select JSON as Source, and provide the request URL with your API key (if required):

Refer to the data provider documentation for details on available requests and how you can obtain an API key.

4. Specify API call parameters, as described in the API documentation. The only required parameter is HTTP method, which will be ‘GET’ in most cases.

5. In Destination, select Microsoft Excel:

6. Connect to your Microsoft Office account, and then select an existing workbook for import. You can specify an existing sheet or create a new one by typing its name:

7. Toggle the Automatic data refresh switch to set up the schedule for automatic data updates. When you are done with settings, click Save and Run:

Synchronizing the spreadsheets

How to sync data between Excel files

If you want to transfer data from one Excel workbook to another, copy-pasting is a good option. But that will not work if you want to keep the data up-to-date with the other file. There are several ways to sync data between Excel files.

You can use formulas for that. To fill a cell with data from another Excel workbook:

1. Make sure that the other workbook is open.
2. Select the cell to sync, then click the Formula Bar and type =.

3. Switch to the other workbook, click the cell that you want to import data from, and press Enter.

Excel will switch you back to the first workbook and fill the cell with the synced data. If the content of the cell in the other workbook changes, the synced cell will be updated.

If you see #REF! in the synced cell, it means that the other workbook is not open. Open it to enable data synchronization. Try another method if you are not comfortable with the formula bar.

• In the source file, i.e. the workbook that you want to import data from, select the cell, and then click Copy or press Ctrl-C:

2. Switch to the target workbook, select the cell, and then select Paste > Other Paste Options > Paste Link.

The formula for that cell will be filled with the link to the source cell in the other workbook.

There are many other options for syncing Excel data that you can learn in our guide on linking Excel files.

Besides the standard Excel features, there are online tools that allow you to import data from Excel to Excel, and let you synchronize your data in a more flexible way.

How to import data from Google Sheets to Excel

Google Sheets is almost as powerful as Excel in terms of data processing. But certain very important features are still available only in Excel. If you need these functions, you can transfer your data in several ways.

Import data from Google Sheets to Excel automatically

First of all, try using Coupler.io. It is an online tool that can import data from Google Sheets to Excel.

1. Sign in to your Coupler.io account and click Add New in the Importers section.
2. Select Google Sheets as Source, click Connect, and sign in to your Google account:

3. Select the workbook and the sheet that you would like to export data from:

4. Select the range of cells, or skip this step to export all the data.
5. In Destination, select Microsoft Excel, click Continue, and then sign in to your Microsoft account.
6. Select an existing workbook and the worksheet. If you want to create a new worksheet, just type its name:

7. Specify the cell address or keep the default ‘A1’ value.
8. Specify the import mode. The default option is ‘Replace’.
9. Now you can click Save and Run to start the import immediately.

Or you can toggle Automatic data refresh to configure automatic data updates:

Import data from Google Sheets to Excel as HTML

The second option is to use the built-in data import feature of Excel:

1. In your Google spreadsheet, go to File > Share > Publish to web:

2. Select the sheet that contains the table, and specify Comma-separated values (.csv).

If you want the data in Excel to be updated when the Google sheet is changed, select the Automatically republish when changes are made checkbox:

3. Press Ctrl-C to copy the link.
4. Go to your Excel workbook and perform the steps described in the section about importing data from HTML above.

If you choose the second option, keep in mind that your data will become accessible by others. If the information is confidential or sensitive, using Coupler.io is preferred.

Importing data from other sources

How to import data from SQL server to Excel

If your data is stored in databases at an SQL server, the simplest way to make it accessible to a wide spectrum of users is to export it to Excel. You can use the native Excel query function.

1. In the Data tab, select New Query > From Database > From SQL Server Database:

2. Specify the name of the server and the database (or you will be able to specify it later):

You can also provide an SQL statement to narrow the range of exported data.

3. Choose to use your current window credentials to access the database, or provide the username and the password. Click Connect.
4. In the next window, select the database and tables that you want to export.
5. Select the name for the new data connection file, and then click Finish.
6. In the next window, you can select where to insert the imported data. Select Existing worksheet and provide the cell address, if needed.

The data will be imported and pasted into the worksheet.

If you use SQL Server Management Studio (SSMS), there is a feature for exporting data to various destinations, including Excel spreadsheets.

1. In Object Explorer, right-click the database with the source data, and then select Tasks > Export Data.
2. In the SQL Server Import and Export Wizard window that opens, click Next.
3. Select SQL Server Native Client 11.0 in the Data source drop-down box.
4. Select Use Windows Authentication in the Authentication area, and then click Next.
5. In the Choose a destination window, specify Microsoft Excel as the destination, browse to the target file in the Excel file path, and select the output file format.
6. In the next window, you can choose to write a query for data export. If you want to copy the tables without changes, just click Next.
7. The list of tables available for export will be displayed. Select the tables that you want to export. In the Destination column, you can specify the name of the worksheet for each table.
8. In the Save and Run Package window, select Run immediately and click Next.
9. Make sure that all parameters are correct, and then click Finish.

The selected data will be exported to the Excel workbook. When the wizard finishes all the tasks, click Close.

How to import data from a text file to Excel

The tables can also be stored in plain text. Such files are similar to regular text files with the only exception that commas have a special function there — they separate cells from each other. The format is called CSV — comma-separated values.

Excel can open .csv files directly. The content of such a file will be rendered as a table. If you edit a CSV file in Excel and then try to save it, you will see the following warning:

Click Yes if you want to continue using this file as CSV in the future. If you click No, the Save as dialogue will be opened, where you will be able to select the format and the destination for the file.

There are other delimiters that can be used to store tables in text format: tabs, pipes (‘|’), spaces, semicolons, etc. To open such files with Excel, instead of trying to open them directly, use the Power Query function.

1. Go to Data > New Query > From File > From Text.
2. Select the text file and click Import.
3. The Query Editor window will open. Make sure that the table looks good and click Close & Load:

The imported table will be inserted into your current worksheet.

How to import data to Excel from PDF

If your tables are in PDF, but you want them in Excel, you can try to import them.

1. Go to the Data tab, and then select New Query > From File > From PDF.
2. Select the tables that you want to import in the Navigator window, and then click Load.

The tables will be extracted from PDF and pasted into your Excel workbook. If you don’t have the From PDF option on the menu, you can use the following workaround to import data from PDF into Excel:

1. Copy the table text in the PDF file by selecting it and pressing Ctrl-C.
2. Open Microsoft Word and press Ctrl-V.
3. Check if Word managed to render the text data correctly:
   1. Press Ctrl-A to select everything.
   2. On the Home tab, click the table menu, and then select All borders:

4. If the table looks good, proceed to the final step. Otherwise, go to the Insert tab, and then select Table > Convert Text to Table:

5. Specify the number of columns in the original table, and then click OK:

6. Copy the entire table, go to Excel, and press Ctrl-V.

The table will be inserted into the current worksheet. If you opt for this workaround, no automatic data synchronization will be available.

Discover many other sources for importing data to Excel

Excel provides you with many powerful features for data processing. You can bring virtually any data to Excel by importing it from various sources. If you didn’t find a good solution in this article, discover other Microsoft Excel integrations to pick what fits your needs best. For example, you can take a look at our article on ClickUp export to Excel to discover how Coupler.io can help you with analysing your project data.

Back to Blog