Import data from Web to Excel Mac

1. On the File menu, click Import.

2. In the Import dialog box, click the option for the type of file that you want to import, and then click Import.

3. In the Choose a File dialog box, locate and click the CSV, HTML, or text file that you want to use as an external data range, and then click Get Data.

4. Follow the steps in the Text Import Wizard, where you can specify how you want to divide the text into columns and other formatting options. When you have completed step 3 of the wizard, click Finish.

5. In the Import Data dialog box, click Properties to set query definition, refresh control, and data layout options for the external data that you are importing. When you have finished, click OK to return to the Import Data dialog box.

6. Do one of the following:

Import the Data Open Excel. Click the Data tab and choose From Web in the Get & Transform Data group. In the dialog box, select Basic and type or paste the URL in the box. In the Navigator box, select the tables you wish to import. Click a table to import from the Navigator box. To add dynamic data from a website to a spreadsheet, click the From Web button under the Get External Data section of the Data tab in Excel. Enter a website address that you want to get data from, and click Go.

Note: You can change the layout or properties for the imported data at any time. On the Data menu, point to Get External Data, and then click either Edit Text Import or Data Range Properties. If you select Edit Text Import, select the file that you imported originally, and then make changes to the external data in the Text Import Wizard. By selecting Data Range Properties, you can set query definition, refresh control, and data layout options for the external data.

Sometimes you need data that is stored outside of Excel, like in a database. In this case, you connect to the external data source first, and then you can work with the data.

Newer versionsOffice 2011

You can use an external data source, but it depends on the type of source.

If the source is a SQL Database

1. On the Data tab, click New Database Query

2. Click SQL Server ODBC.

3. The Connect to SQL Server ODBC Data Source dialog box appears. Get the server information from your database administrator, and type it in this dialog box. Click Connect when you're done.

4. On the left, click the arrow next to the server to see the databases.

5. Click the arrow next to the database you want.

6. Then click the name of the table you want.

7. To get a preview of what the data will look like, click Run.

8. When you're ready to bring the data into Excel, click Return Data.

9. In the Import Data dialog box, choose where you want the data to be: either on the existing sheet, on a new sheet, or in a PivotTable. Then click OK.

If the source is not a SQL Database

If you want to use an external source that is not a SQL Database (for example, FileMaker Pro), you will need an Open Database Connectivity (ODBC) driver installed on your Mac. Information on drivers is available on this web page. Once the driver for your source is installed, you can follow these steps to use the data:

1. On the Data tab, click New Database Query

2. Click From Database.

3. Add the data source for your database, and then click OK.

4. On the left, click the arrow next to the server to see the databases.

5. Click the arrow next to the database you want.

6. Then click the name of the table you want.

8. When you're ready to bring the data into Excel, click Return Data.

9. In the Import Data dialog box, choose where you want the data to be: either on the existing sheet, on a new sheet, or in a PivotTable. Then click OK.

To import data from a database, such as Microsoft SQL Server, you must have an ODBC driver that is compatible with Microsoft Query installed on your computer. Compatible ODBC drivers are available from third-party vendors. For more information, see ODBC drivers that are compatible with Excel for Mac. For more information about installing ODBC drivers, see Microsoft Query Help.

1. On the Data menu, point to Get External Data, and then click New Database Query.

2. Use Microsoft Query to connect to a data source and to create a query. When you have finished, click Return Data to import the data into Excel.

   For more information about connecting to a data source and using Microsoft Query, see Microsoft Query Help.

3. In the Returning External Data to Microsoft Excel dialog box, do any of the following:

Notes:

• Excel 2011 for Mac cannot import data from OLE DB or OLAP data sources.

• By default, Excel for Mac uses tables to import data. To turn off tables when importing data, in the Returning External Data to Microsoft Excel dialog box, click Properties, and then clear the Use Table check box.

• If the external data source you want to access is not on your local computer, you might need to contact the administrator of the database for a password, user permission, or other connection information.

Last updated on 07 February, 2022

The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.

One little-known feature of Excel is its ability to import web pages. If you can access data on a website, it's easy to convert it to an Excel spreadsheet if the page is properly set up. This import capability helps you analyze web data using Excel's familiar formulas and interfaces.

Instructions in this article apply to Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac.

Excel is a spreadsheet application optimized for evaluating information in a two-dimensional grid. If you're going to import data from a web page into Excel, the best format is as a table. Excel imports every table on a web page, just specific tables, or even all the text on the page.

When the imported web data is not structured, it requires restructuring before you can work with it.

After you've identified the website that contains the information you require, you can import the data directly into Excel using the From Web tool with only a few clicks, customizing import options along the way.

Here's how to import a data table from the web on a PC:

1. Open Excel.

2. Select the Data tab and choose From Web in the Get & Transform Data group. The From Web dialog box will open.

3. Select Basic, type or paste the URL in the box, and select OK. If prompted, choose to Connect to the website.

4. In the Navigator box, select the tables to import. Excel isolates content blocks (text, tables, and graphics) if it knows how to parse them. To import more than one data asset, place a check mark next to Select multiple items.

5. After you select a table, a preview appears on the right side of the box. If it's the table you want, select Load. The table appears in a new worksheet.

6. The right side of the screen displays the Queries & Connections pane. If you've imported multiple tables, select a table from the Queries & Connections pane to view it.

Edit Data Before Importing It

If the dataset you want is very large or not formatted to your expectations, modify it in the Query Editor before loading the data from the website into Excel.

In the Navigator box, select Transform Data instead of Load. Excel loads the table into the Query Editor instead of the spreadsheet. This tool opens the table in a specialized box that allows you to:

• Manage the query
• Choose or remove columns and rows in the table
• Sort data
• Split columns
• Group and replace values
• Combine the table with other data sources
• Adjust the parameters of the table

The Query Editor offers advanced functionality that's more akin to a database environment (like Microsoft Access) than the familiar spreadsheet tools of Excel.

Work with Imported Data

After your web data loads into Excel, you'll have access to the Query Tools ribbon. This new set of commands supports data-source editing (through the Query Editor), refreshing from the original data source, merging and appending with other queries in the workbook, and sharing the scraped data with other Excel users.

You cannot import data from a website into Excel for Mac. You'll need to save the website to your computer using your web browser's Save As function. After you've saved the website, import the page's HTML data into an Excel spreadsheet with the following method:

1. Open Excel.

2. Select Data > From HTML.

3. Navigate to the location of your saved HTML web page, select it, and select Open.

4. Excel automatically imports the entire web page into a new workbook. From here, clean up the chart to only include the needed data.

While the From HTML method for Mac isn't as clean or controlled as the From Web option for PC, it still allows data from a web page to be imported into an Excel spreadsheet.

Thanks for letting us know!

Tell us why!