How to Extract Data from a Website to Excel

What the exported file looks like

The run in the example was stopped by hand after eight pages and produced 164 rows. These are the first rows of that CSV, unchanged:

Two things are worth noticing. The columns already have plain names: they are worked out from what each column holds, and any of them can be renamed in the panel before you export. And the first title is cut short because the list page itself shortens it: a scraper copies what the page shows. To get the full titles, collect them from each item's own page — see how to scrape a list of URLs.

Tidy the data in Excel

• Check the headers. A column the scraper could not name is called Text 1, Text 2 and so on. Type a better name over it, or rename it in the panel before exporting.
• Turn prices into numbers. A price such as £51.77 arrives as text. In a new column, strip the currency sign and convert it:

=VALUE(SUBSTITUTE(B2,"£",""))

• Remove duplicates. Select the table, then Data → Remove Duplicates.
• Keep the header in view. View → Freeze Panes → Freeze Top Row.
• Filter and sort. Select the header row and press Ctrl+Shift+L (Cmd+Shift+L on a Mac) to add filter arrows.

Method 2: Excel's own From Web import

Excel can read a web page by itself through Power Query. In Excel for Windows:

1. Go to Data → Get Data → From Other Sources → From Web.
2. Paste the address of the page and click OK.
3. In the Navigator window, pick one of the tables Excel found and check the preview.
4. Click Load, or Transform Data if you want to clean it first.

This works well for a plain HTML table on a public page, and the query can be refreshed later. Microsoft documents it under Import data from data sources (Power Query). It has limits:

• It only sees real tables. Product cards and search results are usually not tables, so the Navigator shows nothing useful for them.
• It reads one address at a time, so a list spread over many pages needs a query per page or some M code.
• It can't scroll or click, so content that loads as you scroll is missed.
• It doesn't use your browser session, so pages that need a login often fail.

Method 3: copy and paste

For one small table, selecting it with the mouse and pasting is still the fastest way. Paste with Paste Special → Text (or "Match Destination Formatting") to avoid dragging fonts, colors and links into the sheet. It breaks down as soon as the list is long, split over pages or made of cards.

Which method to use

Using Google Sheets instead

In the results window, click Copy and paste into an empty sheet; the columns line up. Sheets also has its own import formulas for public tables. Both are covered in the Google Sheets guide.

If the file looks wrong in Excel

• Numbers treated as text, or leading zeros lost: import the CSV through Data → From Text/CSV and set the column type there instead of double-clicking the file.
• Dates in the wrong order: Excel reads dates using your regional settings. Import through Data → From Text/CSV and choose the right locale.
• Empty cells: the page didn't show that value for that item. Open the item on the site to confirm.
• Fewer rows than expected: the run ended because a page brought nothing new. Check the Paging option; the guide on how to scrape multiple pages explains each one.