Scrape Website Data Into Excel, Then Prepare It for Sharing

For a public webpage with a table, start with Data → From Web in desktop Excel for Windows. Power Query can detect tables, let you choose one, and load it into a worksheet. Choose Transform Data before loading when you need to clean headers, remove unwanted rows, or set column types. Microsoft documents this workflow.

The goal is not just to copy what appears on screen. It is to produce a checkable dataset: one observation per row, consistent columns, and enough source information to explain where the values came from.

1. Choose the source and output structure

Before scraping, look for the site’s own CSV or Excel download. Prefer a documented export or API when available, especially for a table spread across multiple pages. Check the site’s terms and data license before collecting or republishing; access to a page is not a substitute for permission to reuse its contents. Do not bypass access controls.

Decide what one row represents. For example, a table of annual regional statistics might use this illustrative output structure:

Column	Meaning	Recommended type
region_code	Identifier exactly as published	Text
region_name	Name of the region	Text
year	Year the observation describes	Whole number
value	Published measurement	Appropriate numeric type
unit	Unit or scale used by the source	Text
source_url	Page supplying the observation	Text
retrieved_date	Date you collected the data	Date

Keep the observation year separate from the retrieval date. A value collected today may describe an earlier period.

2. Import the table with Power Query

These steps target desktop Excel for Windows, using the documented From Web workflow. Power Query is included in Excel 2016 and later for Windows. Connector availability differs across platforms: as of October 19, 2026, Microsoft’s Mac list does not include the Web connector, so do not assume the same buttons exist there. On Mac, a downloaded CSV or XLSX is a practical starting point. See Microsoft’s platform and source comparison.

1. Open a workbook and copy the address of the page containing the table.
2. On the Data tab, choose From Web.
3. Paste the address into the URL box and select OK.
4. If prompted for authentication, choose Anonymous only when the page requires no credentials, then select Connect.
5. In Navigator, select a detected table and inspect its preview. Match the headers and several values to the source page; do not choose solely by a name such as “Table 0.”
6. Select Transform Data to open Power Query Editor.

The Navigator preview and the choice between Load and Transform Data are part of Microsoft’s Web connector instructions.

3. Clean the query before loading

In Power Query Editor, remove title rows, repeated headers, blank rows, and footer material that is not an observation. Give each column a clear, unique name. Preserve explanatory footnotes separately rather than silently dropping their meaning.

Inspect every column’s type using the icon beside its heading:

• Identifiers: use Text for codes, including numeric-looking codes with leading zeros. If an earlier conversion already removed zeros, correct or remove that conversion step; changing the damaged value back to Text will not reconstruct it.
• Measurements: use a numeric type only after separating units and interpreting symbols. Do not replace suppressed or unavailable values with zero.
• Dates: check whether the source uses day/month/year or month/day/year. For a different regional convention, right-click the heading and use Change Type → Using Locale.

Power Query’s automatic type detection for unstructured sources can rely on the first 200 rows. Treat inferred types as a starting point, not validation. Microsoft explains both type detection and locale-based conversion.

Keep totals out of the observation table unless they are explicitly part of your intended dataset. Otherwise, later sums can count both the individual records and their total. See how to separate totals before export.

When the preview is ready, use Home → Close & Load to load the result into Excel. Power Query records transformations in Applied Steps and reruns them when the query refreshes. Microsoft explains loading and recorded transformations.

4. Check completeness and refresh behavior

Before using or sharing the result, verify:

• Coverage: compare the imported row count with the source’s stated coverage. Check pagination, year selectors, and filters; do not assume the import includes every view.
• Values: compare records near the beginning, middle, and end, including blanks, negative values, and unusually large measurements.
• Uniqueness: check the intended key—for example, region code plus year—rather than deleting all repeated names.
• Meaning: record units, missing-value codes, source notes, and any exclusions.

To fetch updates, select Data → Refresh All in Excel and wait for completion. This updates workbook connections; refreshing only the preview inside Power Query Editor does not update the worksheet. Microsoft distinguishes these refresh operations.

Recheck the output after refresh and update retrieved_date to reflect the new collection. A successful connection does not establish that the source still has the same columns or coverage.

If no usable table appears

A table visible in your browser may load dynamically. Microsoft documents timing-related failures on dynamic pages, including intermittent missing-column errors. See the Web connector troubleshooting guidance.

Look for an official download or documented API instead. If the site requires login, presents a challenge page, or blocks extraction, use an authorized export or request the data from its owner. Do not treat a login or error page as your dataset.

5. Prepare a separate public release

Keep the query workbook for maintenance. Create a separate, values-only release containing the reviewed table and safe provenance fields. Exclude private worksheets, personal information, credentials, and source URLs containing access tokens. Follow the CSV export checks if you choose CSV, including inspecting the saved file rather than relying only on Excel’s display.

For public exploration, TablePage accepts CSV, TSV, XLSX, and XLS files and generates a public dataset page with a filterable table and shareable link. Upload only the reviewed release file—not the working workbook containing sensitive material.

Alongside the shared data, provide the source, retrieval date, units, coverage, and cleaning decisions. After a later scrape, validate and publish a new reviewed release; refreshing Excel is a separate action from publishing the file.