How to Scrape Data from a Website to Excel: A Complete Guide

Sharing is caring!

Scraping data from websites to Excel is an essential skill for professionals and students looking to streamline their workflows, conduct research, or analyze online information. With tools like Power Query, Excel makes this process simple and efficient, enabling you to gather, clean, and organize data from multiple web pages into a single table.

In this comprehensive guide, we will show you how to scrape web data and import it into Excel, even from multiple pages, using Power Query.

What is Web Scraping?

Web scraping is the process of extracting data from websites and storing it in a structured format for further analysis. For instance, if you want to gather reviews, prices, or tables of information from a website, web scraping allows you to automate this process and retrieve the data efficiently.

Why Scrape Data to Excel?

Scraping data into Excel offers numerous benefits:

• Data analysis: Perform in-depth analysis using Excel tools.
• Automation: Save time by automating manual data collection tasks.
• Data consolidation: Combine data from multiple sources into one file.
• Ease of use: Excel provides an intuitive interface for handling large datasets.

How to Scrape Data from a Website to Excel Using Power Query

Follow the step-by-step instructions below to scrape data from a website and import it into Excel.

Step 1: Open Power Query in Excel

1. Launch Microsoft Excel and open a new workbook.
2. Go to the Data tab in the ribbon.
3. Click on Get Data > From Other Sources > From Web.

This will open the Power Query Web Connector.

Step 2: Enter the Website URL

1. Copy the URL of the web page containing the data.
2. Paste the URL into the Web Connector dialog box.
3. Click OK.

Power Query will connect to the web page and display all the available tables and data elements from the site.

Step 3: Select the Desired Table

1. In the Navigator window, preview the tables fetched by Power Query.
2. Select the table containing the data you need (e.g., reviews, product details, etc.).
3. Click on Transform Data to clean and prepare the data.

Step 4: Clean and Transform the Data

Power Query offers powerful data-cleaning tools. Here’s what you can do:

• Remove unnecessary columns: Select the columns you want to keep, right-click, and choose Remove Other Columns.
• Rename columns: Double-click the column headers to give them meaningful names.
• Replace values: For example, remove unwanted text by right-clicking a cell, selecting Replace Values, and entering the desired replacement.
• Trim spaces: Go to the Transform tab and click on Trim to remove leading or trailing spaces.

Step 5: Load Data into Excel

1. Once your data is cleaned, go to the Home tab in Power Query.
2. Click Close & Load to load the data into a new worksheet in Excel.

Your scraped data is now available in Excel, ready for analysis!

Scraping Data from Multiple Web Pages into Excel

Sometimes, data is spread across multiple web pages (e.g., paginated reviews or product listings). With a little M code in Power Query, you can extract and combine data from multiple pages.

Step 1: Analyze the URL Structure

Identify the pattern in the URLs for each page. For example:

• Page 1: https://example.com/reviews?page=1
• Page 2: https://example.com/reviews?page=2
• Page 3: https://example.com/reviews?page=3

The page number changes dynamically. This makes it easy to automate.

Step 2: Create a Function in Power Query

1. Go to View > Advanced Editor in Power Query.
2. Replace the existing code with the following function:

(PageNumber as text) =>
let
    Source = Web.Contents("https://example.com/reviews?page=" & PageNumber),
    Data = Html.Table(Source, {"Column Name", "XPath/Selector"})
    // Customize this part based on the website’s structure
in
    Data

3. Click Done to save your function.

Step 3: Generate a List of Page Numbers

1. Create a new query by going to Get Data > Other Sources > Blank Query.
2. In the formula bar, enter:

= {1..5}  // Replace 5 with the total number of pages you want to scrape

3. Convert the list into a table by going to Transform > To Table.

Step 4: Invoke the Function

1. In the new query, go to Add Column > Invoke Custom Function.
2. Choose the function you created earlier and pass the page numbers as input.
3. Combine the results from all pages into a single table by clicking Combine.

Step 5: Load the Combined Data

1. Once the data from all pages is combined and cleaned, go to Home > Close & Load.
2. The combined data will now appear in a new worksheet.

This method is perfect for extracting large datasets distributed across multiple pages.

Tips for Effective Web Scraping in Excel

• Understand the website structure: Use tools like browser developer tools (F12) to inspect elements and find table structures.
• Avoid blocked requests: Some websites may block excessive requests. Use scraping responsibly.
• Regularly update queries: Website structures change over time, so periodically check your queries to ensure they still work.
• Optimize performance: Scraping many pages may slow down Power Query. Keep queries efficient by limiting unnecessary steps.

When to Use Advanced Scraping Tools

If the data you need is not accessible with Power Query (e.g., JavaScript-rendered content), consider using advanced tools like Python (with libraries like BeautifulSoup or Selenium) or dedicated web scraping platforms.

Final Thoughts

Using Power Query to scrape data into Excel is an efficient way to gather and analyze online information. With the steps outlined above, you can scrape data from single or multiple web pages, clean it, and consolidate it into Excel seamlessly. Start applying this technique to save time and make data-driven decisions effortlessly.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.