How to Import Data From a Website Into Excel

Excel has shipped a built-in web scraper for a decade. Data → Get Data → From Web opens a parser that lands any table on the grid in two clicks, and most people who need to import data from website to Excel have never pressed it. The catch shows up the first time you point it at a modern site: the Navigator comes back empty, because the table you can see is assembled by JavaScript after the HTML ships.

Three routes below: the built-in connector for static tables, the JSON endpoint hiding behind every “empty” Navigator, and a scraper-to-file bridge for jobs that outgrow both. I ran or verified everything here today (2026-10-16), and the validation section catches two bugs I hit in my own pipeline this morning.

Pick your route before you click anything

Your job	Route	Why
One table on a static page (Wikipedia, gov stats, older sites)	Power Query, built in	Fastest setup, and refresh re-runs the whole import
Table loads with JavaScript (dashboards, “click a year” pages)	Import the JSON endpoint instead	The endpoint serves raw data with no rendering step
The same pages every week, or many pages at once	Scraper writes a CSV, Excel reads the file	Power Query alone can’t paginate, log in, or throttle
Your pipeline must output .xlsx directly	pandas + openpyxl	One function call writes a real workbook
Your team lives in Google Sheets, not Excel	IMPORTXML / IMPORTHTML	Different tool entirely; see the Sheets guide linked below

Setup: Excel 2016 or Microsoft 365 on Windows for the Power Query steps. Mac imports work, with fewer transform buttons. If you take the scraper bridge, you need Python with pip install pandas openpyxl (I tested against pandas 3.0.0 and openpyxl 3.1.5).

Step 1: Import data from website to Excel with Power Query

The path is Data tab → Get Data → From Web. Paste the URL, press OK, and Excel fetches the page and shows the Navigator: a sidebar listing every table it detected, with a preview pane.

My test target was the Wikipedia page “List of countries by GDP (nominal)”. Before opening Excel I counted what the server actually sends: curl -s <url> | grep -o "<table" | wc -l returns 8. That number matters, because Power Query reads the same HTML curl does. It has no JavaScript engine, so those 8 table elements are exactly what the Navigator has to work with, and 2 carry the wikitable class. Pick the big one (the preview shows country names and three numeric columns), click Load, and the data lands as a green-banded Excel table backed by a refreshable query.

Two failure cases to know about:

• The site returns a challenge page. Power Query’s fetcher gets exactly what any bot gets. If the “table” preview shows 35 words of “verify you are human”, the site is blocking Excel, and no amount of clicking fixes that. Excel has no proxy settings worth the name and no browser fingerprint tricks. Move to the scraper route.
• The Navigator numbers its tables and names none of them. This page serves 8 candidates; docs portals and comparison sites routinely serve 30. You pick by preview, which gets old fast. If you only need one table, a scraper that targets table.wikitable by class beats preview roulette.

One honest note on my own verification: I confirmed the HTML side (what Excel reads, 8 table elements, all numeric cells present) with curl and a browser today. The dialog steps above are the standard flow from Microsoft’s “Import data from the web” guide. I can’t run Excel itself on this Linux box, so treat the click path as documented, not personally verified.

Step 2: When the Navigator shows no tables, find the JSON behind the page

This is the case that kills most imports, and it has a fix that works far more often than people expect.

The rule: Power Query parses what the server sends. It never executes the page’s JavaScript. So when a browser shows a beautiful table and Excel shows nothing, the table did not exist at fetch time. My proof, from today: the practice page at scrapethissite.com/pages/ajax-javascript/ (an Oscar winners table) ships its HTML with one <table> element and zero body rows. The word “Spotlight”, the first film in the 2015 table, appears nowhere in the initial HTML. The rows arrive after you click a year.

Here’s how to find where they come from:

1. Open the page in your browser, press F12, and go to the Network tab.
2. Click the control that loads the data (the “2015” year link here).
3. Watch for a new request. On this page it’s a single GET to /pages/ajax-javascript/?ajax=true&year=2015, and it returns application/json.

Fetch that URL with curl and you get 16 clean records: title, year, nominations, awards. Now paste that URL into Data → Get Data → From Web. Power Query detects JSON, opens the Power Query Editor, and a click on To Table (it’s “To Table” on the list) gives you 16 rows on the grid. No code, no rendering, and refresh re-fetches the JSON.

For a deeper walkthrough of this discovery process, see how to find a website’s hidden API.

Limits of the trick: some apps POST payloads with session tokens, sign requests, or page through an API with cursor parameters. You can’t replay those from a URL box. That’s the moment the job stops being an Excel task.

Step 3: When the job needs a scraper, bridge the data through files

For repeated or login-walled targets, a real scraper is the right tool. I use Magpie, a local CLI scraper, and the honest verdict for Excel work: it fetches and extracts well, but it has no .xlsx export and no Excel or Google Sheets connection. Tables come out as markdown, as HTML you point it at, or as AI-extracted JSON. The workbook step is yours. The core is AGPL-3.0-or-later with a commercial option; the desktop app downloads free with 5 free runs per install, then $49/month.

The default markdown output is the wrong shape for spreadsheets, and the failure deserves a demo. Scraping that GDP page the plain way returns the table rows as prose-glued strings like World128,003,776118,350,166111,565,144. No delimiter, no recovery. Two flags fix it:

magpie scrape "https://en.wikipedia.org/wiki/List_of_countries_by_GDP_(nominal)" \
  --include 'table.wikitable' --page-format html --out gdp-tables.html

That writes just the table HTML (400 KB, 2 tables, 2.1 seconds on my connection). Then pandas, which parses tables the same way Power Query does, writes the workbook:

import pandas as pd

tables = pd.read_html("gdp-tables.html", encoding="utf-8")
df = max(tables, key=len)  # the big IMF table
for col in df.columns[1:]:
    first = df[col].astype(str).str.extract(r"([\d,]+\.?\d*)")[0]
    df[col] = pd.to_numeric(first.str.replace(",", "", regex=False), errors="coerce")
df.to_excel("gdp.xlsx", sheet_name="IMF", index=False)

Run this morning, that pipeline produced 222 rows × 4 columns, wrote gdp.xlsx, and read back clean (United States, 32642373.0, exactly matching the page). The type-coercion lines are not decoration; the validation section explains why they earn their space.

If Python isn’t your thing, most scrapers, Magpie included, can write CSV, and Excel reads CSV natively: Data → Get Data → From Text/CSV, pick the file, confirm the delimiter, Load. For many pages at once, magpie crawl accepts --format csv and applies one extraction schema across every page. The details of both routes live in how to scrape a website to CSV or JSON and how to scrape a table from any website.

Validate the import before you trust it

Every import route in this guide can produce a spreadsheet that looks right and is quietly wrong. Four checks, all runnable in a minute, all of which caught something real in my own runs today.

1. Row count against the source. My xlsx landed 222 data rows. Scrolling the Wikipedia table confirms the same ballpark. If a refresh ever changes that number by more than the site’s actual update, you want to know before a chart does.

2. Spot-check two cells, including a weird one. The US figure read back 32,642,373, matching the page. The United Arab Emirates row reads “552,325 (2024)” on the page, a number with a year glued to it. Check those, not just the clean ones.

3. Type, which is where silent corruption lives. pandas parsed every column as text, and numbers-as-text is the classic Excel import failure too (left-aligned integers, SUM returning 0). The obvious fix, stripping everything that isn’t a digit, mangled my data: “552,325 (2024)” became 5523252024, a number 10,000× too large. That bug would have hit 34 cells on this one page without a single error message. The str.extract in the script above takes the first number and leaves the year annotation behind. In Excel, Power Query’s “Detect Data Type” and “Replace Values” steps do the same job; watch any column where the source mixes numbers with annotations.

4. Blanks. After coercion, 40 cells came back empty. Those are the page’s “N/a” placeholders and footnote-mangled cells, and they are supposed to be blank. Decide deliberately: filter them, count them, or fill them. What you must not do is discover them in a pivot table three weeks later.

For the deeper cleaning pass (dedupe, date normalization, unit drift), that’s a whole discipline of its own: see how to clean and deduplicate scraped data.

Make the refresh stick

Power Query imports remember their steps. Right-click the table and Refresh, or Data → Refresh All, and Excel re-fetches the URL and replays every transformation. That makes the Wikipedia import a one-time setup.

For a daily website-to-Excel pipeline, don’t ask Excel to do the scheduling. Task Scheduler (or cron) runs the scraper at 6 a.m. and writes report.csv; the workbook’s query points at that file path; your morning routine is one Refresh All, or nothing at all if you set the query to refresh on file open. This split matters because the scraper, not Excel, is the component that can throttle itself, rotate user agents politely, and respect robots.txt when a target pushes back.

If the team actually collaborates in Google Sheets, skip Excel entirely: Sheets’ IMPORTXML and IMPORTHTML formulas cover static pages, with different limits. I compared them in Google Sheets web scraping: IMPORTXML and beyond.

FAQ

Can Excel pull data from a website automatically? Yes, through Power Query: the import re-runs on Refresh, and you can set queries to refresh when the file opens. Excel has no built-in scheduler, so true unattended runs pair a scheduled scraper writing a CSV with a file-based query.

Why doesn’t my table show up in Power Query? Usually the table is built by JavaScript after the HTML arrives, so Excel’s fetcher never sees it. Test with curl or view-source: if the table isn’t in that HTML, import the JSON endpoint instead (Step 2 above).

Does Excel have an IMPORTXML formula like Google Sheets? No. The nearest formula is WEBSERVICE, which is Windows-only, returns raw text capped at 32,767 characters, and parses nothing. Power Query is the actual equivalent, and a better one.

Can Excel import from sites that use Cloudflare protection? Excel’s fetcher gets served the challenge page like any other bot, so no. A browser-rendering scraper may fetch what you see, but nothing should bypass the challenge itself, and the site’s terms decide whether you should scrape at all.

Should I import CSV or xlsx? CSV for handoffs: it’s universal, and nothing hides inside it. xlsx when you need typed columns, multiple sheets, or formulas. pandas writes real xlsx with one to_excel call, so the “CSV is limited” argument mostly dissolves.

The fastest diagnostic you now own

When an import misbehaves, curl the URL and count the tables before touching Excel. Fifty-plus tables: Power Query will find them. Zero: go find the JSON. No browser and no login: Power Query again. Everything else belongs to a scraper writing files, and the grid doesn’t care where its rows came from, only that they arrive clean.