How to import table from website using the get data function

Hi Leslie,

This is April, I'll be happy to assist you. I understand you need help to import data table from website using the "Get Data" function in Excel. Let me help you out.

1. What is the exact URL of the webpage and is the table you need rendered as plain HTML (static) or loaded dynamically via script/AJAX? after login?
2. Which tool are you using (Excel version, Power BI, Google Sheets etc.)? and what error or behavior do you see when you try "Get Data from Web"?

To import a table from a website using the "Get Data" function in Excel instead of copying and pasting) you can use Power Query. Here's how to do it step by step:

1. Launch Excel and open or create a workbook.
2. Go to the Data tab on the ribbon.
3. Select Get Data > From Other Sources > From Web
4. When the dialog box opens, paste the website's URL where the table is located.
5. Click OK. Excel will connect to the page and open a Navigator window showing detected tables and other data items.
6. Choose the table(s) you want using the preview pane to check the contents.
7. Either click Load to bring it straight into your worksheet or click Transform Data first if you need to clean, reformat or otherwise adjust the data using Power Query.

Make sure the table is HTML-based, not just an image or embedded inside JavaScript. If the table doesn't load correctly, try switching from Basic to Advanced in the web connection dialog.

I hope this helps. If you need further assistance you can reply to this message.

Thank you,

April P.

Independent Advisor

Hi Leslie,

If both Excel and Sheets fail, you can:

• Use Chrome's "Copy as XPath" to grab the table structure.
• Or write a quick Apps Script in Sheets to scrape the data manually.

I hope this helps. If you need further assistance you can reply to this message.

Thank you,

April P.

Independent Advisor

By now you've found hat the suggestion from April Pascual doesn't work... at least, not in Excel for Mac. Although Power BI has been added From Web isn't included.

This article by MVP Jim Gordon explains how it can be done on a Mac:

Making and using Excel Web Queries

Once you've save the IQY file go to Excel's Data menu to access the query: