How to scrape website data into Excel
Choose the lightest method that can actually read your target page: copy a small static table by hand, use Power Query for repeatable table imports, add VBA or a scraping API for dynamic or protected pages, or move to Python or a public API when you need full control. This guide explains the decision, the setup for each route, and how to keep imported data refreshing.
Start with a one-off copy only when the data is small
Use copy-paste only for a one-off snapshot of a few rows. Select the table on the page, copy it, paste into Excel, and check that columns landed in separate cells. It needs no setup, but it is slow and error-prone for large tables and cannot refresh when the page changes. If you repeat the same paste weekly, move to a query-based method.
Use Power Query From Web for repeatable static tables
On Windows Excel, open Data, then Get Data, From Other Sources, From Web, enter the page address, and choose Anonymous for a public page. In Navigator, preview each detected table and pick the correct one. If no clean table appears, Add Table Using Examples lets you type a few sample values and lets Power Query infer the pattern. Use Load for a new sheet, Load To for a chosen location, or Transform Data to clean columns before loading. This route suits structured HTML tables and lists, needs no code, and can be refreshed, but it does not execute JavaScript and may be blocked on some sites. It is not available as a Web connector on Mac, and Excel for the web offers only a Web API source.
Set refresh so the sheet stays current
Click the imported table, open Query Properties, and enable Refresh every N minutes, Refresh data when opening the file, or both. Refresh All remains available for manual updates. Remember these settings work only while the workbook is open; Excel has no built-in scheduled refresh for a closed file. For unattended updates, pair a refresh macro with Windows Task Scheduler or trigger the refresh through Power Automate.
Use VBA when you need a macro inside Excel
VBA gives more control inside Excel. Enable the Developer tab through File, Options, Customize Ribbon, open the editor with Alt+F11, insert a module, and paste the protected VBA example identified as code0001. That example sends a GET request with MSXML2.XMLHTTP, parses the response with an htmlfile object, and writes extracted links into cells. Older Internet Explorer automation is obsolete after IE retired in June 2022, and the modern request approach never runs JavaScript. The method is Windows-only because MSXML2 is a Windows COM object, and it can break when page structure changes.
Call a scraping API from Excel for dynamic or protected pages
When a page renders content dynamically or blocks automated requests, neither Power Query nor VBA alone is enough. Call a scraping API from Excel instead: in Power Query, start a Blank Query, open the Advanced Editor, and use the protected example code0002, which combines Web.Contents with Json.Document to return a single extracted value; in VBA, use protected example code0003, which sends the same style of MSXML2 request to the API endpoint and extracts the named field from the JSON response. A practical test case is tracking one product price in a cell and enabling refresh on open. CSS selectors work for stable markup, while AI extraction described in plain English is better when class names change frequently. API access, keys, and any free-credit terms require owner confirmation before publication.
Use Google Sheets functions as a no-code alternative
Google Sheets provides IMPORTHTML for HTML tables or lists, IMPORTXML for XPath-targeted elements, and IMPORTDATA for CSV or text files, using protected examples code0004 through code0009. After importing, download the sheet as an Excel file through File, Download, Microsoft Excel. These functions update automatically and need no code, but they share the same limits as Power Query: no JavaScript-rendered pages, no login-protected data, and breakage when structure changes. Excel itself has no equivalent import formulas.
Move to Python or a public API for complex or recurring work
Python is the flexible route for nested tables, multiple pages, scheduled runs, or server-side automation. The workflow is: fetch the page, parse HTML, extract rows and cells, then save a DataFrame to an Excel file. Use a local Python installation outside Excel; the in-cell Python feature runs isolated without internet access and cannot scrape. Before writing a script, check whether the site offers an API through its footer, developer docs, browser DevTools Network tab, or a web search. An available API usually gives cleaner, faster, more stable data than HTML parsing, though it may be limited, rate-limited, or require authentication. Use scraping when no suitable API exists or access is restricted.
| Method | Best fit | Key limit |
|---|---|---|
| Manual copy-paste | Small one-off tables | No refresh; error-prone at scale |
| Power Query From Web | Static HTML tables on Windows | No JavaScript; Windows-only connector |
| VBA with MSXML2 | Custom macro inside Excel | No JavaScript; Windows-only; breaks on layout changes |
| Scraping API from Power Query or VBA | Dynamic or bot-protected pages | Requires API access and key; owner-confirmed terms |
| Google Sheets IMPORT functions | Simple public tables, lists, CSV | No login or JavaScript-rendered content |
| Python script | Complex, multi-page, scheduled extraction | Requires coding and HTML knowledge |
| Public API | Structured data when offered | May be limited, rate-limited, or unavailable |
Decision summary based on the methods described; platform availability and API terms require owner confirmation where noted.
Frequently asked questions
Can Excel scrape data from a website?
Yes for static HTML tables: Power Query's From Web connector loads them into a sheet and can refresh them without code. Pages that render content with JavaScript or block automated requests need assistance, such as calling a scraping API from Power Query or VBA and loading its structured response.
How do I pull data from a website into Excel automatically?
Import once with Power Query, then open Connection Properties for the query and enable refresh every N minutes or refresh when the file opens. Excel refreshes only while the workbook is open; for unattended schedules, use Task Scheduler with a refresh macro or Power Automate.
Does Power Query's From Web work on Mac or Excel for the web?
Mostly no. From Web is Windows-only. Excel for Mac has Power Query but not the Web connector, and Excel for the web provides only a Web API source rather than the full feature set.
Can I scrape a website into Excel without coding?
Yes. Power Query is point-and-click for static tables, and no-code tools can export CSV files that open in Excel. AI extraction tools can also return structured results from a plain-English description without selectors or scripts.
Can I use an Excel formula like WEBSERVICE to get web data?
On Windows, WEBSERVICE can place a URL response in a cell and FILTERXML can parse XML with XPath. Both are unavailable on Mac and Excel for the web, work only with unauthenticated requests, and cannot parse JSON natively, so they fit simple XML feeds rather than general scraping.