How to Scrape an HTML Table to an Excel Spreadsheet

For a normal public webpage, the quickest method is Excel’s built-in Power Query connector: open Data > From Web, enter the page URL, select the detected table in Navigator, optionally transform it, and choose Load. The result is an Excel table that you can refresh later.

This guide covers the complete workflow, including pages with several tables, columns that contain leading zeroes, JavaScript-rendered content, Google Sheets, and a repeatable Python script.

1. Import an HTML table directly with Excel Power Query

Open Excel and select Data > From Web. Some versions show this under Data > Get Data > From Other Sources > From Web.
Paste the URL of the page containing the table and connect.
In Navigator, review the detected table previews. Select the preview whose headers and sample rows match the data you need.
Choose Load to insert the result into a worksheet, or choose Transform Data to open Power Query Editor first.
After loading, inspect the result for missing rows, shifted headers, unexpected blanks, and incorrect data types.

Power Query can suggest tables and lets you inspect the page in Web View. If automatic detection misses the structure, use Add Table Using Examples and provide representative values from the columns you want. Microsoft documents separate connectors for web pages, web APIs, and files; use the connector that matches the resource behind the URL.

Clean the data before loading

Choose Transform Data when you need to:

Remove columns or rows that are not part of the table.
Promote the first row to headers.
Split a combined column such as “Name — Region”.
Trim whitespace and replace blank values.
Change dates, currencies, and numbers to the intended types.
Force identifiers such as 00127 to remain text.

Automatic type detection can turn an identifier into a number and remove leading zeroes. Set that column’s type to Text before loading if the exact characters matter.

Refresh the imported table

When the source changes, select a cell in the loaded table and use Query Refresh (the exact label varies by Excel version). Refreshing reruns the web query and applies the saved transformation steps. A refresh can produce different results if the source page has changed its markup, access rules, or table order.

2. Use Google Sheets as an intermediate step

Google Sheets provides the IMPORTHTML function for pages that expose ordinary HTML tables or lists:

=IMPORTHTML("https://example.com/products", "table", 1)

The syntax is IMPORTHTML(url, query, index). Set query to "table" or "list", then provide the 1-based position of the table or list. Table and list positions are counted separately. For example:

=IMPORTHTML("https://en.wikipedia.org/wiki/Demographics_of_India", "table", 4)

After the data appears in Sheets, download it as an Excel workbook with File > Download > Microsoft Excel (.xlsx).

Google documents that import functions check for updates approximately every hour while the spreadsheet is open. That is a checking cadence, not a guarantee that every page will refresh immediately. External imports can also be throttled or require you to approve access.

3. Scrape tables programmatically with Python and pandas

Use pandas when the extraction must be repeatable, scheduled, or followed by data cleaning. Install the dependencies:

python -m pip install pandas lxml openpyxl requests

The simplest script reads every detected table and writes each one to a separate worksheet:

import pandas as pd

url = "https://example.com/products"
tables = pd.read_html(url)

with pd.ExcelWriter("tables.xlsx", engine="openpyxl") as writer:
    for number, table in enumerate(tables, start=1):
        table.to_excel(writer, sheet_name=f"Table {number}", index=False)

print(f"Wrote {len(tables)} tables to tables.xlsx")

read_html returns a list of DataFrames. Always inspect the list before assuming that the first table is the one you need:

for number, table in enumerate(tables, start=1):
    print(number, table.shape)
    print(table.head(3))

Select a table by text or HTML attributes

Use match when a table contains distinctive text, or attrs when the page gives the table a stable ID or class:

import pandas as pd

url = "https://example.com/catalog"

matching = pd.read_html(url, match="Product code")
by_id = pd.read_html(url, attrs={"id": "catalog-table"})
by_class = pd.read_html(url, attrs={"class": "results"})

matching[0].to_excel("catalog.xlsx", index=False)

Preserve leading zeroes and control types

import pandas as pd

url = "https://example.com/locations"
table = pd.read_html(
    url,
    converters={"Store code": str},
)[0]

table.to_excel("locations.xlsx", index=False)

Converters are useful for account numbers, postal codes, SKU values, and other identifiers where 00042 must not become 42. For more complex pages, specify headers, index columns, skipped rows, and missing-value behavior in the read_html call, then validate the resulting DataFrame before exporting.

Export one workbook with multiple cleaned sheets

import pandas as pd

url = "https://example.com/report"
tables = pd.read_html(url)

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    for i, df in enumerate(tables, 1):
        cleaned = df.dropna(how="all").copy()
        cleaned.columns = [str(column).strip() for column in cleaned.columns]
        cleaned.to_excel(writer, sheet_name=f"Table {i}", index=False)

See the pandas HTML table documentation for the supported selection and parsing options.

4. Choose the right method

Method	Best for	Selection	Refresh or repeatability
Excel Power Query	One-off imports and visual cleanup	Navigator previews, Web View, examples	Refresh the saved query
Google Sheets `IMPORTHTML`	A quick formula-based import	1-based table or list index	Checks approximately hourly while open
Python and pandas	Scripts, validation, and downstream processing	Text matching, IDs/classes, parser options	Run the script whenever needed

Do not choose based only on the number of tables on the page. Check how the site delivers the data, whether you are authorized to access it, and whether the table is present in the initial HTML. If the URL actually points to an API or downloadable file, use the corresponding Power Query connector or the API’s documented response instead of treating it as a web page.

5. Tables that do not import cleanly

JavaScript-rendered tables

Some pages send an empty table shell and populate rows in the browser with JavaScript. Excel and pandas may then see no rows or only the headers. Look for an underlying API request in the site’s documentation or browser developer tools and use the authorized API or file endpoint. If no supported endpoint exists, a browser automation workflow may be required before exporting the result.

Nested, merged, or multi-row headers

HTML tables with rowspan, colspan, nested tables, or repeated heading rows can produce multi-level or shifted columns. In Excel, inspect the preview and remove or promote headers in Power Query. In pandas, print table.columns, drop repeated header rows, and rename columns explicitly before writing the workbook.

Pagination and lazy loading

A visible table may show only the current page of results. Confirm whether “Next” loads a new URL, calls an API, or appends rows after a click. A single HTML request generally cannot recover rows that the server never included in its response. Treat each page or API response as a separate extraction step and deduplicate records before export.

6. Troubleshooting

Problem	Likely cause	Fix
Excel shows the wrong table	The page contains several tables or decorative layout tables	Compare Navigator previews and headers; use Add Table Using Examples or transform steps.
No table is detected	The rows are rendered by JavaScript, or the URL is an API/file	Check the initial HTML and use the matching API or file connector.
Leading zeroes disappear	Automatic numeric type conversion	Set the Excel column to Text or use a pandas converter such as `{"Code": str}`.
Headers are shifted	Multiple header rows, merged cells, or a caption row	Remove non-data rows, promote the correct row, and rename columns explicitly.
Rows are missing	Pagination, lazy loading, or a failed request	Check page controls and network requests; process every page or use the documented data endpoint.
Sheets asks for access	The external import needs approval or has reached a usage limit	Approve the requested access when authorized, reduce repeated imports, and account for hourly checks and throttling.
Authentication fails	The connector credentials do not match the site’s supported method	Use the site’s authorized authentication method; Microsoft lists options such as anonymous, basic, Web API, Windows, and organizational accounts with platform-specific differences.
pandas raises a parser or encoding error	Malformed HTML, missing parser dependency, or unusual markup	Install the required parser, try the other supported parser, save the authorized response for inspection, and simplify the selection criteria.

Confirm the source URL and retrieval date.
Compare the first and last few source rows with the worksheet.
Check row counts against the page or API response.
Verify dates, currency, percentages, and identifiers have the intended types.
Search for blank cells caused by merged headers or missing values.
Check for duplicate rows when combining paginated results.
Save the query, formula, or script so the import can be reproduced.

8. Performance, reliability, and cost considerations

Power Query is usually efficient for a small number of ordinary tables because it combines retrieval and transformation in one workbook. pandas is better when you need validation, multiple pages, or repeatable processing, but it adds Python dependencies and maintenance. Sheets is convenient for collaboration, while its documented hourly checking and import limits make it less suitable for high-frequency collection.

Keep requests infrequent, respect the site’s terms and access controls, and cache intermediate data when a source is slow or rate-limited. An API or downloadable file is generally more stable than parsing presentation HTML, especially when the site redesigns its markup.

9. Or skip the browser setup

If you need a clean visual record of the source page before reviewing or documenting the table, ScreenshotNeo provides a one-call screenshot API. It is a visual capture service, so you still use Excel, Sheets, pandas, or the site’s API to extract structured cells.

See the ScreenshotNeo API documentation for options and response details:

curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://example.com/products -o shot.webp

import requests
r = requests.get("https://api.screenshotneo.com/v1/shot", params={"access_key": "YOUR_API_KEY", "url": "https://example.com/products"}, timeout=90)
open("shot.webp", "wb").write(r.content)

const q = new URLSearchParams({ access_key: 'YOUR_API_KEY', url: 'https://example.com/products' });
const res = await fetch(`https://api.screenshotneo.com/v1/shot?${q}`);

ScreenshotNeo accepts consent banners before capture and removes more than 60 known consent platforms, newsletter popups, and chat widgets; each step can be turned off. Bot checks or CAPTCHAs, blank pages, timeouts, failed loads, and cache hits are not billed, and responses identify the page verdict and billing status in X-Page-Verdict and X-Billed headers. Its MCP server provides take_screenshot, get_page_info, and capture_pdf tools for Claude, Cursor, and other MCP clients. The Free plan includes 1,000 screenshots each month without a card; paid plans start at $5 for 3,000 shots.

Create a free ScreenshotNeo account to get 1,000 screenshots per month with no card.

10. FAQ

Can I scrape an HTML table without installing software?

Yes. Excel Power Query and Google Sheets provide built-in or formula-based workflows. They still require the page to expose usable HTML and allow the request.

Why does pandas return several DataFrames?

read_html returns every table it detects. Inspect each DataFrame, then select by text or HTML attributes instead of assuming index zero is correct.

Should I use an API instead of scraping HTML?

Use an authorized API or downloadable file when the site provides one. Structured endpoints are less dependent on visual markup and usually make pagination and types clearer.

Will imported Excel data update automatically?

A saved Power Query can be refreshed. Google documents approximately hourly checking for import functions while the spreadsheet is open. Neither behavior guarantees that a changed or restricted source will refresh successfully.